INF: Indexes Use Distribution Page Statistics

• Microsoft SQL Server for OS/2, version 4.2

SUMMARY

The following steps illustrate that distribution statistics are used when only a prefix of the index is used in the query.

MORE INFORMATION

1. Create a table and index as follows:
   

   
         create table t1 (c1 varchar(20), c2 varchar(20), c3 char(30) )
         create index i1 on t1 (c1,c2)
    

   The third column ensures that the table occupies enough pages to keep the optimizer from always choosing a table scan.



2. Populate the table with 200 rows of generated data with the following criteria:
   
   
   1. Generate all 200 values of c2 using a counter.
   
   
   
   2. Set c1=1 in the first 100 rows.
   
   
   
   3. Increment the value of c1 using a counter for the next 100 rows.
   
   
   
   With the table populated in this way, c1 will not be very selective below row 100; however, it will be very selective above 100.



3. Next, run update statistics and the following queries with SET SHOWPLAN ON:
   

   
         select * from t1 where c1=1
         select * from t1 where c1=150
    

   Note that the first query scans the table instead of using the index. This proves that the optimizer is aware that the index is very unselective when c1=1. In contrast, the second query uses the index. This indicates that the optimizer is also aware that the index is very selective when c1=150.

Additional query words: Transact-SQL dblib

Keywords          : kbprg SSrvDB_Lib SSrvTrans SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        : 

Last Reviewed: March 11, 1999