SQL Table Variables
Table variables, like their cousin temp tables are variables not intended to be permanent residents in your database. The definition of both temp tables and table variables reside in TempDB. Generally, @ table variables declared are maintained only in memory. A # or ## table resides as long as your database connection continues to be open.
They are often used for:
- Instances where you need to use a cursor instead of creating the cursor on a permanent table
- Breaking down a complicated query to achieve better performance
- Temporary staging for work
- Simplifying complicated SQL
# and ## Tables may be manipulated just like permanent tables from the perspective of constraints and indexes. By contrast, @ Tables only support UNIQUE constraint, and that not named. They do support composite constraints so you can emulate a Unique Clustered index should you need to use them in that fashion.
For this reason I find myself often using a # table rather than an @ table in those situations where I may need to define specific indexes for one reason or another. The down side of using # tables is the load it places on tempdb. Overuse of # Tables can cause your performance to degraded instead of your original intention.
A nice takeaway is that if your database has adequate memory, try using @ table variables first. You might write the code using # tables because they remain between executions. But in the final code try using @ table variables. If the performance is good, you are done. Otherwise, you might want to consider trying @ table variables with individual constraints or composite constraints.
That’s my Accidental DBA tip for now.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Tips for using alerts in SQL Server 2012
In this article, you can find some useful tips to work with SQL Server 2012 alerts.
Featured White Paper(s)
Key Considerations in Evaluating Data Warehouse Appliances
read more)