Nonclustered index is faster than clustered index?

Question

Both tables have same structure and 19972 rows in each table. for practicing indexing, i created both tables having same structure and created

clustered index on persontb(BusinessEntityID)

and

nonclustered index on Persontb_NC(BusinessEntityId)

and table structure

BusinessEntityID int
FirstName varchar(100)
LastName  varchar(100)                                                                                                                       

 -- Nonclusted key on businessentityid takes 38%
SELECT  BusinessEntityId from Persontb_NC
WHERE businessentityid BETWEEN 400 AND 4000

-- CLustered key businessentityid takes 62%
SELECT BusinessEntityId  from persontb 
WHERE businessentityid BETWEEN 400 AND 4000

Why clustered index takes 62% and non clustered 38%?

Answer 1

Yes the clustered index has fewer rows per page than the non clustered index as the leaf pages of the clustered index must store the values for the other two columns (FirstName and LastName).

The leaf pages of the NCI store only the BusinessEntityId values and a row locator (RID if the table is a heap or the CI key otherwise).

So the estimated costs reflect the greater number of reads and IO requirement.

If you were to declare the NCI as

nonclustered index on Persontb_NC(BusinessEntityId) INCLUDE (FirstName, LastName)

then it would be similar to the clustered index.

Answer 2

Clustered index contains not only data from column index is on, but also data from all other columns. (There can only be one clustered index per table)

Nonclustered index contains only data from indexed column(s), and a row_id pointer to where the rest of data is.

Therefore this particular nonclustered index is lighter and less reading is required to scan/seek through it and this particular query will work faster.

However, have you tried to retrieve FirstName and LastName as well, it would be different and clustered index should perform better.

Answer 3

The percentages between the query plans are meaningless to compare outright. You must benchmark the queries to have a valid comparison. Additionally, small row counts have a tendency to hide performance differences between indexing strategies. By increasing the row count to 10 million you can gain a clearer picture of the performance differences.

There is a sample script that creates 3 tables, your two from above, and a third with both a clustered and non-clustered index.

USE [tempdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[t1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [c1] [varchar](200) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[t2](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [c1] [varchar](200) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[t3](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [c1] [varchar](200) NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX CIX_t1 ON t1(id)

CREATE NONCLUSTERED INDEX IX_t2 ON t2(id)

CREATE CLUSTERED INDEX CIX_t3 ON t3(id)
CREATE NONCLUSTERED INDEX IX_t3 ON t3(id)

Populate the tables with 10 million rows

DECLARE @i INT
DECLARE @j int
DECLARE @t DATETIME
SET NOCOUNT ON
SET @t = CURRENT_TIMESTAMP
SET @i = 0
WHILE @i < 10000000
BEGIN
--populate with strings with a length between 100 and 200 
INSERT INTO t1 (c1) VALUES (REPLICATE('x', 101+ CAST(RAND(@i) * 100 AS INT)))
SET @i = @i + 1
END

PRINT 'Time to populate t1: '+ CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR(10)) + ' ms'
SET @t = CURRENT_TIMESTAMP


SET @i = 0
WHILE @i < 10000000
BEGIN
--populate with strings with a length between 100 and 200 
INSERT INTO t2 (c1) VALUES (REPLICATE('x', 101+ CAST(RAND(@i) * 100 AS INT)))
SET @i = @i + 1
END

PRINT 'Time to populate t3: '+ CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR(10)) + ' ms'
SET @t = CURRENT_TIMESTAMP

SET @i = 0
WHILE @i < 10000000
BEGIN
--populate with strings with a length between 100 and 200 
INSERT INTO t3 (c1) VALUES (REPLICATE('x', 101+ CAST(RAND(@i) * 100 AS INT)))
SET @i = @i + 1
END

PRINT 'Time to populate t3: '+ CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR(10)) + ' ms'

We can use sys.dm_db_index_physical_stats to see the size on disk of the indexes.

SELECT  OBJECT_NAME(OBJECT_ID) table_name, index_id, index_type_desc, 
record_count, page_count, page_count / 128.0 size_in_mb, avg_record_size_in_bytes
FROM    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('t1'), NULL, NULL, 'detailed')
WHERE   index_level = 0 
UNION ALL
SELECT  OBJECT_NAME(OBJECT_ID) table_name, index_id, index_type_desc, 
record_count, page_count, page_count / 128.0 size_in_mb, avg_record_size_in_bytes
FROM    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('t2'), NULL, NULL, 'detailed')
WHERE   index_level = 0 
UNION ALL
SELECT  OBJECT_NAME(OBJECT_ID) table_name, index_id, index_type_desc, 
record_count, page_count, page_count / 128.0 size_in_mb, avg_record_size_in_bytes
FROM    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('t3'), NULL, NULL, 'detailed')
WHERE   index_level = 0 

And the results:

table_name  index_id    page_count  size_in_mb  avg_record_size_in_bytes    index_type_desc
t1  1   211698  1653.890625 167.543 CLUSTERED INDEX
t2  0   209163  1634.085937 165.543 HEAP
t2  2   22272   174.000000  16  NONCLUSTERED INDEX
t3  1   211698  1653.890625 167.543 CLUSTERED INDEX
t3  2   12361   96.570312   8   NONCLUSTERED INDEX

T1's clustered index is around 1.6 GB in size. T2's non-clustered index is 170 MB (90% savings in IO). T3's non-clustered index is 97 MB, or about 95% less IO than T1.

So, based off of the IO required, the original query plan should have been more along the lines of 10%/90%, not 38%/62%. Also, since the non-clustered index is likely to fit entirely in memory, the difference may be greater still, as disk IO is very expensive.