In many cases a table variable is used to outperform a solution using a temporary table, although we will need to review the strengths and weaknesses of each in this given blog.
Table variables are mainly used to store a set of records, so the declaration syntax looks similar to a CREATE TABLE statement.
We can use table variables in functions, stored procedures, and batches. To declare table variables, we have to use DECLARE @local_variable.
DECLARE @ProductTotals TABLE ( ProductID int, Revenue money )
table variables can be referenced by name in the FROM clause of a batch, as shown the following example:
SELECT Employee_ID, Department_ID FROM @MyTableVar;
If we want to populate table variable we could write the following SELECT statement.
INSERT INTO @ProductTotals (ProductID, Revenue) SELECT ProductID, SUM(UnitPrice * Quantity) FROM [Order Details] GROUP BY ProductID
We can UPDATE the records as well as DELETE the records in our table variable.
UPDATE @ProductTotals SET Revenue = Revenue * 1.50 WHERE ProductID = 46 DELETE FROM @ProductTotals WHERE ProductID = 6 SELECT TOP 10 * FROM @ProductTotals ORDER BY Revenue DESC
table variables are not like temporary tables (CREATE TABLE #ProductTotals), but there are some differences.
Unlike the other data types in SQL Server, you cannot use a table variable as an input or an output parameter.
a table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement.
The table variable will no longer exist after the procedure exits - we won't have to clean up our table variable with a DROP statement.
We cannot use a table variable as an input or output parameter, but we can return a table variable from a user-defined function.
Because of the scope, a table variable will use very fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there are less locking and logging overhead.
If we use a temporary table inside a stored procedure will take an additional recompilation of the stored procedure. Table variables can often avoid this recompilation.
Constraints are an excellent way to ensure the data in a table meets specific requirements, and we also can use constraints with table variables. The following example ensures ProductID values in the table will be unique, and all prices are less than 1000.
DECLARE @MyTable TABLE ( ProductID int UNIQUE, Price money CHECK(Price < 1000) )
You can also declare primary keys. identity columns, and default values.
UPDATE @ProductTotals DECLARE @MyTable TABLE ( ProductID int IDENTITY(1,1) PRIMARY KEY, Name varchar(20) NOT NULL DEFAULT('Unknown') )
table variables can do anything temporary tables can do within the scope, but there are some drawbacks.
We cannot create a non-clustered index on a table variable unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table.
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.
The table definition of a table variable cannot change after the DECLARE statement. Also, we cannot use a table variable with SELECT INTO or INSERT EXEC queries. If you are using a table variable in a join, you will need to alias the table in order to execute the query.
SELECT ProductName, Revenue FROM Products P INNER JOIN @ProductTotals PT ON P.ProductID = PT.ProductID
Choosing Between Temporary Tables and Table Variables:
how do you decide on using a table variable or a temporary table?
1) we know there are situations that which demand to use a temporary table. This includes calling nested stored procedures which use the resultset, certain scenarios using dynamic SQL, and cases where you need transaction rollback support.
2) The size of the resultset will determine which solution to choose. If the table stores a resultset so large you require indexes to improve query performance, you’ll need to stick to a temporary table. In some borderline cases try some performance benchmark testing to see which approach offers the best performance. If the resultset is small, the table variable is always the optimum choice.