Drop Identity

Is there anyway to drop the Identity characteristic from a field? I have a database where I need to remove some Identity characteristics from the primary keys.

I have been able to work around this situation by deleting the primary key column, and creating a new primary key without the Identity... but that seems the long way around.Use enterprise manager. Right-click the table and choose design table. Once in design view you can remove the identity.|||Thanks for your reply. I should have qualified my initial problem. I knew I could remove the Identity with the Enterprise Manager, but I wanted to script the change so that it could be run on a couple of different remote servers by individuals other than myself.

Any idea how to do that?|||You could make the changes as suggested above and then save them to a script file (instead of executing them). That's an option in SQL EM when you are in table design mode. I think in this case (though I'm not positive), that the script EM will generate will create a temp table (without the identity fields), copy all the records into the temp table, drop the original table and then rename the new temp table with the name of the original table.

I could be wrong on the sequence, but I think that is what is required when eliminating the identity property from a column.

Regards,

Hugh Scott

Originally posted by acg_ray
Thanks for your reply. I should have qualified my initial problem. I knew I could remove the Identity with the Enterprise Manager, but I wanted to script the change so that it could be run on a couple of different remote servers by individuals other than myself.

Any idea how to do that?|||Use EM the same way as if you remove identity in design table, but click SCRIPT instead of SAVE.
Identity cannot be dropped without dropping column with identity. To free this dropped fixed column used space, you must drop table. So without dropping table you cannot remove identity and keep fast table. You need too many opperations to be done...|||This is what ss does - your method is probably faster:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Test
(
ID int NOT NULL,
Code varchar(10) NULL,
Name varchar(255) NOT NULL,
Conference int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Divisions)
EXEC('INSERT INTO dbo.Tmp_Test(ID, Code, Name, Conference)
SELECT ID, Code, Name, Conference FROM dbo.Test TABLOCKX')
GO
DROP TABLE dbo.Test
GO
EXECUTE sp_rename 'dbo.Tmp_Test', 'Test'
GO
COMMIT|||But you must drop and recreate all referenced objects, all those PK,FK,DF,CK,IX and other schema bound objects.|||Thanks... this will be of great help.

- Ray

Originally posted by hmscott
You could make the changes as suggested above and then save them to a script file (instead of executing them). That's an option in SQL EM when you are in table design mode. I think in this case (though I'm not positive), that the script EM will generate will create a temp table (without the identity fields), copy all the records into the temp table, drop the original table and then rename the new temp table with the name of the original table.

I could be wrong on the sequence, but I think that is what is required when eliminating the identity property from a column.

Regards,

Hugh Scott