February 13, 2007 at 10:10 am
I have a column col1 in the table tbl1 with Identity property.I wanted to remove the identity property on that column using a sql command. Usually I do this task from enterprise manager.
I need to remove this Identity property because I have to update the values in that column.
Thanks.
February 13, 2007 at 10:26 am
Why do you have to update the values in the identity column? An ID really should not have any particular meaning, other than to provide a row identifier.
February 13, 2007 at 10:27 am
You cannot remove the identity property of a column. You must rebuild the table to do this. If you've done this via EM in the past, what EM does is drop and re-create the table. If you need to be able to update the values, look into SET IDENTITY_INSERT in BOL. Otherwise, see this thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=344314
March 7, 2007 at 10:34 am
In SQL 2000 you could do the following (wont work in SQL 2005)
---
USE [DB_NAME]
EXEC dbo.sp_configure @configname = 'allow update'
, @configvalue = '1'
RECONFIGURE WITH OVERRIDE
UPDATE dbo.syscolumns
SET colstat = 0
WHERE id = object_id('[dbo].[TABLE_NAME]')
AND name = 'FIELD_NAME'
EXEC dbo.sp_configure @configname = 'allow update'
, @configvalue = '0'
RECONFIGURE WITH OVERRIDE
---
Note: In order to reconfigure you will need to be sysadmin to the server... the configuration change will enable changes to the sys[tables] for anybody that would have permission to the individual databases. So it is very important that after you are done you reconfigure back to 'allow update' with value 0.
Much safer approach that requires less permissions/risk to server and actually just enough permission to database/table in question would be to rename the column to predictably unique name (add something like '_' before it)... then add a new column with the original name. Copy all the data into that... then drop the renamed 'identity' column. Only con here is that you will have to worry about whatever other properties existed for the original field like Index, Primary Key, or Foreign Key.
March 7, 2007 at 11:33 am
the correct answer is to not remove the identity at all...NEVER update sys tables directly...
the identity column sis there for conveneince, and you can still insert by setting an additional property. assume your table has a "gap" and for some reason you want to re-use the values for the identity....simply insert the values you want like this:
As others stated, you should not have any business logic related to WHAt value the Id field has...just that it exists as a reference to uniquely identify the row...gaps in the sequence are perfectly normal, and would occur naturally if a transaction was rolled back during an INSERT statement.
SET IDENTITY_INSERT YOURTABLENAME ON
INSERT INTO YOURTABLENAME (IDENTITYCOLUMN,OTHERFIELDS) VALUES(99,'Some Value')
INSERT INTO YOURTABLENAME (IDENTITYCOLUMN,OTHERFIELDS) VALUES(100,'Some other Value')
SET IDENTITY_INSERT YOURTABLENAME OFF
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply