Best way to create indexes in SQL Server

Question

I have four columns in my table (a, b, c, d) that all of them depend on column (date), so in my queries i have :

 select a where date 
 select b where date 
 select c where date 
 select d where date

I need to know what's the best way to create indexes for all of them, I have two suggestions :

First suggestion:

create i_a on a
create i_b on b
create i_c on c
create i_d on d
create i_date on date

Second suggestion:

create i_a on a include date
create i_b on b include date
create i_c on c include date
create i_d on d include date

Please which one is better to use.

@Mr.香港人, that's just a sample. It depends. I believe testing should be done first before tuning the query. — Felix Pamittan, Apr 15 '15 at 07:40

score 2 · Accepted Answer · answered Apr 15 '15 at 07:47

2

This might help you:

CREATE NONCLUSTERED INDEX Index_Name ON TableName(Date) INCLUDE(A,B,C,D)

The column you want to index should be the one used for filtering (WHERE clause). You may add an INCLUDE to avoid lookups.

answered Apr 15 '15 at 07:47

Felix Pamittan

1 Answers1