Today, I am going to talk about table variables.
In general, TSQL variables are defined by using the DECLARE key word and preceeding the variable name with a @ sign. Table variables are created by using the TABLE key word just after the variable name.
The declaration following this key word includes column definitions — names, data types, and constraints. The only constraint types allowed in the declaration are PRIMARY KEY, UNIQUE, NULL, and CHECK. Indexes are a bi-product of a PRIMARY and UNIQUE constraints.
The scope of a table variable is the function, stored procedure or batch that it is defined in. Within its scope, a table variable can be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, it can not be dynamically defined with a SELECT INTO combination. Once defined, the definition of the table variable can not be altered.
I am going to use the car ownership table for person A (@MY_TABLE) to demonstration how to work with loca table variables. Please see the tiny AUTOS database I created in earlier article.
The code snipet below creates a local table variable with scope related to a batch inside SSMS query window. Since end of a batch is signaled by the GO statement, all TSQL processing refering to the table variable needs to be finished before then.
The following actions are performed by the code below.
1 – Create local TABLE variable.
2 – INSERT data into table variable.
3 – SELECT current data from table variable.
4 – DELETE record three from table variable.
5 – UPDATE record five from table variable.
6 – SELECT current data from table variable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
<span style="color: #008000;">-- Database selection does not matter USE model; GO -- Create local table variable DECLARE @MY_TABLE TABLE ( MyId INT PRIMARY KEY CLUSTERED, MyValue VARCHAR(20) ); -- Add data to local table variables INSERT INTO @MY_TABLE (MyId, MyValue) VALUES (1, 'Continental'), (2, 'Edsel'), (3, 'Lincoln'), (4, 'Mercury'), (5, 'Ram'); -- Show data from table A SELECT T.MyId, T.MyValue FROM @MY_TABLE AS T -- Remove record 3 DELETE FROM @MY_TABLE WHERE MyId = 3; -- Show data from table A SELECT T.MyId, T.MyValue FROM @MY_TABLE AS T -- Update record 5 UPDATE @MY_TABLE SET MyValue = 'Dodge Ram' WHERE MyId = 5; -- Show up dated data from table A SELECT MyId, MyValue FROM @MY_TABLE GO</span> |
Unlike temporary tables, the scope of the variable is the batch. Therefore, no clean up routines are needed to drop the table after use.
I will be using the Adventure Works Sample database supplied by Microsoft during the rest of this talk.
A common query pattern is to get the most recent id by date. Joining these results to the original table will bring back the most recent record by date.
The SELECT query below stores the last sales order by customer id as a local table variable called @MC. The main query joins to this table to return customer name, sales id, sub-total and order date. This might be a query for the SALES team to cold call customers that have not ordered in the past x months.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
<span style="color: #008000;">-- Use the A.W. database USE AdventureWorks2008R2 GO -- Create local table variable DECLARE @MC TABLE ( MyCustomer INT, MyDate DATETIME ); -- Local temp table (MC) - last sales order by customer in 2008 INSERT INTO @MC SELECT CustomerID AS MyCustomer, MAX(OrderDate) AS MyDate FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = 2008 GROUP BY CustomerID -- Get customer name, sales id, sub-total and order date SELECT P.FirstName, P.LastName, SH.SalesOrderID, SH.SubTotal, SH.OrderDate FROM Sales.SalesOrderHeader AS SH INNER JOIN @MC AS MC ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID ORDER BY SH.OrderDate ASC GO </span> |
Table variables have a limited scope. Any cleanup issues that developers might create with temporary tables can be avoided by using them. However, the definition of the variable can not be changed once defined. Indexes on a table variable are created as a bi-product of constraints. Any object with indexes is usually faster than without. For extremely large data sets, local temporary tables might out perform them.
Next time you need a temporary object, try using a local table variable. I will be talking about creating long term persistent objects in tempdb, shortly.
I am just commenting to let you understand what a cool encounter our princess undergone using your web site. She picked up a wide variety of pieces, including what it is like to possess a very effective helping mindset to let many more effortlessly understand selected extremely tough subject areas. You undoubtedly did more than my desires. Many thanks for rendering those warm and helpful, trustworthy, revealing and also easy tips about your topic to Gloria.
Loving the info on this site, you have done outstanding job on the posts .