Difference between [Multiple Non Clustered Index with Same Key Column and Different Include Column] and One Non Clustered Index with Common Key column and all columns in Include list]

Question

question

sanjeevjbind asked Nov 12, '21

I am wondering what is good practice from below two options. Please help.

1) [Multiple Non Clustered Index with Same Key Column and Different Include Column]

CREATE INDEX IX_index1 ON EMPLOYEE(keyColumn) INCLUDE (col1,col2)

CREATE INDEX IX_index2 ON EMPLOYEE(keyColumn) INCLUDE (col3,col4)

CREATE INDEX IX_index3 ON EMPLOYEE(keyColumn) INCLUDE (col5,col6)

CREATE INDEX IX_index4 ON EMPLOYEE(keyColumn) INCLUDE (col7,col8)

2) One Non Clustered Index with the Common Key column and all required columns in Include list]

CREATE INDEX IX_index ON EMPLOYEE(keyColumn) INCLUDE (col1,col2,col3,col4,col5,col6,col7,col8)

which one one from above two approach is better and faster.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-11-13T07:02:57Z

anthony.green answered Nov 13, '21

Option 2.

1 index is better than 4.

Each insert/update/delete only then has to update the clustered index and the 1 non clustered.

If you go option 1 you will have to do 5 writes so option 2 you save 150% query time in I/O.

You also save storage as your not having to store the keyColumn 3 extra times.

So option two results in faster writes and storage savings so it’s win win.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.