How to alter a table with PRIMARY KEY CLUSTERED

• barry.nielson

  SSC Eights!

  Points: 912

  More actions

  November 19, 2018 at 3:43 pm

  #378643

  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

• frederico_fonseca

  SSCoach

  Points: 16153

  More actions

  November 19, 2018 at 5:01 pm

  #2014050

  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.

• Jeff Moden

  SSC Guru

  Points: 1004307

  More actions

  November 19, 2018 at 7:39 pm

  #2014058

  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)