Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Removing column IDENTITY using T-SQL

Posted on 2004-03-25
13
Medium Priority
?
1,699 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 252 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 248 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

722 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