Indexes onTable

I have a table (Denver) with Sales data that is sampled hourly.
This table numerous samples (50 million samples).
Please help me determine the appropriate indexes that should be applied to
the table with the select statement listed below for quick retrieval. I
create a monthly report for each month and each category.
Thanks,
Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
From Denver
Where DC_Year = 2005 AND DC_Month = 5
order by DC_Day, DC_Hour
Denver
DC_Date
DC_Value
DC_Name
DC_Category
DC_Hour
DC_Day
DC_Month
DC_Year
What is the expected selectivity (number of rows to be typically returned)?
If high selectivity, a clustered index in (DC_Year, DC_Month) should be very efficient.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
>I have a table (Denver) with Sales data that is sampled hourly.
> This table numerous samples (50 million samples).
> Please help me determine the appropriate indexes that should be applied to
> the table with the select statement listed below for quick retrieval. I
> create a monthly report for each month and each category.
> Thanks,
>
> Select DC_Name, DC_Value, DC_Hour, DC_Day, DC_Month
> From Denver
> Where DC_Year = 2005 AND DC_Month = 5
> order by DC_Day, DC_Hour
>
> Denver
> DC_Date
> DC_Value
> DC_Name
> DC_Category
> DC_Hour
> DC_Day
> DC_Month
> DC_Year
|||(DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
clause is covered by the first 2 elements of the index, the second 2 provide
the sorting.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> What is the expected selectivity (number of rows to be typically
returned)?
> If high selectivity, a clustered index in (DC_Year, DC_Month) should be
very efficient.[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:40539FB9-52F5-401F-A91A-269CE111AC20@.microsoft.com...
to
>
|||I doubt adding day and hour at the end of the index will affect the query plan. You don't sort by
year, month anyhow. Say that SQL Server uses the index to find following rows, in order:
2005-02-12 14:00
2005-03-14 13:00
2005-04-05 15:00
2005-05-16 11:00
And the result is to be sorted by day, hour, i.e.:
2005-05-16 11:00
2005-03-14 13:00
2005-02-12 14:00
2005-04-05 15:00
As you can see, a sort operation was necessary after the rows were found because the sort operation
didn't include the high order elements in the index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R" <anon@.spamme.please> wrote in message news:OO1K1dApFHA.2916@.TK2MSFTNGP14.phx.gbl...
> (DC_Year, DC_Month, DC_Day, DC_Hour) might provide even better. The where
> clause is covered by the first 2 elements of the index, the second 2 provide
> the sorting.
> R
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23L7jOC2oFHA.2152@.TK2MSFTNGP14.phx.gbl...
> returned)?
> very efficient.
> to
>