Table Variables

Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. In many cases a table variable can outperform a solution using a temporary table. Table variables are a bit like temporary tables, but they work mainly in memory. It is essentially creating a variable that has the structure of a table.

Syntax

Create Table Variable Type

GO

CREATE TYPE [dbo].[EMPLOYEE_MSTR_TYPE] AS TABLE(

      [ID_EMP] [int] NULL,

      [EMP_NAME] [varchar](50) NULL,

      [EMP_SALARY] [numeric](18, 2) NULL

)

GO

Using of Table Variable Type in Store Procedure

CREATE PROCEDURE [dbo].[EMPLOYEE_SAVE]

(         

    @EMPLOYEE_MSTR_TYPE AS [DBO].[EMPLOYEE_MSTR_TYPE] READONLY

)         

AS         

            

BEGIN   

SET NOCOUNT ON; 

INSERT INTO EMPLOYEE_MSTR (EMP_NAME, EMP_SALARY)

SELECT EMP_NAME, EMP_SALARY

FROM  @EMPLOYEE_MSTR_TYPE

SET NOCOUNT OFF; 

END

Code sample to pass Data Table to store procedure as parameter

public void Save_Employee(DataTable dtEmployee,int idEntity)

       {

            try

            {

                string sqlCommand = "EMPLOYEE_SAVE";

                Database db = DatabaseFactory.CreateDatabase();

                DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

                SqlParameter parameter1 = new SqlParameter();

                Parameter1.ParameterName = "@EMPLOYEE_MSTR_SAVE";

                Parameter1.SqlDbType = System.Data.SqlDbType.Structured;

                Parameter2.Value = dtEmployee;

                dbCommand.Parameters.Add(parameter1);

               

                db.AddInParameter(dbCommand, "ID_ENTITY", DbType.Int32, idEntity);

                db.ExecuteNonQuery(dbCommand);

            }

            catch (Exception)

            {

                throw;

            }

        }

Scope

A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits there will be no table to clean up with a DROP statement. Because of the well-defined scope, a table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.

Advantages

• A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
• If you have a large amount of data which need to be inserted or modified in bulk, in that case table variables will help a lot. As it has an ability to define structure of the table.
• Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
• Table variables are automatically cleaned up for you at the end of the function, stored procedure, or batch in which they are defined
• Table variables need fewer resources for locking and logging, partially because any transactions involving them last only for the duration of an update on the variable

Limitations

• You cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table (SQL Server enforces any UNIQUE or PRIMARY KEY constraints using an index).
• SQL Server does not maintain statistics on a table variable, and statistics are used heavily by the query optimizer to determine the best method to execute a query. Neither of these restrictions should be a problem, however, as table variables generally exist for a specific purpose and aren’t used for a wide range of ad-hoc queries.
• 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. if you are using a table variable in a join, you will need to alias the table in order to execute the query.

About Author:

Mukesh Mange is a consultant in Systems Plus Pvt. Ltd. Within Systems Plus, he actively contributes to the areas of Technology and Information Security. He can be contacted at mukesh.m@spluspl.com