Table Variables – still a mystery

Geeking with table variables. Again.

Table variables have always been somewhat interesting to work with when developing in SQL Server. I remember when they came out in SQL Server 2000 we were all excited about trying them out, something new from SQL 7 that mainly sparked our interest I suppose. I mostly remember that there were a slew of KB articles, books, magazine articles, etc. each with various opinions and proclamations about them, such as table variables are faster than temp tables (or slower), or that there is less locking, blocking, or recompiles than temp tables, that they’re somehow in memory where temp tables are not, that they could not be indexed (or could be), or that you should use them for fewer rows while keeping # tables for the larger data sets…the list goes on and on and on. You remember some of these I know, and most of the time information was in conflict.

All of this had made them somewhat of a mystery. I began dorking with them this go-round because I wanted to 1) figure out what kind of object that they really were, with an idea that they were some sort of a temporary table, and 2) attempt to disprove an earlier fact or “myth” that you could not build statistics on them, knowing that you could in fact index them, either with primary key or unique constraint. (Many think that just because there are no statistics on them that an index is useless without them by the way, which is positively untrue, has been documented widely, and probably worth posting on in the future).

So, let’s figure out if we can answer these two questions. Run the following script, and do it in CTL + D (Grid) mode since that’s how I’ll be referencing the output. Essentially the script creates a temporary table, inserts rows, and then creates a table variable and inserts rows. Then, because table variables are run-time objects – meaning that they’re destroyed at the completion of the batch and not when closing a connection as are local temporary tables - we need to capture the metadata about them on the fly. We’ll attempt to put this data in a table called dbo.Stats, and then try to gather other information about them. This will allow us to answer questions one and two, above.

SET NOCOUNT ON

USE Test

IF OBJECT_ID('tempdb..#tab') IS NOT NULL

DROP TABLE #tab

CREATE TABLE #tab (id char(200))

INSERT #tab VALUES ('A')

DECLARE @tbl TABLE (TableVarID bigint IDENTITY , TableVarString char(467))

DECLARE @id INT

SET @id=1

INSERT INTO @tbl (TableVarString) VALUES ('A')

INSERT INTO @tbl (TableVarString) VALUES ('B')

INSERT INTO @tbl (TableVarString) VALUES ('C')

INSERT INTO @tbl (TableVarString) VALUES ('D')

INSERT INTO @tbl (TableVarString) VALUES ('E')

--'Creating Stats table'

IF OBJECT_ID ('Stats') IS NOT NULL

DROP TABLE Stats

CREATE TABLE Stats

(ID INT IDENTITY (1,1), TBL SYSNAME, OB AS OBJECT_ID(TBL))

USE tempdb

INSERT test..Stats (tbl)

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME LIKE '%#%'

USE Test

SELECT * FROM Stats

DECLARE @stat varchar (8000) , @ObjIdInQuestion int

SELECT @id = MIN(id) FROM Stats

SELECT @stat = tbl FROM Stats WHERE id=@id

DECLARE @str varchar (max)

,@help varchar (999)

WHILE (@id IS NOT NULL )

BEGIN

SELECT OBJECT_NAME(OBJECT_ID) as ObjName, @stat as TableName,

* from tempdb.sys.indexes WHERE OBJECT_ID =

(select id from tempdb.sys.sysobjects where name = @stat)

SELECT @ObjIdInQuestion = OBJECT_ID

from tempdb.sys.indexes WHERE OBJECT_ID =

(SELECT id FROM tempdb.sys.sysobjects WHERE name = @stat)

DECLARE @str1 varchar (max)

BEGIN TRY

SET @str1 = 'create statistics xxx'+ left(@stat, 10)+' on ' + @stat + '(id)'

PRINT 'Creating stats for ' + @stat

EXEC (@str1)

END TRY

BEGIN CATCH

PRINT '>>> Error occured creating stats:!'

SELECT ERROR_NUMBER() as Error_Messg

END CATCH

SET @str = 'UPDATE STATISTICS ['+ @stat + ']'

BEGIN TRY

USE tempdb

EXEC (@str)

IF EXISTS (SELECT * FROM sys.stats WHERE name = 'xxx'+@stat)

BEGIN

PRINT '>>> Successful update statistics!'

END

--USE Test

END TRY

BEGIN CATCH

SELECT @str + ': not found'

SELECT ERROR_NUMBER() as ErrorNum

,ERROR_MESSAGE() as ErrorMessage

,ERROR_PROCEDURE() as ErrorProcedure

,ERROR_STATE() as ErrorState

,ERROR_SEVERITY() as ErrorSeverity

,ERROR_LINE() as ErrorLine

END CATCH

SELECT 'SYSINDEXES',*

FROM tempdb.sys.sysindexes where id=@ObjIdInQuestion

SELECT 'SYSOBJECTS',*

FROM tempdb.sys.sysobjects where id=@ObjIdInQuestion

SELECT @id = MIN(id) FROM test.dbo.Stats WHERE id > @id

SELECT @stat = tbl FROM test.dbo.Stats WHERE id=@id

END

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

--EXEC sp_msforeachtable 'USE tempdb update statistics ?'

Figure 1. Table variables are in fact ‘temp tables’

Figure 2. Our temporary table in sys.sysindexes, sys.sysobjects

Figure 3. Our table variable and object id, also a heap

Figure 4. Error attempting to create statistics, and error ‘not found’ when updating statistics.

Figure 5. Success creating statistics on temp table, and error when creating on table variable (message tab in SSMS)

Figure 6. Our table variable as viewed in INFORMATION_SCHEMA.COLUMNS

From Figures 1-6, we can see that table variables are in fact ‘temp’ tables; they appear to be the same when viewing in system tables and views, and display all of the similar information that a table or temporary table might. You can see that I create and then update statistics on our temp table in the loop, and then the second pass in the loop, it fails to when attempting same on the table variable. Also interesting to note, while table variables have object_ids, they don’t get created in the same manner. Crack open SQL Profiler and verify – you will not see an object created for table variables during the execution of this script. I found this odd that Microsoft doesn’t show this.

Note that in Figure 3 we clearly see that our table exists, but when performing create statistics, the table is not found, as shown in figure 4. For fun, remove the comment on the exec sp_msforeachtable; you'll find that the script dies because the table does not exist. Again, we know that it exists from figures 1 and 6 because at runtime we interrogate system views and discover its existence. This is very clear in the INFORMATION_SCHEMA.COLUMNS view because we find the column names and data types that we created.

In conclusion, it is easy to see from the screenshots that table variables, while obviously not exactly the same as temporary tables, are represented in SQL Server as a temporary table, and have all of the same metadata in system tables as their counterparts, including naming type, object_ids, type, minlen, dpages, etc. From this test, we can also see that you cannot create any statistics on one of these tables to go along with constraints and indexes that we can place on them. Go ahead and add PRIMARY KEY CLUSTERED after TableVarID bigint IDENTITY in the script. Also, they do in fact reside in tempdb, so maybe we answered three questions here? Finally, I found nothing that differentiates them from temporary tables inside of any of the sys tables.

While nothing terribly exciting was uncovered here (such as my previous geeking with them when I realized that INSERT EXEC is now possible, beginning with SQL 2005), we at least clarified a few things…or maybe we didn’t.

Lee

----------------

Is it time for Thanksgiving Dinner already? I'm starving.