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
Example
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.
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.
4) Table variable not allows creating index on table.
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.
7) We cannot insert data in table variable using INTO keyword.
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 .
