What is a non-clustered index scan

Question

I know what table scan, clustered index scan and index seek is but my google skills let me down to find a precise explanation into non clustered index scans. Why and when a query uses a non clustered index scan?

Thank you.

Answer 1

As the name suggests, Non Clustered Index Scans are scans on Non Clustered Indexes - NCI scans will typically be done if all of the fields in a select can be fulfilled from a non clustered index, but where the selectivity or indexing of the query is too poor to result in an Seek.

NCI scans potentially have performance benefit over a clustered index scan in that the NCI indexes are generally narrower than the Clustered Indexes (since they generally have fewer columns), hence fewer pages to fetch, and less I/O.

I've put a contrived scenario up on SqlFiddle Here - click on the 'view execution plan' at the bottom.

Given the following setup of table, clustered, and non clustered indexes:

CREATE TABLE Foo
(
    FooId INT,
    Name VARCHAR(50),
    BigCharField CHAR(7000),

   CONSTRAINT PK_FOO PRIMARY KEY CLUSTERED(FooId)
);

CREATE NONCLUSTERED INDEX IX_FOO ON Foo(Name);

The following queries demonstrate the different scans:

-- Clustered Index Scan - because we need all fields, CI is most efficient
SELECT * FROM FOO;

-- Non Clustered Index Scan - because we just need Name, but have no selectivity, the NCI 
-- will suffice and is narrower.
SELECT DISTINCT(Name) FROM FOO;