Re: Indexing strategy
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 13 Jun 2006 21:53:01 +0000 (UTC)
Ryan (ryanofford@xxxxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Indexing strategy
- From: Ryan
- Re: Indexing strategy
- From: Stu
- Re: Indexing strategy
- From: Ryan
- Indexing strategy
- Prev by Date: Re: 2005: Database cannot be opened
- Next by Date: Re: enterprise manager (or other software) to document tables and columns for Server2k?
- Previous by thread: Re: Indexing strategy
- Next by thread: Re: Indexing strategy
- Index(es):
Relevant Pages
|
Loading
