Table variables are table type variables which are used to create table like structure. They are partially stored in both memory and in tempdb database of a database server. Once the execution is finished, table variables are dropped automatically.
Why we need Table variables
When you are executing code in T-SQL, tables are necessary to store data temporarily to smooth your development, raise code maintainability and re usability.
As other variables, table variables are declared inside the code block of batch or stored procedures. Table variable store a set of records, so naturally the declaration syntax looks very similar to create table statement, as you can see in following example:
DECLARE @usersTblVar TABLE ( Id int, Name varchar(200) )
Insert, update, delete in table variable is same as we do with normal Table.
INSERT INTO @usersTblVar(id, name) values(1,’raam’)
UPDATE @usersTblVar SET name =’shyam’ where id =’1’
DELETE FROM @usersTblVar where id =’1’
You might think table variable work just like temporary tables, there are differences.
Note: you cannot use table variable like input and output parameter but you can return a table variable from a user defined function. The restricted scope of a table variable gives are database server some liberty to perform optimization.
Because of scope definition concept, a table variable use fewer resources than a temporary table. Table variables only last only duration of update on table variable which reduces locking and logging overhead. It also reduces the problem of recompilation of stored procedures which occurs with temporary tables.
You can also insert into a table variable in bulk as well for example:
INSERT INTO @usersTblVar SELECT * FROM usersBackup
It will insert data into table variable in bulk from usersBackup table.
Note: use table variable without @ in case of MySql.
Disadvantages of using table variables
- You cannot create a non-cluster index on a table variable.
- database server does not maintain statics on table variable.
- Table definition of table variable cannot be changed after declaration.