Best way to create non clustered index

Sunil 2017-03-16 14:45:51


I am having one table contains 30+ Million records. The table structure is as follows,

id int
col1 varchar
start_dt datetime
end_dt datetime
indx_val float

Most of the operations on this table are
– Select indx_val based on col1, start_dt
– Select indx_val based on col1, end_dt
– Update indx_val based on col1, start_dt
– Update indx_val based on col1, end_dt

I am trying to create two non clustered indexes
one on col1, start_dt and indx_val as include field and another one as col1, end_dt and indx_val as include field.

Please suggest as this approach is good or any other approach which makes better performance.

Thanks in advance.

Vlady Oselsky 2017-03-23 19:19:34
That sounds like a good approach. The only thing you have to consider when creating indexes is to have most restrictive column before less restrictive. The update is not going to be helped by INCLUDE column list, but the SELECT operation will not have additional costly BOOKMARK lookup.
Sunil 2017-03-24 12:22:15
Thanks Viady for your suggestion. Will try this option.