Database Reference
In-Depth Information
Table
12-2
illustrates how the tables will look.
Table 12-2.
Sample Tables
Table Test1
Table Test2
Column
Test1_c1
Test1_C2 Test2_c1
Test2_C2
Row1
1
1
1
2
Row2
2
2
2
1
RowN
N
2
N
1
Rowl000l
10001
2
10001
1
To understand the importance of statistics on a nonindexed column, use the default setting for the auto create
statistics feature. By default, this feature is on. You can verify this using the
DATABASEPROPERTYEX
function (although
you can also query the sys.databases view).
SELECT DATABASEPROPERTYEX('AdventureWorks2012',
'IsAutoCreateStatistics');
■
Note
you can find a detailed description of configuring the auto create statistics feature later in this chapter.
Use the following
SELECT
statement to access a large result set from table
Test1
and a small result set from table
Test2
. Table
Test1
has 10,000 rows for the column value of
Test1_C2 = 2
, and table
Test2
has 1 row for
Test2_C2 = 2
.
Note that these columns used in the join and filter criteria have no index on either table.
SELECT Test1.Test1_C2,
Test2.Test2_C2
FROM dbo.Test1
JOIN dbo.Test2
ON Test1.Test1_C2 = Test2.Test2_C2
WHERE Test1.Test1_C2 = 2 ;
Figure
12-7
shows the execution plan for this query.
Figure 12-7.
Execution plan with AUTO_CREATE_STATISTICS ON
