You are correctly describing a feature of the index, but it does not help the optimiser. It will still scan all the pages. In theory it could read fewer pages if the cardinality of the leading column were low enough by instead performing a series of seeks, however it does not do this in practice.
Code:
USE test;
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.low_cardinatilty'))
BEGIN
DROP TABLE dbo.low_cardinatilty;
END;
CREATE TABLE dbo.low_cardinatilty
(
leading_column BIT NOT NULL
, second_column INT NOT NULL
, datas CHAR(100) COLLATE Latin1_General_CI_AS NOT NULL
, CONSTRAINT pk_low_cardinatilty PRIMARY KEY CLUSTERED (leading_column, second_column) WITH (FILLFACTOR = 100)
) ON [PRIMARY];
INSERT INTO dbo.low_cardinatilty
(
leading_column
, second_column
, datas
)
SELECT leading_column = number % 2
, second_column = FLOOR(number/2.0)
, datas = REPLICATE('YAYA', 25)
FROM dbo.numbers
SET STATISTICS TIME, IO, PROFILE ON
SELECT *
FROM dbo.low_cardinatilty
WHERE second_column = 0
SET STATISTICS TIME, IO, PROFILE OFF
In the above, the optimiser scans rather than seeks. The scan reads 929 pages. The index depth is 3 (including leaf) so two seeks would only require 3 reads each. We can prove this empirically:
Code:
SET STATISTICS TIME, IO, PROFILE ON
SELECT *
FROM dbo.low_cardinatilty
WHERE second_column = 0
AND leading_column = 0
UNION ALL
SELECT *
FROM dbo.low_cardinatilty
WHERE second_column = 0
AND leading_column = 1
SET STATISTICS TIME, IO, PROFILE OFF
The point is, this is not an optimisation the team bothered putting in to the cost based optimiser, presumably because contrived examples like this are going to be very rare.
A clustered index clusters the table data together. The leaf level of the index is the table. As such, it is a B-Tree index and also the table. Arguably since SQL Server 2005 and the introduction of includes columns, non-clustered indexes have become a little like clustered-indexes-lite but we are getting in to some of the complications I referred to earlier.
dBforums
>
Business Card Analogy for Understanding Indexes
11-24-10, 16:38
Linear Mode
