Database Reference
In-Depth Information
INSERT INTO @TempTable
(C1)
VALUES (42);
--Recompilation not needed
GO
EXEC dbo.TestProc; --First execution
Figure
17-15
shows the Extended Events output for the first execution of the stored procedure. The recompilation
caused by the temporary table has been avoided by using the table variable.
Figure 17-15.
Extended Events output showing the role of a table variable in resolving recompilation
However, table variables have their limitations. The main ones are as follows:
•
No DDL statement can be executed on the table variable once it is created, which means no
indexes or constraints can be added to the table variable later. Constraints can be specified
only as part of the table variable's
DECLARE
statement. Therefore, only one index can be created
on a table variable, using the
PRIMARY KEY
or
UNIQUE
constraint.
•
No statistics are created for table variables, which means they resolve as single-row tables in
execution plans. This is not an issue when the table actually contains only a small quantity
of data, approximately less than 100 rows. It becomes a major performance problem when
the table variable contains more data since appropriate decisions regarding the right sorts of
operations within an execution plan are completely dependent on statistics.
•
The following statements are not supported on the table variables:
INSERT INTO TableVariable EXEC StoredProcedure
•
SELECT SelectList INTO TableVariable FROM Table
•
SET TableVariable = Value
•
