Table Variables 101

Table variables fall in one of those areas where lack of knowledge and understanding can get you in trouble. I’m leaving the creative part of getting in trouble up to you and here, I will show you a few things I didn’t know about table variables.

Table variables exist in tempdb regardless of their size. I have found in several places on the web people making statements such as temporary tables exist in tempdb and table variables exist in memory. Another reason why we must do our homework and conduct our own tests.

DECLARE @t TABLE (
        RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int)

 SELECT *
   FROM tempdb.sys.objects
  WHERE [type] = 'U'

INSERT INTO @t (
       RequiredDate
     , ShippedDate
     , CustomerID)
VALUES ('20120122', '20120114',4)
     , ('20120122', '20120114',1)
     , ('20120122', '20120114',5);

SELECT * 
  FROM @t;

You can use the IDENTITY column property

DECLARE @t TABLE (
        OrderID int IDENTITY(1,1)
      , RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int)

INSERT INTO @t (
       RequiredDate
     , ShippedDate
     , CustomerID)
VALUES ('20120122', '20120114',4)
     , ('20120122', '20120114',1)
     , ('20120122', '20120114',5);

SELECT *
  FROM @t;

You can define a primary key

DECLARE @t TABLE (
        OrderID int IDENTITY(1,1) PRIMARY KEY
      , RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int)
 
INSERT INTO @t (
       RequiredDate
     , ShippedDate
     , CustomerID)
VALUES ('20120122', '20120114',4)
     , ('20120122', '20120114',1)
     , ('20120122', '20120114',5);
 
SELECT * FROM @t;

You can define a clustered key other than the primary key.

DECLARE @t TABLE (
        OrderID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
      , RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int,
 UNIQUE CLUSTERED (CustomerID, OrderID))

You can create a check constraint

DECLARE @t TABLE (
        OrderID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
      , RequiredDate datetime NOT NULL
      , ShippedDate datetime NULL
      , CustomerID int,
 UNIQUE CLUSTERED (CustomerID, OrderID),
 CHECK (RequiredDate BETWEEN '20131001' AND '20131031'))

Now, just because you can do all these things with table variable you shouldn’t go and define every table variable with all these properties. You should always practice good database design.