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

Comments on this post

# re: When is a variable not a variable?

Requesting Gravatar...
Here's a little demo script I use to prove my point, when discussing this with those who don't believe the KB article could be wrong (c:

Cheers,
Greg

set nocount on
go

use tempdb

declare @t table (c1 int identity)
checkpoint

insert into @t default values
insert into @t default values
insert into @t default values

select * from ::fn_dblog(null, null)
where operation = 'LOP_INSERT_ROWS'


create table #t (c1 int identity)
checkpoint

insert into #t default values
insert into #t default values
insert into #t default values

select * from ::fn_dblog(null, null)
where operation = 'LOP_INSERT_ROWS'

drop table #t
Left by Greg Linwood on Mar 27, 2006 6:10 PM

# re: When is a variable not a variable?

Requesting Gravatar...
Cool, Thanks Greg!
Left by Darren Gosbell on Mar 27, 2006 6:36 PM

# SQL Server Myths

Requesting Gravatar...
The whole area of "SQL Server myths" is an interesting one. One of the fascinations of working with SQL...
Left by Tony Davis on Jun 28, 2007 1:26 AM

Your comment:

 (will show your gravatar)