Table Variable in SQL Server

Table Variable in SQL Server store a set of records like SQL tables, and these are best alternative to Temp Tables. Like Local Variables, Table variable scope is limited to User Defined Functions, or Stored procedures. Table variable is very fast when compared to temporary tables, and it is recommended to use this for less amount of data.

Table Variable in SQL Server Syntax

The syntax of the SQL Server Table variable is similar to the Create Table Statement

TIP: You cannot use a table variable as either Input, or an Output Parameter.

Table Variable in SQL Server Example

In this example we will show you, How to Create a table variable, How to insert new records into it, and how to select records from the table variable.

SQL CODE

OUTPUT

As you can see from the above screenshot, we are executing all the statements together. let me show you, what will happen when we execute them independently.

OUTPUT

There won’t be any problem to declare a table variable

OUTPUT

Hope you understood the scope of a table variable.

NOTE: You don’t need any DELETE, or DROP statement to drop the table variable. Database Engine will automatically delete when it exit from the scope

Thank You for Visiting Our Blog