Statistics, Data Distribution, and Cardinality - SQL Server Query Performance Tuning - page 207

IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1 ;
GO
CREATE TABLE dbo.Test1 (C1 INT, C2 INT IDENTITY) ;
INSERT INTO dbo.Test1
(C1)
VALUES (1) ;
SELECT TOP 10000
IDENTITY( INT,1,1 ) AS n
INTO #Nums
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2 ;
INSERT INTO dbo.Test1
(C1)
SELECT 2
FROM #Nums ;
DROP TABLE #Nums;
CREATE NONCLUSTERED INDEX FirstIndex ON dbo.Test1 (C1) ;
When the preceding nonclustered index is created, SQL Server automatically creates statistics on the index key.
You can obtain statistics for this nonclustered index ( FirstIndex ) by executing the DBCC SHOW_STATISTICS command.
DBCC SHOW_STATISTICS(Test1, FirstIndex);
Figure 12-17 shows the statistics output.
Figure 12-17. Statistics on index iFirstIndex
Now, to understand how effectively the optimizer decides upon different data retrieval strategies based on
statistics, execute the following two queries requesting different numbers of rows:
--Retrieve 1 row;
SELECT *
FROM dbo.Test1
WHERE C1 = 1;