When is a variable not a variable?

 

When it is a SQL Server table variable! This topic came up a little while ago on the SQLDownUnder mailing list and surprised me because it went against most things I had heard about table variables. In fact a few days later I got a newsletter from SQLServerCentral.com with the following tip in it:

If you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table instead. Table variables are created and manipulated in memory instead of the tempdb database, making them much faster in some cases. But not in all cases. Because of this, you will need to test both options to determine which works best for you under your particular circumstances.

In addition, table variables found in stored procedures result in fewer compilations (than when using temporary tables), and transactions using table variables only last as long as the duration of an update on the table variable, requiring less locking and logging resources. [2000]

But according to Greg Linwood (SQL Server MVP) table variables actually get persisted through to tempdb. In fact there has apparently been a discussion between the product team and some of the MVP's as to whether this behaviour should be changed so that they are only held in memory. As a few of us have been discussing, there would most likely be value in having a memory only structure, but changing the current behaviour of table variables could have a negative impact on existing applications.

See the full forum discussion here: http://www.sqlserver.org.au/forums/ShowPost.aspx?PostID=1039

Print | posted on Sunday, March 26, 2006 5:54 PM