How to remove auto increment from table in sql server 2012

Question

I have created a table in SQL Server 2012 with primary key as auto increment. But how can I remove that auto increment property from the table using a SQL query?

Beseech

#8834933 – Femininity

Drop the PK constraint, drop the column - there's no way to just remove the IDENTITY specification from an existing column .... – Rahr

#8230757 – Deferment

Answer 1

If you need to keep the data in that column then create a new column on the table which is of the same type (but a different name), copy the data from the column you want to get rid of to the new one, drop the old column and rename the new. Complete example:

CREATE TABLE test(col1 INT IDENTITY (1,1) NOT NULL, col2 VARCHAR(10) NULL);

ALTER TABLE test ADD col3 INT NULL;

UPDATE test SET col3 = col1;

ALTER TABLE test DROP COLUMN col1;

EXEC sp_rename 'dbo.test.col3', 'col1', 'COLUMN';

Fade

Answer 2

The easiest way would be:

1. Open SQL Server Management Studio.
2. Locate Server > DataBase > Table.
3. Right Click on the Table > Select Design.
4. In the design window, Highlight the column you want to modify.
5. In the Column Properties Window browse to Identity Specification > Is Identity And set to No.
6. Go to the toolbar menu > Table Designer > Select Generate Change Script...
7. Walla, you got the requested script.

I Like using this method for getting scripts, since it allows me to generate scripts I'm not sure how to compose from scratch and thus learning and improving my skills...

Wimer

I get "Saving changes is not permitted..." – Ixia

If Is Identity = True/Yes, you cannot remove it without dropping the column – Bibliopole

Answer 3

If it's a primary key column, then you have to drop the PK first. If there's any tables referencing it, then you'll have to drop these FKs to be able to drop the PK. After that, add another column of the same type, update it with values from identity column, drop the identity column, rename the new column to whatever the name of identity column was (with sp_rename procedure), recreate the PK, recreate the FKs, check if everything went right.

I'd be very careful doing it on a production database. Ensure that noone can access the data while you're doing this.

Espinoza

Answer 4

I searched a lot to find a simple solution to remove the auto increment because i should do a lot of work if i drop the column and the primary key which was a foreign key on another table where I should remove the data that are using my foreign ... finally I ended up with a very simple solution that made my life easy:

SET IDENTITY_INSERT <table_name> ON ;

Sulky

this is temporarily turning off, it can be switched on. So the auto increment is not removed. – Krieg

Answer 5

You can remove 'auto_increment' by using following query...

ALTER TABLE <table_name> MODIFY <auto_incremented_column_name> <column_data_type>;

For exmample,

table_name = STUDENT,

auto_incremented_column_name = ID,

column_data_type = INT

ALTER TABLE STUDENT MODIFY ID INT;

To add 'auto_increment'

ALTER TABLE STUDENT MODIFY ID INT AUTO_INCREMENT;

Fauch

Answer 6

SET IDENTITY_INSERT [TABLE] OFF .. this allows to remove the auto increment to off state.., so that we have to enter the value in thatcolumn

Hereabout

No it doesn't - this would mean that you cannot insert directly to the column. you need to set IDENTITY_INSERT to ON to override the auto number. Besides which, this is not really a proper solution because it hasn't removed the identity spec of the column and could be turned on in future with potentially unwanted or even dangerous consequences. – Fade