Database Reference
In-Depth Information
Figure 12-6.
Cardinality estimations for temporary tables and table variables
Regular temporary tables maintain statistics on indexes and, as a result, SQL Server was able to estimate the
number of rows in the first select correctly. As previously, without a statement level recompile, it is assumed that the
table variable has only a single row. Nevertheless, even with a statement level recompile, the estimations were way
off. There are no statistics, and SQL Server assumes that the
greater
operator will return one-third of the rows from the
table, which is incorrect in our case.
Another difference between temporary tables and table variables is how they handle transactions. Temporary
tables are fully transaction-aware, similar to the regular tables. Table variables, on the other hand, only support
statement-level rollback. Any statement-level errors, for example “key violation,” would rollback the statement.
Although explicit transaction rollback keeps the table variable data intact.
Let's look at a couple of examples. In the first example, we will produce a
primary key violation error
during the
insert operation. The code for this is shown in Listing 12-11.
Listing 12-11.
Temporary table variables: statement-level rollback
declare
@T table(ID int not null primary key)
-- Success
insert into @T(ID) values(1)
