We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Removing column IDENTITY using T-SQL

mike_marquet
mike_marquet asked
on
Medium Priority
1,764 Views
Last Modified: 2011-10-03
What is the T-SQL syntaxe for removing/adding a column identity ?
Comment
Watch Question

Commented:
As far as I know there's no way to add the identity constraint to an existing column
nor to drop the identity constraint
(whithout dropping/recreating)

BUt you can temporarily disable the identity_insert using the following syntax

Disabling Identity insert:
SET IDENTITY_INSERT <Table_Name> ON
GO

enabling Identity insert:
SET IDENTITY_INSERT <Table_Name> OFF
GO

Commented:
Try:

ALTER TABLE doc_exe ALTER COLUMN
/* Modify Column_b to have an identity column*/
column_b INT IDENTITY

And the removal is:
ALTER TABLE doc_exe ALTER COLUMN
/* Modify Column_b to have an not have an identity column*/
column_b INT

A slightly bigger question, why do this ?

Commented:
@dan
Are you sure it works ??

Commented:
This code given, is untested.  If there is a way of performing the altering of a column it will be with the ALTER table <table_name> alter column <column_properties> syntax.  I have just had a look at BOL, it does look like Hillare you are right -- it is not yet possible.  

removing/adding a column identity ?
So the only way would be to perform an: (Add identity property:)
alter table doc_exe ADD column_c INT IDENTITY (1,1)

And a remove:
alter table doc_exe DROP column_c


Author

Commented:
the code given by danblake don't works, I have already try it before !

Commented:
I wish that it would be possible to redefine a column fully without fully modifying a table at times...but then there are very good reasons why you would not want to do that.

Author

Commented:
The identity column where I want to remove the identity for can not be delete and reinserted in my case

Author

Commented:
SQL Enterprise Manager can toggle the identity therefore I think there is way to do it but how ?
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
The only way is to export the data from the identity column to another column with the int datatype, drop the original column and rename de intermediate column.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.