Table variable in SQL Server - WebNetGuru.com

Table variable in SQL Server


Table variable in SQL server create table over temporary table or physical table . It is not similar to temporary table or physical table. It is similar to temporary table but it create using variable. Below are characteristics of table variable in SQL server.

This is table variable so syntax to create is different from create table.

1) It's variable so syntax is similar to declare the variable in SQL Server.
SQL Server use @ symbol to declare the variable or table variable.

Syntax to create table using table variable

DECLARE < @ table Name > < TABLE data Type >
(
column1 < data type > ,
column2 < data type > ,
column3 < data type >
)

Example

DECLARE @tempTable TABLE
(
Id INT ,
StudentName VARCHAR (100) ,
Std TINYINT ,
ContactNumber VARCHAR (10)
)

Execute above statement, so new table variable get created.


2) Table variable not store the table structure in tempdb like a temporary table, it's variable so, it gets created in current connection / session scope.

create table using table variable in sql server

3) Scope of this table variable is limited to current session or current connection.

Insert one record in table variable, and try to access in another connection, it will not get accessible, and it will give below error.

scope of table variable in sql server

4) Table variable not allows creating index on table.

index on table variable in sql server

5) Table variable get remove automatically from SQL Server when current connection is closed.

6) We cannot drop table which is created using table variable, like a physical table or temporary table because it is not get created in SQL Server.

can we drop table variable in sql server.?

7) We cannot insert data in table variable using INTO keyword.

we can not use into to insert data using table variable in sql server

To read more about table variable in SQL Server refer Microsoft.

Note : When to use table variable in SQL Server (@table)?
When are we going to store small amount of record in temporary table for current scope / connection.


Note : above session is nothing but connection (query editor) in SQL Server.

Learn what is difference between table variable and temp table in SQL Server temporary table .
Move Top