Tags


Hello everyone, I am sorry for not writing anything for nearly 4 weeks. I was little busy with some of the projects that I was working on. Now that I have some time so I thought of continuing where I left. In my last blog I tried to explain the uses and features of Temp tables in SQL Servers. In this blog we will discuss about Table Variables in SQL Server.

Table Variables were introduced by Microsoft with SQL Server 2000 as an alternative to Temp tables. Table variables are stored in memory but act as a table. Table variables are partially stored on disk and partially stored on memory. Because table variables are partially stored in memory, the access time for a table variable can be faster than Temp tables in certain scenarios.

I have noticed that when we talk about table variables to the developers, they have a notion that table variables are best options when compared to Temporary tables which is not true every time.

Lets start with the basics about Table variables:

Table Variables:

— Creating a table variable

DECLARE @First_Table_Variable TABLE

(

RowId INT IDENTITY (1,1),
My_Name VARCHAR(100),
My_ADDRESS VARCHAR (100)

)

Features of Table variables:

1) Transactions: Table variables are not bound to any transactions as they are just like any other variable.

I can explain this with the help of an example:

BEGIN TRAN

— Declaring the Table variable

DECLARE @First_Table_Variable TABLE

(

My_Name VARCHAR(100),
My_ADDRESS VARCHAR (100)

)

— Inserting records in the Table Variable

INSERT INTO @First_Table_Variable values (‘Subhro Saha’, ‘INDIA’)

INSERT INTO @First_Table_Variable values (‘Samantha’,’NEW YORK’)

— Rolling back the Transaction. Normal tables or Temp tables would lose every records.

ROLLBACK

SELECT * FROM @First_Table_Variable — We will get the two records that we inserted.

2) No SELECT INTO: We cannot use a SELECT INTO with Table Variables in SQL Server 2000 though the feature is available with Table Variables in SQL Server 2005. Likewise, we can also have INSERT INTO working with Table Variables against a SELECT but not against an EXEC Stored Procedure.

3)No ALTER TABLE Variable: We cannot ALTER a Table once it has been declared. This may look a little rigid but remember that recompilation can come out like wild-fire when there are DDL (Data Definition Language .i.e Schema) changes and therefore, this helps to avoid recompilation.

4) Scope: Just like any other variable, a Table Variable’s scope exists only within the context of the current level. Therefore, unlike Temp Tables, it is not accessible to sub levels (of Stored Procedures).

5) Minimum Constraints: A Table Variable permits us to use only the PRIMARY KEY, UNIQUE KEY and NULL constraint only. What this implies behind the scenes is that we can have unique indexes. The only possibility of creating a non-unique index is if we add attributes and make that blend unique and have a PRIMARY KEY or a UNIQUE KEY on the combination we just made.

6) Table Variables And The TempDb: This is a very common myth that Table Variables have nothing to do with TempDb and therefore, they have no physical representation in the TempDb and therefore, they reside in ONLY memory and therefore they’re the best option for efficient processing.

Not entirely true. Table Variables do indeed have a physical representation within the TempDb and this can proved with a simple query in your database against the TempDb:

SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘%#%’ — no result

DECLARE @First_Table_Variable TABLE

(

My_Name VARCHAR(100),
My_ADDRESS VARCHAR (100)

)

SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ‘%#%’ — notice that a new #something exists in TABLE_NAME column

So yes, Table Variables do have a physical representation within the TempDb. So how does this affect our understanding of Temp Tables residing in TempDb and Table Variables also residing in TempDb?

The fact of the matter is that this depends entirely on the scenario and in certain cases, Temp Variables are faster and in certain cases, Temp Tables are faster. Table Variable’s content will reside in memory when it is available but when that limit gets crossed, it will resort to physical I/O.

I hope now you have fair knowledge and idea about Table variables. Please feel free to raise any points and we can discuss those points.

Till then keep learning and keep coding!!

Thanks

Subhro Saha

Advertisements