Clustered Index/table

  • Hi,

    I am totaly new to SQL*SERVER. I found that all our primary keys indexes have been created with the "clustered" option (which is the default). I really think we shouldn't have done this. Could someone explain the pros and cons of this approach or point to any resources that'd provide guildelines on when to use clustered indexes? Thank you!



  • This was removed by the editor as SPAM

  • On the whole every table should have a clustered index. You will want to use somewhat basic index design in deciding which fields to use for the clustered index. Here is a good article:

    If you do a Google search for Clustere Index you will get lots of good articles. Do a search on this site for Clustered Index and you will get some very good references.



  • Hope it's ok that I add my question to your thread.

    I created a clustered index on column Zipcode, intending that the columns would always be sorted. Then I created a primary key on the other column.

    Why, when I do a select, is the Zipcode column not sorted? Other tables set up the same, do sort on the clustered index.

    Thanks - Marc


    CREATE TABLE [dbo].[globZipcodes2] (

     [ZipcodeID] [int] IDENTITY (1, 1) NOT NULL ,

     [Zipcode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    ) ON [PRIMARY]


    CREATE  UNIQUE  CLUSTERED  INDEX [NDX_Zipcode_CLUSTERED_UNIQUE] ON [dbo].[globZipcodes2]([Zipcode]) ON [PRIMARY]


    ALTER TABLE [dbo].[globZipcodes2] ADD


     ([ZipcodeID])  ON [PRIMARY]


  • Your ZIPCODE Field is a Varchar and therefore will probably be sorted in Alpha order not numerical order.  For example (1,2,3,10,12,20) would sort (1,10,12,2,20,3).



  • Thanks for responding.

    Great point!  However, wouldn't (01,02,03,10,12,20) sort as (01,02,03,10,12,20)? Would this concept mess it up if I had data such as (33256-0456, 33256)!!!?

    Here's what I did:

    1.  I dropped the indexes, removed the PK and indenty, then rebuilt the table as follows:

    Set Zipcode to PK, Set ZipCodeID to Identity and it works fine.

    2.  I wonder, with such a simple table design (and static data), if I even need the ID field. I was not planning on using the ID in a join or FK anyway. Just using zipcode in a combo box.

    3.  I also wonder if I there was an extra index in the original situation as when I compared the old code to the new, I saw the PK was named PK_globZipcodes2 previously!!!



  • I have found that if you Create the table with primary key.

    CREATE TABLE tbl_Example (

    [Field1] [int] Not Null Primary Key,

    [Field1] [Char] Null)

    on [Primary]


    there will be no object in sysobjects called PK_tbl_Example....

    If you use and alter statement and add the constraint it will create an object.  Either way a clustered index will automatically be created to support the primary key.


    Glen Anderson

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply