SQL server – which Index is better – Composite index or Index with included columns – Part 1

Indexes are really important subject when it comes to query tuning. There has always been a question around indexes over covering indexes options:

1. Composite Indexes

2. Indexes with included column

Based on the recommendations and readings on the web , the best practice is:

1. Composite indexes should be created to cover the columns in where clause

2. Indexes with included columns should be created to cover the columns in the select
I will try to explain whether creating indexes with included columns will help in both the scenarios. As by doing so, I can still get index seek on the cost of lesser space utilization

Note -I have attached the script for the repro.
Step 1:

Create table tbl_test (col1 int , col2 int, col3 int,col4 int)

Step 2: Insert some data in the table:

DECLARE @count1 BIGINT;

DECLARE @count2 BIGINT;

DECLARE @count3 BIGINT;

DECLARE @count4 BIGINT;

SET @count1 = 1;

SET @count2 = 2;

SET @count3 = 3;

SET @count4 = 4;

WHILE @count1 < 100000000

BEGIN

INSERT INTO tbl_test (col1, col2,col3,col4) VALUES (@count1, @count2,@count3,@count4);

SET @count1 = @count1+1;

SET @count2 = @count2+1;

SET @count3 = @count3 + 1;

SET @count4 = @count4 +1;

END;

Step 3:

select * from tbl_test

Execution plan snippet –
|–Table Scan(OBJECT:([tempdb].[dbo].[tbl_test]))
Created a Clustered index to change it to index scan:

create clustered index idx_col1 on tbl_test(col1)

Execution Plan Snippet:

|–Clustered Index Scan(OBJECT:([tempdb].[dbo].[tbl_test].[idx_col1]))
Step 4:

select col1 from tbl_test where col1=1

Execution plan – Clustered Index Seek
|–Clustered Index Seek(OBJECT:([tempdb].[dbo].[tbl_test].[idx_col1]), SEEK:([tempdb].[dbo].[tbl_test].[col1]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

Step 5:

select col1,col2 from tbl_test where col2=2

Execution plan will show scan on the clustered Index :

|–Clustered Index Scan(OBJECT:([tempdb].[dbo].[tbl_test].[idx_col1]), WHERE:([tempdb].[dbo].[tbl_test].[col2]=CONVERT_IMPLICIT(int,[@1],0)))

The reason being there is no index on col2 therefore it goes for a scan.  Once NCI is created on Col2 , query will go for a seek

create index idx_col2 on tbl_test(col2)

 Step 6: Here starts my question:

select col1,col2 from tbl_test where col2=2

and col3=3 –-additional column

Currently, Col2 has non clustered index which covers col1 and col2 but doesn’t cover col3. To make the query go for index seek we need to make this index as a covering index by adding col3 to the index.

Here is the question , whether to go for composite index or index with included column to create a covering Index.

The obvious answer will be to create composite index to get the Index seek as we have the column to be added, in the where clause.

Case 1 :

But even If I create index with included column, I still get the seek as follows:

create index idx_col2 on tbl_test(col2) include (col3)

|–Index Seek(OBJECT:([tempdb].[dbo].[tbl_test].[idx_col2]), SEEK:([tempdb].[dbo].[tbl_test].[col2]=CONVERT_IMPLICIT(int,[@1],0)), WHERE:([tempdb].[dbo].[tbl_test].[col3]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)

If I see the IO stats of the query:

Table ‘tbl_test’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Case 2:

create index idx_col2 on tbl_test(col2) include (col3,col4) with (drop_existing=on)

|–Index Seek(OBJECT:([tempdb].[dbo].[tbl_test].[idx_col2]), SEEK:([tempdb].[dbo].[tbl_test].[col2]=CONVERT_IMPLICIT(int,[@1],0) AND [tempdb].[dbo].[tbl_test].[col3]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)

Table ‘tbl_test’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If I see the IO stats of the query:

Table ‘tbl_test’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

In both the case I am getting a seek and with no difference in IO. There is a slight difference in the seek columns in the execution plan (if you notice closely):

1. For the execution plan in the first case, filter criteria is on the single column and remaining is in the where clause

2. In the second case, filter criteria is on both the columns of the index
Question –

1. Based on the logic – the index seek will happen with the leading column of the index and in both the cases it’s col2. If the column exists in both select and where clause then which covering index is better

2. How composite index outperform index with included columns

3. Amidst the overhead of index fragmentation and size of composite index over index with included can help me decide which one is better.

I will answer my questions in the next blog post!

Seek_Scan_lookup – demo

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s