{"id":1210,"date":"2011-11-15T17:27:29","date_gmt":"2011-11-15T17:27:29","guid":{"rendered":"http:\/\/craftydba.com\/?p=1210"},"modified":"2011-11-15T20:36:08","modified_gmt":"2011-11-15T20:36:08","slug":"temporary-tables-part-3","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1210","title":{"rendered":"Temporary Tables – Part 3"},"content":{"rendered":"

Today, I am going to talk about table variables<\/a>.<\/p>\n

In general, TSQL variables are defined by using the DECLARE <\/a>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.<\/p>\n

The declaration following this key word includes column definitions — names, data types, and constraints. The only constraint <\/a>types allowed in the declaration are PRIMARY KEY, UNIQUE, NULL, and CHECK. Indexes are a bi-product of a PRIMARY and UNIQUE constraints.<\/p>\n

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<\/a>, INSERT<\/a>, UPDATE<\/a>, and DELETE <\/a>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.<\/p>\n

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<\/a>. <\/p>\n

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.<\/p>\n

The following actions are performed by the code below.<\/p>\n

1 – Create local TABLE variable.
\n2 – INSERT data into table variable.
\n3 – SELECT current data from table variable.
\n4 – DELETE record three from table variable.
\n5 – UPDATE record five from table variable.
\n6 – SELECT current data from table variable.<\/p>\n

-- Database selection does not matter\r\nUSE model;\r\nGO\r\n\r\n-- Create local table variable\r\nDECLARE @MY_TABLE TABLE\r\n(\r\n  MyId INT PRIMARY KEY CLUSTERED,\r\n  MyValue VARCHAR(20)\r\n);\r\n        \r\n                \r\n-- Add data to local table variables\r\nINSERT INTO @MY_TABLE (MyId, MyValue) VALUES\r\n  (1, 'Continental'),\r\n  (2, 'Edsel'),\r\n  (3, 'Lincoln'),\r\n  (4, 'Mercury'),\r\n  (5, 'Ram');\r\n\r\n-- Show data from table A\r\nSELECT T.MyId, T.MyValue\r\nFROM @MY_TABLE AS T\r\n\r\n-- Remove record 3\r\nDELETE FROM @MY_TABLE\r\nWHERE MyId = 3;\r\n\r\n-- Show data from table A\r\nSELECT T.MyId, T.MyValue\r\nFROM @MY_TABLE AS T\r\n\r\n-- Update record 5\r\nUPDATE @MY_TABLE\r\nSET MyValue = 'Dodge Ram'\r\nWHERE MyId = 5;\r\n\r\n-- Show up dated data from table A\r\nSELECT MyId, MyValue\r\nFROM @MY_TABLE\r\nGO<\/span><\/pre>\n<\/p>\n
Unlike temporary tables, the scope of the variable is the batch.  Therefore, no clean up routines are needed to drop the table after use.<\/p>\n
I will be using the Adventure Works<\/a> Sample database supplied by Microsoft during the rest of this talk.<\/p>\n
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.<\/p>\n
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.<\/p>\n
-- Use the A.W. database\r\nUSE AdventureWorks2008R2\r\nGO\r\n\r\n-- Create local table variable\r\nDECLARE @MC TABLE\r\n(\r\n  MyCustomer INT,\r\n  MyDate DATETIME\r\n);\r\n   \r\n-- Local temp table (MC) - last sales order by customer in 2008 \r\nINSERT INTO @MC\r\nSELECT \r\n  CustomerID AS MyCustomer,\r\n  MAX(OrderDate) AS MyDate\r\nFROM \r\n  Sales.SalesOrderHeader\r\nWHERE\r\n  YEAR(OrderDate) = 2008 \r\nGROUP BY \r\n  CustomerID\r\n\r\n-- Get customer name, sales id, sub-total and order date\r\nSELECT \r\n  P.FirstName, \r\n  P.LastName,\r\n  SH.SalesOrderID, \r\n  SH.SubTotal,\r\n  SH.OrderDate\r\nFROM\r\n  Sales.SalesOrderHeader AS SH   \r\n  INNER JOIN @MC AS MC ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate\r\n  INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID\r\n  INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID\r\nORDER BY \r\n  SH.OrderDate ASC  \r\nGO\r\n<\/span><\/pre>\n<\/p>\n
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.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[240,224,33,12,30,15,223,37,28,226,29,32],"class_list":["post-1210","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-240","tag-declare","tag-delete","tag-free-code","tag-insert","tag-john-f-miner-iii","tag-local-tables-variables","tag-select","tag-sql-server","tag-table","tag-tsql","tag-update"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1210","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1210"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1210\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1210"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1210"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1210"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}