question

dave.jones.btl avatar image
dave.jones.btl asked

Does it matter if Primary Key isn't the first column?

Hi, We have a table like this CREATE TABLE [dbo].[Table] ( [ID] [int] NOT NULL ,[SessionID] [int] NOT NULL, ,[itemId] [nvarchar](20) NOT NULL, ,[Name] [nvarchar](200) NOT NULL, ,[Document] [image] NOT NULL, ,[uploadDate] [datetime] NOT NULL, ,[DocumentId] [int] NULL ) And as part of a migration they are dropping the [ID] column, which is the Primary Key and re-adding it to make it an IDENTITY(1,1) . When it gets re-added it will be at the end, like so: CREATE TABLE [dbo].[Table] ( [SessionID] [int] NOT NULL, ,[itemId] [nvarchar](20) NOT NULL, ,[Name] [nvarchar](200) NOT NULL, ,[Document] [image] NOT NULL, ,[uploadDate] [datetime] NOT NULL, ,[DocumentId] [int] NULL ,[ID] [int] IDENTITY(1,1) NOT NULL ) Aside from it not looking great, and being bad practice, are there any performance issues surrounding this?
primary-key
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

seanlange avatar image
seanlange answered
There are absolutely zero performance issues with this. I would argue that it isn't a bad practice either. The order of columns means absolutely nothing.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered
Sounds dangerous to me (if there are foreign keys depending on this column, if not, this should not be a problem). Instead of dropping it you could just ALTER table and make it an identity column.This preserves the current values and for new rows will just add one to the current max(ID). If there are currently any gaps, due to deletions/rollbacks etc. these gaps will be "fixed" and every row after the fix will have a different ID than the original table.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.