Table variables in SQL Server

Working in SQL requires a mind-shift from procedural thinking to set-based thinking. Although you can get SQL to operate one record at a time, it’s usually slower, and almost always painful to force SQL to work like that.

One of the implications of set-based operations is that traditional single-value variables can’t quite handle many processing needs — you need a collection-like variable, and traditionally, that’s meant a temporary, or temp table. Temp table implementation is pretty similar across database engines, but it’s also slightly cumbersome. In many cases, SQL Server’s Table variables are a better solution.

Although Microsoft introduced table variables with SQL Server 2000, I hadn’t had occasion to try them out until recently. Like cursors, temporary tables have usually been just enough of a pain that I avoided them until I really needed one.


CREATE TABLE #mystuff
(
id INT,
stuff VARCHAR(50)
)

-- do stuff with your table

DROP TABLE #mystuff

The thing that always messed me up when using temp tables was the DROP TABLE at the end. When developing or debugging, or when working with ad-hoc SQL, I frequently missed the DROP, which showed up shortly afterwards when I tried to CREATE a table that already existed.

Table variables eliminate the need for the DROP, because they are reclaimed automatically as soon as they go out of scope:


CREATE TABLE @mystuff
(
id INT,
stuff VARCHAR(50)
)

-- do stuff with your table

Other advantages: for small tables, Table variables are more efficient than “real” temp tables, and they can be used in user-defined functions (unlike temp tables). Like temp tables, you can create constraints, identity columns, and defaults – plenty of power to help you work through set-based problems.

For more on Table variables, see the following articles:

Table Variables In T-SQL

Should I use a #temp table or a @table variable?