Tipping Point (Game) - Clustered Index Scan vs Nonclustered Index Seek with Key Lookup
Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.
If you like the video you can support me on Patreon, / beardeddev
In this video we play a game to try and identify when the query optimizer in SQL Server will switch from using a Clustered Index Scan to using a Nonclustered Index Seek with a Key Lookup. When a filter is highly selective we expect that an index seek will be performed however this is not always the case as when a nonclustered index is non-covering then a key lookup needs to be performed which can expensive therefore the query optimizer may decide it is faster to use an index scan, this is certainly not a bad thing and you do not want to spend all your time trying to turn index scans to index seeks.
Clustered Indexes are the logical storing of the data and Nonclustered Indexes are created to allow for faster retrieval of data, we would typically add columns that we filter on in the WHERE clause to a nonclustered index to improve read time, nonclustered indexes contain a reference to the clustered index in the form of a key lookup.
If you are not familiar with how indexes are stored as B-Trees check out this video: • SQL Tutorial - Clustered Indexes OLTP
To follow along with the examples please see the code below.
Note: Angled brackets aren't allowed so these will need to be changed when pasting in to SQL Server Management Studio.
Please feel free to post any comments.
DROP TABLE IF EXISTS dbo.Table1;
GO
-- TABLE CONTAINS CLUSTERED AND NONCOVERING NONCLUSTERED INDEX
CREATE TABLE dbo.Table1
(
Id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[Value] TINYINT NOT NULL,
[ColumnA] CHAR(1) NOT NULL,
[ColumnB] CHAR(1) NOT NULL,
[ColumnC] CHAR(1) NOT NULL,
[ColumnD] CHAR(1) NOT NULL
INDEX NC_IX_Table1_Value NONCLUSTERED ([Value])
);
-- VALUE = 1, 1 ROW
INSERT INTO dbo.Table1 ([Value], [ColumnA], [ColumnB], [ColumnC], [ColumnD])
VALUES (1, 'A', 'A', 'A', 'A');
-- VALUE = 2, 11 ROWS
GO
DECLARE @i INT = 1;
WHILE @i [insert less than symbol]= 10
BEGIN
INSERT INTO dbo.Table1 ([Value], [ColumnA], [ColumnB], [ColumnC], [ColumnD])
VALUES (2, 'B', 'B', 'B', 'B');
SET @i += 1
END;
-- VALUE = 3, 111 ROWS
GO
DECLARE @i INT = 1;
WHILE @i [insert less than symbol]= 100
BEGIN
INSERT INTO dbo.Table1 ([Value], [ColumnA], [ColumnB], [ColumnC], [ColumnD])
VALUES (3, 'C', 'C', 'C', 'C');
SET @i += 1
END;
-- VALUE = 4, 1111 ROWS
GO
DECLARE @i INT = 1;
WHILE @i [insert less than symbol]= 1000
BEGIN
INSERT INTO dbo.Table1 ([Value], [ColumnA], [ColumnB], [ColumnC], [ColumnD])
VALUES (4, 'D', 'D', 'D', 'D');
SET @i += 1
END;
-- VALUE = 5, 11111 ROWS
GO
DECLARE @i INT = 1;
WHILE @i [insert less than symbol]= 10000
BEGIN
INSERT INTO dbo.Table1 ([Value], [ColumnA], [ColumnB], [ColumnC], [ColumnD])
VALUES (5, 'E', 'E', 'E', 'E');
SET @i += 1
END;
SELECT * FROM dbo.Table1
WHERE [Value] = 1;
