The Clustered Index is not the Primary Key

  • richlion2 (5/26/2016)


    Sorry for making this to drag on like this.

    Attached is a real example from one of my databases (not my design BTW). It shows a table with 2 first columns. The PK's are KEY_ID and FIXEL_ID, the latter being a VARCHAR.

    I do not see a CLUSTERED index here, a non-clustered one is, this is the extract into SQL:

    CREATE TABLE [dbo].[FIXEL](

    [Key_id] [int] NOT NULL,

    [fixel_id] [varchar](15) NOT NULL,

    [name] [varchar](32) NULL,

    /* ---- etc, etc, */

    CONSTRAINT [PK_FIXEL] PRIMARY KEY NONCLUSTERED

    (

    [Key_id] ASC,

    [fixel_id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

    IGNORE_DUP_KEY = OFF,

    ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    Whether a non-clustered index was explicitely requested I don't know. From the screenshot you may see a sample of the data. My question would be, why in this case a non-clustered index was chosen? Is it because a CLUSTERD (default) would not be created due to the types of the PK columns?

    I can provide some more rows to show what's the table made up of.

    Regards,

    Ryszard

    This does not have an identity so 'probably' the inserts are not sequential. But cant say anything unless you try and analyze and find out how the table is used.

    Most data modelling tools would create a clustered index for PK and non clustered index for FKs. I have found no reason to change it 90 % of the time for OLTP database especially if you prefer small identity surrogate columns as PK, a review should be there in the checklist though. Definitely there are specific cases where you would want to alter the default behavior but mostly it will be specific designs only applicable for that particular scenario. So until and unless we know it I too tend to use default clustered index on PK.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Terje Hermanseter (5/26/2016)


    However, I like Kimberly Tripp's advice. Choose the clustering key separately from the PK, keep it unique, fixed, and narrow..

    I'm not sure I fully understand the "choose the clustering key separately from the PK" part. I thought it was perfectly ok to use the PK as the clustered key as long as it fulfills the other criterias: keep it unique, narrow, static, ever-increasing and fixed width?

    It is, but you're choosing them separately. You're picking a PK based on some unique criteria. You're looking at a clustered index as being narrow, static, increasing, fixed. If they match up, great, but you choose them separately.

  • Steve Jones - SSC Editor (5/30/2016)


    Terje Hermanseter (5/26/2016)


    However, I like Kimberly Tripp's advice. Choose the clustering key separately from the PK, keep it unique, fixed, and narrow..

    I'm not sure I fully understand the "choose the clustering key separately from the PK" part. I thought it was perfectly ok to use the PK as the clustered key as long as it fulfills the other criterias: keep it unique, narrow, static, ever-increasing and fixed width?

    It is, but you're choosing them separately. You're picking a PK based on some unique criteria. You're looking at a clustered index as being narrow, static, increasing, fixed. If they match up, great, but you choose them separately.

    Thanks! That was clarifying.

  • I am still amazed at how many people here come across long term SQL Server practitioners who don't know what a clustered index is. I cannot remember how long ago, if at all, I was last in an interview that didn't ask what one was. Whichever side of the table I sat. Whether for a DBA or a developer.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Gary Varga - Monday, January 16, 2017 5:22 PM

    I am still amazed at how many people here come across long term SQL Server practitioners who don't know what a clustered index is. I cannot remember how long ago, if at all, I was last in an interview that didn't ask what one was. Whichever side of the table I sat. Whether for a DBA or a developer.

    "Clustered Index" collective noun for a group of indices?
    ;-0

  • Yet Another DBA - Tuesday, January 17, 2017 12:16 AM

    Gary Varga - Monday, January 16, 2017 5:22 PM

    I am still amazed at how many people here come across long term SQL Server practitioners who don't know what a clustered index is. I cannot remember how long ago, if at all, I was last in an interview that didn't ask what one was. Whichever side of the table I sat. Whether for a DBA or a developer.

    "Clustered Index" collective noun for a group of indices?
    ;-0

    It was late. I couldn't sleep. You're mean. (Only joking 😛 Off to edit post.)

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Yet Another DBA - Tuesday, January 17, 2017 12:16 AM

    Gary Varga - Monday, January 16, 2017 5:22 PM

    I am still amazed at how many people here come across long term SQL Server practitioners who don't know what a clustered index is. I cannot remember how long ago, if at all, I was last in an interview that didn't ask what one was. Whichever side of the table I sat. Whether for a DBA or a developer.

    "Clustered Index" collective noun for a group of indices?
    ;-0

    That's just silly. Everybody knows the correct collective noun is a fragment of indices

Viewing 7 posts - 31 through 37 (of 37 total)

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