BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Table variables allow you to hold rows of data in temporary storage, without any processing overhead. Read this blog to add this technique to your SQL armoury!
- Table Variables in SQL (this blog)
- Worked Example of a Table Variable
- Pros and cons of table variables
Posted by Andy Brown on 08 February 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Table Variables in SQL
I'll discuss later the pros and cons of table variables, but it's largely a matter of personal preference whether you use them instead of or as well as temporary tables. I like table variables because you never have to worry whether they already exist when you create them!
Syntax of Temporary Variables
In a previous blog I showed that you can declare variables as follows:
where VariableType can be any of the standard data types in SQL. So the following are examples of variables:
DECLARE @LifeAnswer int = 42
DECLARE @AlternativeAnswer varchar(50) = 'Forty-two'
However, you can also declare a variable to refer to an entire table. The syntax for this is as follows:
DECLARE @TableName TABLE (
Why would you want to do this? Let's look at a worked example, showing how you could solve it with and without a table variable.