Transact SQL – Table Variable

 

Background

Transact SQL allows one to create tables on the fly.

Currently the two supported variants are temporary table ( session or global ) or Table variable.

Table Variable Versus Temp Table

  1. Lifetime
    • Table Variable can only be session specific
    • Temp table can be session and global
  2. Statistics
    • On Temp Tables, Statistics can be updated
    • Not so, on table variable

Table Variable

Sample

declare @tblStat TABLE
(
      [id]      smallint not null identity(1,1)
    , [object]  sysname  not null
    , [stat]    sysname  not null
    , [columns] nvarchar(4000) not null

    , [objectID] int null

    --  as object_id([object])
    
    , primary key
        (
            [id]
        )

    , unique 
        (
              [object]
            , [stat]  
        )
        WITH 
        (
            IGNORE_DUP_KEY = ON
        )

    , index [IDX_Columns]
        (
              [columns]
        )


)


Explanation

  1. Columns
    • object_id
      • Defined as null
        • Will be populated later
  2. Primary Key
    • Columns :- ID
  3. Unique Constraint
    • [object], [stat]
      • ignore dup key
  4. Index
    • INDX_Columns
      • [columns]

Unsupported

  1. Constraints
    • Supported
    • Unsupported
      • Named Constraints
  2. Index
    • Supported
      • NonClustered Index
    • Unsupported
      • Clustered Index

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s