Changing a Data Type from INT to BIGINT

I have a PK column which was created as a INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL - I need to change this columns DataType to BIGINT

Can I just drop the primary key constraint and then

--ALTER TABLE [TableName]
--ALTER COLUMN  ColumnName  BIGINT NOT NULL

If I do this will it keep the same structure with the IDENTITY field and the not for replication not null or do I need to do something different

Thanks In Advance
VitaminDAsked:
Who is Participating?
 
deviprasadgConnect With a Mentor Commented:
It should keep the same structure with the IDENTITY field and the not for replication not null.
Your plan looks good.

Below is the demo script:
DROP TABLE TESTPK
GO

CREATE TABLE TESTPK(
ID INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL ,
A VARCHAR(100))
GO

ALTER TABLE TESTPK ADD CONSTRAINT PKTESTPK PRIMARY KEY (ID)
GO

INSERT INTO TESTPK(A)
SELECT 'A'
GO 10

SELECT * FROM TESTPK
GO

ALTER TABLE TESTPK
DROP CONSTRAINT PKTESTPK

GO

ALTER TABLE TESTPK
ALTER COLUMN ID BIGINT

GO

ALTER TABLE TESTPK ADD CONSTRAINT PKTESTPK PRIMARY KEY (ID)

GO

INSERT INTO TESTPK(A)
SELECT 'A'
GO 10

SELECT * FROM TESTPK
GO

Open in new window

0
 
krtyknmsqlCommented:
Yes, You can drop the Primary Key constraint then alter the column to change the data type and recreate the primary key constraint as Deviprasad mentioned with three steps.

ALTER TABLE Tbl DROP CONSTRAINT PK_Tbl
GO

ALTER TABLE Tbl ALTER COLUMN ID BIGINT 
GO

ALTER TABLE Tbl ADD CONSTRAINT PK_Tbl PRIMARY KEY (ID)
GO

Open in new window

0
 
LowfatspreadCommented:
which version/edition of sql server?
how big is the table rows/columns?
is the column involved in any other parent/child relations ...? (ie is it used as a foreign key in any other tables?)

is the table actually being published (or is it the destination of a publication) as part of a replication strategy?

...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.