Table Variable - Sql Server

Microsoft first time introduce Table variable in sqlserver 2000. it is alternative to using temporary table, but has some difference. Table variable acts like general table to  store, update, delete and delete records. Table variable store in TempDB in sqlserver. I will explain TempDB in later.

Table variable declaration similar to CREATE TABLE Statement

Declaration :

DECLARE @MyProducts TABLE

(

       Id                 INT

      ,ProductName  VARCHAR(200)

      ,Price              numeric(18, 0)

)

DML Operation: DML operation as it is general table operation. You can use tablevariables in batches, stored procedures, and user-defined functions (UDFs). We can UPDATE records in our table variable as well as DELETE records.

--Insert statement

INSERT INTO @MyProducts VALUES(0,'Pran',20);

--Using Northwind database

INSERT INTO @MyProducts

SELECT ProductID,ProductName,UnitPrice FROM Products

SELECT * From @MyProducts;

--UPDATE statement

UPDATE @MyProducts

  SET Price = Price +5

WHERE Price < 15

--DELETE statement

DELETE FROM @MyProducts

WHERE Price < 15

 --Select Update data top 5

SELECT TOP 5 *

FROM @MyProducts WHERE Price < 20

ORDER BY ProductName DESC

Other table features:

You can use UNIQUE, CHECK and also PRIMARKEY key constraint in Table variable.

DECLARE @MyProducts TABLE

(

 Id  INT

,ProductName  VARCHAR(200) UNIQUE

,Price  numeric(18, 0) CHECK (Price >10)

)

-- Unique constrain check

INSERT INTO @MyProducts VALUES (1,'Pran',20);

INSERT INTO @MyProducts VALUES (1,'Pran',24);

-- Chek constrain check

INSERT INTO @MyProducts VALUES (1,'Pran',20);

INSERT INTO @MyProducts VALUES (1,'Pen',6);

Limitations and restrictions:

• Table variables does not have distribution statistics, theywill not trigger recompiles. Therefore, in many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100).

•  Table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.

• The table definition of a table variable cannot change after the DECLARE statement. Any ALTER TABLE query attempting to alter a table variable will fail with a syntax error. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. f you are using a table variable in a join, you will need to alias the table in order to execute the query.

• Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics.
• CHECK constraints, DEFAULT values and computed columns in the table type declaration cannot call user-defined functions.
• Assignment operation between table variables is not supported.
• Because table variables have limited scope and are not part of the persistent database, they are not affected by transaction rollbacks.
• Table variables cannot be altered after creation.
• 
  

      Continue

         Used in Store Procedure:

        Used In User defined function: