A table variable contains collections of data or records. It can be used in batches, stored procedures and user-defined functions. We can update the records in our table variable as well as delete the records. The variable will no longer exist after the procedure exits and there is no need to drop it like a temp table.
Like normal table and temp table, the constraints (primary key, check etc.) and identity column can be used with table variable. Declaration syntax of table variable is very similar to a create table statement.
- DECLARE @Student TABLE
- (
- ID int,
- Name NVARCHAR(100)
- )
Unlike temp table, a table variable, when used inside stored procedure, can avoid this recompilation hit, because using temp table in stored procedure may result in additional re-compilations of the stored procedure. It will generally uses fewer resources than a temporary table.
Table variables can do anything that temporary tables can do within the scope of a stored procedure, batch or UDF), but there are some drawbacks.
SQL Server does not maintain statistics on a table variable and statistics are used heavily by the query optimizer to determine the best method to execute a query. Neither of these restrictions should be a problem, however, as a table variables generally exist for a specific purpose and aren’t used for a wide range of ad-hoc queries.
Even non-clustered index can not be created on a table variable, unless the index is a side effect of a primary key or unique constraint on the table (SQL Server enforces any unique or primary key constraints, using an index).
After the declare statement of table variable, its definition cannot be changed. Alter table query attempts to alter a table variable will fail with a syntax error. Along the same lines, you cannot use a table variable with 'select into' or 'insert exec' queries. For using it in a join, you will need to alias the table in order to execute the query.
Summary
We discussed about SQL table variable, its merits and demerits. I hope this blog helps.