Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 215

You can do the same test but with a temporary table instead by executing this code:

CREATE TABLE #TempTable ( c1 INT ) ;
INSERT INTO #TempTable
SELECT TOP 1000000 row_number( ) OVER ( ORDER BY t1.number ) AS N
FROM master..spt_values t1
CROSS JOIN master..spt_values t2 ;

SELECT COUNT(*)
FROM #TempTable ;

The properties for the table scan in this scenario are shown in
Figure 8-2, which indicates an accurate row estimate of 1000000.

Indexes
You can't create indexes on table variables although you can
create constraints. This means that by creating primary keys or
unique constraints, you can have indexes (as these are created to
support constraints) on table variables.

Even if you have constraints, and therefore indexes that will have
statistics, the indexes will not be used when the query is compiled
because they won't exist at compile time, nor will they cause
recompilations.

Schema Modifi cations
Schema modii cations are possible on temporary tables but
not on table variables. Although schema modii cations are
possible on temporary tables, avoid using them because they cause recompilations of statements that
use the tables.

FIGURE 8-2

Table 8-1 provides a brief summary of the differences between temporary tables and table variables.

TABLE 8-1: Temporary Tables versus Table Variables

TEMPORARY TABLES TABLE VARIABLES

Statistics Yes No

Indexes Yes Only with constraints

Schema modifi cations Yes No

Available in child routines including sp _ executesql Yes No

Use with INSERT INTO … EXEC Yes No

In memory structures No No