Thursday, April 30, 2015

index help

hello,
In my table I have columns like

Year int  not null
week int  not null
type int  not null
code nchar(5) not null
exchange  nvarchar(3)  null

c1    nvarchar(10) null
c2    nvarchar(10) null
c3    nvarchar(10) null
.
.
.
.

c20   nvarchar(10) null

In table I will have Uniqueness of Year,Week,Type,Code,Exchange,c1,c2..,c20
 
In my most of the query I have condition like

where
             Year=@year and period =@period and type=@type and code=@code and exchange=@exchange and c1=@c1 and c2=@c2 and c3=@c3.... and c4=@c20

In this scenario should I make single index with include columns like below

CREATE NONCLUSTERED INDEX IX_table
ON table ( Year,period,type,code,exchange)
INCLUDE (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20)

Or I will create 20 indexes like below

CREATE NONCLUSTERED INDEX IX_table
ON table ( Year,period,type,code,exchange,c1)

CREATE NONCLUSTERED INDEX IX_table
ON table ( Year,period,type,code,exchange,c2)


and so on

No comments:

Post a Comment