Database Reference
In-Depth Information
This time, when the temporary table is cached, table creation introduces just a few log records, all of which are
against the system table with no allocation map pages involved.
SQL Server does not cache IAM and data pages for global temporary tables nor does it cache local temporary
tables created in the session scope. Only the temporary tables created within the stored procedures and triggers
are cached.
There are also a few requirements of the table and code, including:
•
The table needs to be smaller than eight megabytes. Large tables are not cached.
•
There are no DDL statements that change the table structure. Any schema modification
statements in the code, with exception of
DROP TABLE
,
will prevent a temporary table caching.
However, you can create indexes on the table and, as mentioned previously, SQL Server will
cache them.
•
There are no
named
constraints defined in the table. Unnamed constraints will not prevent
the caching.
As you see, it is very easy to follow the guidelines that make temporary tables cacheable. This can significantly
improve performance and reduce tempdb allocation map pages contention on busy systems.
Table Variables
Despite the myth that table variables are in-memory objects, they are actually created and live in tempdb similar to
regular temporary tables. You can think about them as lightweight temporary tables, although their lightness comes
with the set of the limitations and restrictions.
The first major difference between temporary tables and table variables is the scope. Table variables live only
within the batch in which they were created. They are not accessible from outside of the batch, as opposed to
temporary tables. For example, when you define the table variable in a stored procedure, you are not able to reference
it from the dynamic SQL nor from other stored procedures called from the original one.
You cannot create indexes on table variables, with exception of primary key and unique constraints.
■
sQl server does not maintain any statistics on table variables, and it always estimates that a table
variable has just a single row, unless a statement-level recompile is used.
Important
Look at the example shown in Listing 12-10. In this example, we create a temporary table and table variable,
populate it with some data, and check SQL Server's cardinality estimations.
Listing 12-10.
Cardinality estimation for temporary tables and table variables
declare
@TTV table(ID int not null primary key)
create table #TT(ID int not null primary key)
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 CROSS JOIN N3 as T2) -- 256 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N4)
