Temporary Tables – Part 3

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.

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.

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.

Related posts

2 Thoughts to “Temporary Tables – Part 3”

  1. 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.

  2. Loving the info on this site, you have done outstanding job on the posts .

Leave a Comment