May 25, 2016

SQL tip #3: consider a table variable

By

Theta

Most developers know the # preceding a local temporary table very well, as temporary tables are really useful when you need to keep some stuff in a place for a moment while you do something else. But you may not know there are quite a lot of processes that go into making this table, or that these can really inhibit the reuse of execution plans. So a harmless #temp may be bloating your memory or putting extra pressure on your TempDB and you might not even know about it.

As an alternative, consider table variables. Of course, table variables (preceded by “@”, like @table) have their own set of special needs and are ‘private’, so only the process that creates them can access them. But they are really fast on smaller data loads (generally less than 2500 rows) and they lead to far fewer recompiles on your queries. They also get disposed of after an execution, so they clean up after themselves quite nicely. Table variables also go quite nicely with Table-Valued Parameters when you are working with sp_executesql. As the Dynamic SQL statements are generated in a different context than the source statement you will need to be clever in passing data into the Dynamic SQL statement. The reading bit is easily done with an INSERT INTO @table (xx) EXECUTE sp_executesql @SQL.

Try a table variable next time you want to use a temporary storage space in a query, or see if you have queries with a bad mixture of recompile counts and temporary tables and see if you can rework them.

For more information on using table (and other) variables...