Return to Revisions

There is a big problem that you have not considered:

• As far as I know you cannot remove IDENTITY from a column.
• Hence, you can only convert this column to int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0

Not good news, but you could try another approach:

• Add a new column
• Fill it with new values
• Drop PRIMARY KEY constraint.
• Drop ID column
• Rename new column as Id
• Add a PRIMARY KEY constraint on Id column

CREATE TABLE MyTable
(
    Id int IDENTITY(1,1),
    F1 int,
    CONSTRAINT [PK_MyTable] PRIMARY KEY (Id)
);

INSERT INTO MyTable VALUES (10),(20),(30);

--= Add a new column
ALTER TABLE MyTable ADD Id2 uniqueidentifier DEFAULT NewID() NOT NULL;

--= Fill new field with values
UPDATE MyTable 
SET    Id2 = NewID();

--= Drop PRIMARY KEY
ALTER TABLE MyTable DROP CONSTRAINT [PK_MyTable];

--= Drop Id column
ALTER TABLE MyTable DROP COLUMN Id;

--= Rename Id2 column as Id
EXEC sp_RENAME 'MyTable.Id2', 'Id', 'COLUMN';

--= Add PRIMARY KEY
ALTER TABLE MyTable ADD CONSTRAINT [PK_MyTable] PRIMARY KEY (Id);
    
SELECT * FROM myTable;
GO

F1 | Id                                  
-: | :-----------------------------------
30 | e430b87c-6fbf-43c3-a88c-00abe925c2ea
20 | 67e6d0ac-3bb8-4c29-b19b-501ebdc2acee
10 | 287117fb-0188-480f-be54-ef42cacc123a

Caution: Changing any part of an object name could break scripts and stored procedures.

dbfiddle here

Even though this works, let me try to add a new table:

CREATE TABLE MyTable2
(
    Id int,
    MyTable_Id int NOT NULL,
    CONSTRAINT [PK_MyTable2] PRIMARY KEY (Id),
    CONSTRAINT [FK_MyTable2] FOREIGN KEY (Id) REFERENCES MyTable(Id)
);

Yes, there is a FOREIGN KEY that references Id field of MyTable. Now try to execute the script again and...

Msg 3725 Level 16 State 0 Line 25

The constraint 'PK_MyTable' is being referenced by table 'MyTable2', foreign key constraint 'FK_MyTable2'. Msg 3727 Level 16 State 0 Line 25 Could not drop constraint. See previous errors.

dbfiddle here

Yes, bad news again, to drop a PRIMARY KEY, you should DROP all FOREIGN KEYS that references this field before.