Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1703
  • Last Modified:

Removing column IDENTITY using T-SQL

What is the T-SQL syntaxe for removing/adding a column identity ?
0
mike_marquet
Asked:
mike_marquet
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
HilaireCommented:
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
 
danblakeCommented:
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
 
HilaireCommented:
@dan
Are you sure it works ??
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
danblakeCommented:
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
 
mike_marquetAuthor Commented:
the code given by danblake don't works, I have already try it before !
0
 
danblakeCommented:
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
 
mike_marquetAuthor Commented:
The identity column where I want to remove the identity for can not be delete and reinserted in my case
0
 
mike_marquetAuthor Commented:
SQL Enterprise Manager can toggle the identity therefore I think there is way to do it but how ?
0
 
danblakeCommented:
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
 
HilaireCommented:
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
 
lguzman1509Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now