How to alter a table with PRIMARY KEY CLUSTERED

  • Hi All,

    So I created a series of tables, and all of them looked something like this
    CREATE TABLE [dbo].[Tbl_MyTable](
        [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    ) ON [PRIMARY]
    GO

    No Problems.  All works well.  except One of my tables was missing PRIMARY KEY CLUSTERED,
    So now most tables look like this
    CREATE TABLE [dbo].[Tbl_MyTable](
        [ID] [int] IDENTITY(1,1) NOT NULL
    PRIMARY KEY CLUSTERED
    (
        [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]
    GO

    Except the table where I missed the PRIMARY KEY CLUSTERED down't look like that.
    So I need to know how to fix it.  it has data in it and I can't kill that data and rebuild the table.  How can I alter it so that it's fixed?
    Cheers

  • barry.nielson - Monday, November 19, 2018 3:43 PM

    Hi All,

    So I created a series of tables, and all of them looked something like this
    CREATE TABLE [dbo].[Tbl_MyTable](
        [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    ) ON [PRIMARY]
    GO

    No Problems.  All works well.  except One of my tables was missing PRIMARY KEY CLUSTERED,
    So now most tables look like this
    CREATE TABLE [dbo].[Tbl_MyTable](
        [ID] [int] IDENTITY(1,1) NOT NULL
    PRIMARY KEY CLUSTERED
    (
        [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]
    GO

    Except the table where I missed the PRIMARY KEY CLUSTERED down't look like that.
    So I need to know how to fix it.  it has data in it and I can't kill that data and rebuild the table.  How can I alter it so that it's fixed?
    Cheers

    does the table have a clustered index? and does it have a PK or that wasn't created either?
    If it already has a clustered index they maybe that is the correct index to be clustered so don't go and change it just because you think the ID should be clustered - probably should not, and even on your other tables it may be possible that you should change the clustered index to be something else.

    If there isn't any clustered index then most likely you should create one - so see on which column(s) it should be clustered and create the index on that/those columns.

    If it has a PK and no other columns can be used for a (better) clustered index, then drop the pk and recreate it again as clustered.

  • If there is no Clustered Index on the table, then this will work...


      ALTER TABLE dbo.SomeTable
        ADD CONSTRAINT PK_SomeTable PRIMARY KEY CLUSTERED (ID)

    You can also change the Fill Factor as you build the constraint above but I'll let you Google for "ALTER TABLE SQL SERVER" so that you can find this type of thing in the future.

    Let us know if your table already has a Clustered  Index or  PK constraint on it.

    Before Joe Celko or a couple of other well meaning folks show up, I'll seriously recommend that you...
    1.  Stop using Hungarian Notation for your objects (the "tbl_") thing.  It's just extra typing and you'll be seriously embarrassed someday when you have to overlay a view on the table and do so without breaking any code which would require your view to be "tbl_whateveryourtablenameis".
    2.  Adopt an naming standard of "sometablenameID" for your IDENTITY columns.
    3.  Stop letting SQL Server name your constraints.

    You might also want to take a look at how you're going to use the table.  It may be better to use the clustered index on something else... or not.  "It Depends" on a lot of things other than just what you can do with a SELECT statement.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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