Hello friends, In this tutorial, we are going to learn Table Variable in SQL Server. Table variable was introduced with SQL Server 2000 as an alternate for Temporary tables. We can use Table Variable to store a set of records temporally.
Table Variable in SQL Server:
We can use Table Variable to store a set of records temporally. Unlike other data type table, a variable cannot use as an input or output parameter like other data types. As we use variables in SQL server to store some values with the different data type in the same way we can use Table Variable for storing the set of record that exists only for the current scope. Table Variables are declared into the current batch or stored procedure with the Declare Statement.
Syntax for declaring Table Variable:
As you can see from the above syntax declaring a Table Variable is very similar to create table statement.
The scope of Table Variable:
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 scope of the Stored Procedure, batch or User-Defined function.
Because of the limited scope table variable uses fewer resources than the temporary table. A temporary table inside a stored procedure requires additional recompilation whereas the table variable does not require this additional recompilation of stored procedure.
Let’s start by creating a simple Employee table here is the script.
Create Employee Table:
Declaring a Table Variable:
I am going to declare a Table variable. The Table variable has the same columns as the Employee Table.
Copy Records to the @tblEmployee variable from Employee Table:
Run the below script to see how to insert a record into Table Variable.
Update Operation On Table Variable:
Delete Operation on Table Variable:
- User Defined Function to Calculate Age from Date Of Birth in SQL Server.
- SQL Queries Example.
- How to Call SQL Server Stored Procedure in C#
- How to show only Odd or Even Rows in SQL Server?
I hope you would love this post. Please don’t hesitate to comment for any technical help. Your feedback and suggestions are important to me.