Solved

Removing column IDENTITY using T-SQL

Posted on 2004-03-25
13
1,692 Views
Last Modified: 2011-10-03
What is the T-SQL syntaxe for removing/adding a column identity ?
0
Comment
Question by:mike_marquet
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

860 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