Query by date optimization

[Joseph Caldwell]

Hi
*Edit* I ran some tests and wanted to edit my question. Basically I just would like to know:

If I have an date field as part of a multicolumn primary key, would it be best to create a nonclustered index on just the invoice date field?

There are 760 million records and the result set is one week worth of data - 150,000 records.

Looking for the best practice. The data is historical and is never modified.

[Joseph Caldwell]

I am learning data warehousing and analysis services as I am starting to think this may be the best approach.

[ck9663]

Rule of thumb:

If there's only one index on the table, make it clustered.

The primary key is the column mostly used for filtering your data and should be unique. (Aside from the book definition of PK). PK is primarily used to preserve the uniqueness of the record. Indexes are more of optimization than anything else.

Try to include the most commonly used column in WHERE clause on your index.

If all else fails, start from here...


Good Luck!!!

~~ CK