Database Reference
In-Depth Information
To understand how a clustered index can outperform a nonclustered index in these circumstances, even in
retrieving a small number of rows, create a test table with a high selectivity for one column.
IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1 (C1 INT,C2 INT);
WITH Nums
AS (SELECT TOP (10000)
ROW_NUMBER() OVER (ORDER BY (SELECT 1
)) AS n
FROM master.sys.all_columns AS ac1
CROSS JOIN master.sys.all_columns AS ac2
)
INSERT INTO dbo.Test1
(C1,C2)
SELECT n,
2
FROM Nums;
The following
SELECT
statement fetches only 1 out of 10,000 rows from the table:
SELECT t.C1,
t. C2
FROM dbo.Test1 AS t
WHERE C1 = 1000;
This will be with the graphical execution plan shown in Figure
8-21
and the output of
SET STATISTICS IO
and
STATISTICS TIME
as follows:
Table 'Test1'. Scan count 1, logical reads 39
CPU time = 0 ms, elapsed time = 1 ms.
Figure 8-21.
Execution plan with no index
Considering the small size of the result set retrieved by the preceding
SELECT
statement, a nonclustered column
on
c1
can be a good choice.
CREATE NONCLUSTERED INDEX incl ON dbo.Test1(C1);
