Results 1 to 2 of 2

Thread: Easy Question

  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Question Index and Identities

    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
    (
    [company_id]
    ) 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 09:35.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •