Alex Rivera | Logout

Clustered vs Non-Clustered

Asked 2011-09-30T03:37:36.973
100

My lower level knowledge of SQL (Server 2008) is limited, and is now being challanged by our DBAs. Let me explain (I have mentioned obvious statements in the hope that I am right, but if you see something wrong, please tell me) the scenario:

We have a table which holds 'Court Orders' for people. When I created the table, (Name: CourtOrder), I created it like:

CREATE TABLE dbo.CourtOrder
(
  CourtOrderID INT NOT NULL IDENTITY(1,1), (Primary Key)
  PersonId INT NOT NULL,
  + around 20 other fields of different types.
)

I then applied a non-clustered index to the primary key (for efficiency). My reasons is that it is a unique field (primary key), and should be indexed, mainly for selection purposes, as we often Select from table where primary key = ...

I then applied a CLUSTERED index on PersonId. The reason was to group orders for a particular person physically, as the vast majority of work is getting orders for a person. So, select from mytable where personId = ...

I have been pulled up on this now. I have been told that we should put the clustered index on the primary key, and the normal index on the personId. That seems very strange to me. First off, why would you put a clustered index on a unique column? what is it clustering? Surely that's a waste of the clustered index? I'd have believed a normal index would be used on a unique column. Also, clustering the index would mean we can't cluster a different column (One per table, right?).

The reasoning for me being told I have made a mistake is that they believe putting a clustered index on the PersonId would make inserts slow. For the 5% gain in speed of a select, we would be getting a 95% degradation in speed on inserts and updates. Is that correct and valid?

They say that because we cluster the personId, SQL Server has to rearrange data when ever we insert or make a change to the PersonId.

So then I have a

sql performance sql-server-2008 indexing
Edit
Report

1 Answer

5

Some authors do suggest not "wasting" the CI on an identity column if there is an alternative that would benefit range queries.

From MSDN Clustered Index Design Guidelines the key should be chosen according to the following criteria

  1. Can be used for frequently used queries.
  2. Provide a high degree of uniqueness.
  3. Can be used in range queries.

Your CourtOrderID column meets 2. Your PersonId meets 1 and 3. As most rows will end up with the uniqueifier added anyway you might as well just declare it as unique and use PersonId,CourtOrderID as this will be the same width but be more useful as the clustered index key is added to all NCIs as the row locator and this will allow them to cover more queries.

The main issue with using PersonId,CourtOrderID as the CI is that logical fragmentation will likely ensue (and this particularly affects the range queries you are trying to help) so you would need to monitor fill factor, and fragmentation levels and perform index maintenance more often.

answered 2011-09-30T08:21:56.687

Your Answer