Indexing strategy in sql server 2000 query with group by clause

Question

I have a query as shown below,It has clustered index on one of the columns ,[Generated_red_ident].On which columns of below mentioned query has to be non clustered index?

I can understand that the Episode_Number is a good candidate for non clustered indexing.. How about the column methd and start_date1 ?

Also,do i have to create non clustered index on all those columns in group by clause as well?

Please sugest

SELECT [ID],
       [Number],
       [Age],
       [Start_Date],
       [Pr],
       [Mthd],
       [Identifier],
       CASE
         WHEN [methd] IN ( 21, 22, 23, 24,28) THEN 'er'
       END AS 'ner',
       CASE
         WHEN Datepart(dw, [Start_Date1]) in (1,7) THEN 'Weekend'
         END AS 'weekday'
FROM   [PPL_Data]
WHERE  [Episode_Number] = 1
GROUP  BY [ID],
       [Number],
       [Age],
       [Start_Date],
       [Pr],
       [Mthd],
       [Identifier]

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

Yes,Ideally you should create index on all the coloumn in the query. You can use covered index. see Covered Index

edited May 23 '17 at 10:25

Community

1
1

answered Feb 10 '14 at 14:23

hrishi

1,531
8
28
43

score 0 · Answer 2 · answered Feb 10 '14 at 14:59

Two things, performance of NC-Index is largely depends on the cardinality of the first column in that Index. So you should think what is the cardinality of column [Episode_Number]. Column with High cardinality should be used. Also instead using the DATEPART function during SELECT make that value persisted on table as a new column if the query is falling into 75% usage scenarios. Last thing is you can try and see how much performance gain if you include all first 7 columns as Cover Index Vs, only few of them.

Indexing strategy in sql server 2000 query with group by clause

2 Answers2