Table Variable is a special data type. In the query we can create and insert a result set into the table variables and use it again later.
The Table Variable is similar to the Temp tables. But there are some differences.
You can find the differences between Temp tables and Table Variables in the article “Differences Between Temp Table and Table Variable“.
You can create a Table Variable as follows, add a record, and select.
|
1 2 3 |
DECLARE @TableVariableTablo table(TableVariableKolon varchar(50)) Insert Into @TableVariableTablo VALUES('testkayıt') Select * From @TableVariableTablo |
In the above script, you can transfer the records in your table to the Table Variable by typing another select expression instead of testkayıt.
Table Variables are deleted when the stored procedure or function is finished.
You can not transfer data to the table variable with the expression Select INTO.
There is no transaction logic in Table Variable. After BEGIN TRANSACTION, you can not ROLLBACK.
When calculating a query plan for a query made to a table of type Table Variable, SQL Server thinks that there is no record in the table and prepares the query plan accordingly.
So if you put your large datasets into the Table Variable, you will have performance bottlenecks.
You can use the temp table for large datasets.
Once created, you can not alter the Table Variable.
Before SQL Server 2014, indexes could be created with Primary Key and Unique Constraints.
Non unique index could not be created. With SQL Server 2014, the non unique index can be created as follows.
|
1 2 3 4 5 |
DECLARE @TableVariableTablo TABLE ( Kolon1 INT INDEX IX1 CLUSTERED, Kolon2 INT INDEX IX2 NONCLUSTERED, INDEX IX3 NONCLUSTERED(Kolon1,Kolon2) /*An index containing Kolon1 and Kolon2*/ ); |
Basically you can think of Table Variables and Temp Tables doing the same thing.
But if you examine it in detail, you will see that there are a lot of differences.
You may want to look at the article titled “Differences Between Temp Table and Table Variable“.
