question

sanjeevjbind avatar image
sanjeevjbind asked

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]

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.

indexingperformance-tuningsql server 2016nonclustered-indexindex-performance
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
anthony.green avatar image
anthony.green answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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