Solved

Removing column IDENTITY using T-SQL

Posted on 2004-03-25
13
1,693 Views
Last Modified: 2011-10-03
What is the T-SQL syntaxe for removing/adding a column identity ?
0
Comment
Question by:mike_marquet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 10676981
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
0
 
LVL 13

Expert Comment

by:danblake
ID: 10676994
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 ?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10677042
@dan
Are you sure it works ??
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 13

Expert Comment

by:danblake
ID: 10677054
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


0
 

Author Comment

by:mike_marquet
ID: 10677070
the code given by danblake don't works, I have already try it before !
0
 
LVL 13

Expert Comment

by:danblake
ID: 10677079
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.
0
 

Author Comment

by:mike_marquet
ID: 10677091
The identity column where I want to remove the identity for can not be delete and reinserted in my case
0
 

Author Comment

by:mike_marquet
ID: 10677102
SQL Enterprise Manager can toggle the identity therefore I think there is way to do it but how ?
0
 
LVL 13

Accepted Solution

by:
danblake earned 63 total points
ID: 10677142
SQL Enterprise Manager can toggle the identity therefore I think there is way to do it but how ?

The script generated is a complete drop script / rebuild script.

If you click on the Save script button after using EM, you will be able to view the script.
0
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 62 total points
ID: 10677211
SQL EM often generates drop-create statements

To drop an ID column you can

alter table yourtable add newcol int
update yourtable set newcol = oldcol
alter table yourtable drop column oldcol
exec sp_rename 'yourtable.newcol', 'yourtable.oldcol', 'COLUMN'

You'll also have to drop / create indexes, constraints, ... if you have some that use this col

The same can be achieved to create a "new" col with identity using the same logic and the
SET IDENTITY_INSERT <Table_Name> ON / OFF as explained in a previous post


Hilaire


0
 

Expert Comment

by:lguzman1509
ID: 24282957
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.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question