Hello,

We want to add a new int identity column as a primary key to an already exisiting table that has a primary key on Guid. Here is the DDL:

CREATE TABLE [dbo].[VRes](
	[VResID] [uniqueidentifier] NOT NULL,
	[Mes] [varchar](max) NOT NULL,
	[PID] [uniqueidentifier] NOT NULL,
	[Segt] [int] NOT NULL,
	[MeType] [nvarchar](50) NOT NULL,
	[PMID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_VResID] PRIMARY KEY CLUSTERED 
(
	[VResID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[VRes]  WITH CHECK ADD  CONSTRAINT [FK_VRes_PPerson] FOREIGN KEY([PID])
REFERENCES [dbo].[PPerson] ([PID])
GO

ALTER TABLE [dbo].[VRes] CHECK CONSTRAINT [FK_VRes_PPerson]
GO

Here is how I am doing it:

ALTER TABLE [dbo].[VRes] DROP CONSTRAINT [PK_VResID]

alter table VRES add PTID  int IDENTITY(1,1)

ALTER TABLE VRES
ADD CONSTRAINT PK_PTID PRIMARY KEY CLUSTERED (PTID)

And it runs forever.

Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?

Hi,

 ALTER TABLE tblYourTableName ADD ColName INT IDENTITY(1,1) 

Note: Adding IDENTITY Column will essentially update all the rows with Seed and Increment value, if the table is large then this operation might take some time to complete and you may see blocking on other sessions

You should have probably create a new table with the identity column (if the table is going to grow quickly, you can use BigInt). Then add rows from the current table using batches technique, say, 50K rows at once.

Then add constraints and rename the original table to _old and this new table to correct name.

It appears to be the only solution.

Took 30 mins to run. I think because of the data. Thanks everyone

• Edited by oleolehoohoo 12 hours 51 minutes ago

Hi,

Do not forget to move constraint(s) referenced to old table to newly created.