12 August 2010

Temporary Tables vs. Table Variables – Stored in TempDB

    Yesterday I started to read Ken Henderson’s book, SQL Server 2005 Practical Troubleshooting: The Database Engine, diving directly into tempdb topic (Chapter 9, Tempdb issues). He mentions that metadata are created in system tables when a temporary table is created (see p.415). This means that when a temporary table is created, a record must be created in tempdb’s sys.tables and sys.columns system table, the respective records being deleted when the table is dropped. As I never looked at how the metadata of a temporary table look like, I thought is the case to do something in this direction, and here’s the code created for this purpose:
-- creating the temporary tables
CREATE TABLE #temp (
   id int NOT NULL
,
value nvarchar(50) NOT NULL)


-- retrieving the metadata
SELECT t.name table_name
,
s.name column_name

,
t.type

,
t.type_desc

,
t.create_date

FROM
tempdb.sys.tables t
    JOIN tempdb.sys.columns s
      ON t.object_id = s.object_id

WHERE
t.name LIKE '%temp%'


-- dropping the temporary table
DROP TABLE #temp
temp vs variable tables - temporary example 

Note:
    By changing the width of table_name column could be seen that object’s name corresponding to the temporary tables is a combination from table’s name and, according to K. Henderson, the number designating the connection that owns the table.

    If the temporary table is stored in tempdb and metadata are stored about it, what’s happening with a temporary table? Here’s the answer:
-- creating the table variable
DECLARE @temp TABLE(
   id int NOT NULL

, value nvarchar(50) NOT NULL)

-- retrieving the metadata
SELECT t.name table_name
,
s.name column_name

,
t.type

,
t.type_desc

,
t.create_date

FROM
tempdb.sys.tables t
     JOIN tempdb.sys.columns s
      ON t.object_id = s.object_id

WHERE
t.name LIKE '%#%'

   AND DateDiff(ss, t.create_date, GetDate()) BETWEEN -2 AND 2

temp vs variable tables - table variable example

    As can be seen I had to put a little more effort in order to see a table variable’s metadata. As there is no name that could be used in order to identify the table, as object’s name is stored as a hex number, I had to restrain the list of tables by using the timestamp. Excepting the name, the metadata stored about the two types of tables are identical for the same table definition. Actually their definition is similar with the one of a “standard” table:
-- creating a "standard" table
CREATE TABLE temp(

  id int NOT NULL
,
value nvarchar(50) NOT NULL)


-- retrieving the metadata
SELECT t.name table_name

,
s.name column_name

,
t.type

,
t.type_desc

,
t.create_date

FROM
sys.tables t
     JOIN sys.columns s
      ON t.object_id = s.object_id

WHERE
t.name LIKE '%temp%'


-- dropping the table
DROP TABLE temp

temp vs variable tables - standard table example

Notes: 
    For exemplification I used a restrained list of attributes, when comparing the various table’s metadata could be used instead a SELECT * statement. The above examples reflect also the differences in declaring the three types of tables.

No comments: