Primary key column question

• sqlsurfer

  SSC Veteran

  Points: 267

  More actions

  November 23, 2023 at 7:57 pm

  #4333257

  Hi,

  I'm new to sql server and successfully created a table of columns, however after creating the table I realised I forgot to add the primary key column.

  I used the ALTER command for and used a query to create a new  column for the primary key. This was successful but it added the column at the right side of the table.

  Can I move the primary key column to the beginning of the table if at all possible? or delete the existing one and create a new one as the first column in the table? If so how? I don't want to drop the table and start again if I don't have to.

  Thanks for any help

  • This topic was modified 12 months ago by  sqlsurfer.
  • This topic was modified 12 months ago by  sqlsurfer.
  • This topic was modified 12 months ago by  sqlsurfer.

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  November 24, 2023 at 4:47 am

  #4333419

  You could use the table designer in SSMS.  It will allow you to "move" it.  Be advised that, behind the scenes, it will create a new table, copy the data to it (along with all the index and key stuff) and then drop the original and rename the new table to the same as the original.

  My question would be, does it actually matter where the PK is?

  On that note, what is the table for?  I ask because it sounds to me like you might be making an IDENTITY column for the PK and, depending on what's in the table, that might be a really bad idea... especially when it comes to what the Clustered Index will be.

  --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)
  

• Mr. Brian Gale

  SSC-Insane

  Points: 24800

  More actions

  November 24, 2023 at 2:20 pm

  #4333699

  Another way to "move" the columns is to change the SELECT order. What I mean is rather than:

  SELECT NAME, ID
  FROM TABLE

  do:

  SELECT ID, NAME
  FROM TABLE

  The exception is if you are using "SELECT *", but best practice is to never use SELECT *. The coding standards at my company don't allow SELECT * in any production level code due to the surprises that can happen.

  The above is all just my opinion on what you should do. 
  As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
  I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.