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: