Solved

Removing column IDENTITY using T-SQL

Posted on 2004-03-25
13
1,695 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

729 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