I've read some post that highly recommend not putting a clustered pk on identity columns. Rather use a non-clustered pk for identity pk columns. Does anyone have opinions about this? I have several reference tables that have the following structure. Since joins are made on the pk column I would guess that it would be beneficial to make the pk the clustered index, then again maybe it's better to make one of my unique indexes clustered. Any thoughts?
CREATE TABLE [company] (
[company_id] [int] IDENTITY (1, 1) NOT NULL ,
[co_abbrev] [char(10)] NOT NULL ,
[co_name] [varchar(50)] NOT NULL ,
CONSTRAINT [pk_company] PRIMARY KEY CLUSTERED
) WITH FILLFACTOR = 90
CREATE UNIQUE INDEX [ux_company_01] ON [dbo].[company]([co_name]) WITH FILLFACTOR = 90
CREATE UNIQUE INDEX [ux_company_02] ON [dbo].[company]([co_abbrev]) WITH FILLFACTOR = 90
Last edited by peterlemonjello; 04-01-04 at 08:35.
If table includes field datetime and you are going to select data from this table by date - create clustered index on it.
BOL:Consider using a clustered index for:
-Columns that contain a large number of distinct values.
-Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
-Columns that are accessed sequentially.
-Queries that return large result sets, etc.