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),
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
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;
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.
Even though this works, let me try to add a new table:
CREATE TABLE MyTable2
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.
Yes, bad news again, to drop a PRIMARY KEY, you should DROP all FOREIGN KEYS that references this field before.
I've found a good article about how to drop and recreate all foreign keys, perhaps you could use it on this issue.