Table Variable

SQL Serve 2000 introduces a new variable named table. You can say table variable is an alternative to temporary table. Which is better in both it depends on scenario somewhere table variable is good and somewhere temporary table.

You can save record in table variable

Declaration:

DECLARE @SalaryDetail TABLE

(

  Basic int,

  PF int,

  EmpId varchar

)

Fill the table

Insert into @SalaryDetail (Basic,PF,EmpId) Select Basic,PF,EmpId from Salary.

Table variables can be used in batches, stored procedures, and user defined functions. It also can be update and delete as well.

Update @SalaryDetail Set PF=PF*8/100 where EmpIt=’2’

Delete from @SalaryDetail where EmpId=’1’

Select Top 10 from @SalaryDetail OrderBy Basic Desc

Scope: Table variable cannot use as input and output parameter in procedure or function. Just like other variable table variable also no longer exist after the procedure exits. No need to drop the table (table variable).

A user defined function can return a table variable.

Performance: A table variable will generally use fewer resources in comparison of temporary variable cause is its well-defined scope. Transactions lock the table variable at last moment so less locking time.

There is no need to recompile the stored procedure in case of table variable unlike temporary table.

Other Features:

//////////////////////////////////////////////////////////////////////////////////////

Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit. For more information on why stored procedures may recompile, look at Microsoft knowledge base article 243586 (INF: Troubleshooting Stored Procedure Recompilation).

Other Features

Constraints are an excellent way to ensure the data in a table meets specific requirements, and you can use constraints with table variables. The following example ensures ProductID values in the table will be unique, and all prices are less then 10.0.

DECLARE @MyTable TABLE (   ProductID int UNIQUE,   Price money CHECK(Price < 10.0) )

You can also declare primary keys. identity columns, and default values.

UPDATE @ProductTotals DECLARE @MyTable TABLE (   ProductID int IDENTITY(1,1) PRIMARY KEY,   Name varchar(10) NOT NULL DEFAULT('Unknown') )

So far it seems that table variables can do anything temporary tables can do within the scope of a stored procedure, batch, or UDF), but there are some drawbacks.