Posted by Erland Sommarskog on 06/13/06 21:53

Ryan (ryanofford@hotmail.com) writes:
> ALTER TABLE [dbo].[DEALER_SOURCE_DATA_VALUES] WITH NOCHECK ADD
> CONSTRAINT [PK_DEALER_SOURCE_DATA_VALUES] PRIMARY KEY CLUSTERED
> (
> [DEALER_SOURCE_DATA_ID],
> [FIELD_CODE]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> This table has approx 25 million rows. I would have added indexes to
> Dealer_Source_Data_Id and Field Code seperately. This table holds the
> data ID which can be related back to a row in another table for the
> year, month and Dealer ID. It also holds all lines and the value
> against those lines. Quite often queries would be needed for everything
> by the Dealer_Source_Data_Id hence the first index I would add. Also,
> we want to sum (for example) all of a specific field_code, hence the
> other index I would add.
>
> We would normally have approx 4,000 unique Field_Codes per
> Dealer_Source_Data_Id.
>
> The PK is correct, but not good for querying what we need to as it
> would only be used when we specify both Dealer_Source_Data_Id and
> Field_Code in order to return a value. Correct ?

Not really. A query only by DEALER_SOURCE_DATA_ID would use the
clustered index. Unless you do queries like:

SELECT COUNT(*), DEALER_SOURCE_DATA_ID
FROM tbl
GROUP BY DEALER_SOURCE_DATA_ID

there is not reason to add an NC index on DEALER_SOURCE_DATA_ID.


But you are correct that the clustred index is not good for queries
on a certain FIELD_CODE.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]