remove Identity from a column
-
Hi Dudes,
I have a Table Which has a column that is set to identity by mistake and it is on my customers server, so I need to create my sql commands and run it at once.
How can I remove the Identity column by code?
Best Regards,
Ashkan -
note:I know I can use SET IDENTITY_INSERT to disable it temporarily but i need a query to remove it....
Best Regards,
Ashkan -
To remove the Identity property, creatr a new column name 'tempID' then temporarily storing the ID data into it while I recreate ID column so as to remove the Identity property.
The following code assumes that the column is also the primary key (which it usually is).
ALTER TABLE [TableName]
ADD tempID int NULL
GO
UPDATE [TableName]
SET tempID=[IdentityColumnName]
GO
ALTER TABLE [TableName]
DROP CONSTRAINT PK_[TableName]
GO
ALTER TABLE [TableName]
DROP COLUMN [IdentityColumnName]
GO
ALTER TABLE [TableName]
ADD [IdentityColumnName] int NULL
GO
UPDATE [TableName]
SET [IdentityColumnName]=tempID
GO
ALTER TABLE [TableName]
ALTER COLUMN [IdentityColumnName] int NOT NULL
GO
ALTER TABLE [TableName]
ADD CONSTRAINT PK_[TableName] PRIMARY KEY CLUSTERED ([IdentityColumnName]) ON [PRIMARY]
--or ADD CONSTRAINT [PrimaryKeyConstraintName]
GO
-
Shall I drop my column and all of its relations?:(
This is Terrible:(
Best Regards,
Ashkan -
Isn't there any solution without droping?
Best Regards,
Ashkan -
ashkan siroos (6/16/2011)
Isn't there any solution without droping?No. That's an unfortunate property of IDENTITY columns you bump up against when you want to change your schema. They are bound to the underlying table structure different than non-IDENTITY columns are bound. SQL Server "Denali" (Version 11 without a commercial name as of yet) adds support for SEQUENCES which eases the situation you're in.
Columns that make use of SEQUENCES can be altered as any other column would be and associating or disassociating a column from a SEQUENCE does not require dropping the column, you will only have to worry about the CONSTRAINTS.
See Example G.
http://msdn.microsoft.com/en-us/library/ff878058%28v=SQL.110%29.aspx#CodeSpippet9
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Sorry for the typos, BTW. I was getting into a meeting.
-
ashkan siroos (6/16/2011)
Shall I drop my column and all of its relations?:(This is Terrible:(
Ashkan
The code I gave you is safe and preserves all your data. And it is reasonably fast. And it came free. 🙂
Good luck.
- ashkan sirous - Thursday, June 16, 2011 9:30 AM
Yes. go to
Tools à Options à ='mso-char-type:symbol;mso-symbol-font-family:wingdings'> Designersà Table and DatabaseDesigners
In database Designers there is one checkbox named “Prevent saving changes that require table recreation†, uncheck that and then try to remove identity column from Design of the table and save it.
- ashkan sirous - Thursday, June 16, 2011 9:30 AM
Try this.
Tools à Options à ='mso-char-type:symbol;mso-symbol-font-family:wingdings'> Designersà Table and DatabaseDesigners
In database Designers there is one checkbox named “Prevent saving changes that require table recreation†, uncheck that and then try to remove identity column from Design of the table and save it. once got save successfully again check that check box.
-
Go to
Tools àOptions à Designersà Table and Database Designers
In Table and database Designers there is one checkbox named “Prevent saving changes that require table recreation” , uncheck that and then try to remove identity column from Design of the table after that.
once getting save successfully again check that check box.This might work.
🙂 -
HI,
FYI, in case you hadn't noticed, you are replying to a 7 year old post 🙂 -
@Nigel, I noticed that too but they probably arrived here through a search engine which means others will too...which means I am compelled to provide some additional info.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato - MADDY30 - Wednesday, July 25, 2018 10:48 PM
Not so fast 🙂
While that may allow you to "save" the table what is really happening under the covers is this:1. create a new table with a temporary name that mimics the schema of the original table, except without an IDENTITY column defined
2. copy all data from the original table to the new table
3. drop the existing table
4. rename the table with the temporary name to have the original table nameSo while the GUI can make it seem like a simple one-click operation there is real work happening behind the scenes. On a large table this can take a long time and be very disruptive to other clients of the database which is likely why Microsoft opted to "prevent saving changes that require table re-creation" and make people disable that if they want to do that work through the GUI.
A good practice when using the GUI to maintain a database is to make your desired changes but not save them. Instead, once you have things looking how you want script the changes to have a look at what the Save button would do if you clicked it. This ends up being an instructive and safe way to manage your databases using the GUI. In the Table Designer menu in newer versions of SSMS:
I am afraid if you need to preserve the column-order the only way to remove IDENTITY from a column is to make a new table, copy your data, drop your original table and rename your new table with the original name.
In the image example from above this is the change script SSMS generates:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tbl
(
id bigint NOT NULL,
name char(100) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_tbl SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.tbl)
EXEC('INSERT INTO dbo.Tmp_tbl (id, name)
SELECT id, name FROM dbo.tbl WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.tbl
GO
EXECUTE sp_rename N'dbo.Tmp_tbl', N'tbl', 'OBJECT'
GO
COMMITThere are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply