13

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3458120
  • 161
  • 1
  • 2
  • 6
  • http://stackoverflow.com/questions/8834433/remove-auto-increment-by-script-sql-server – slavoo May 07 '14 at 07:25
  • Drop the PK constraint, drop the column - there's no way to just remove the `IDENTITY` specification from an existing column .... – marc_s May 07 '14 at 07:26
  • http://stackoverflow.com/questions/8230257/sql-server-remove-identity-from-a-column-in-a-table – MarkD May 07 '14 at 07:30

6 Answers6

14

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';
Steve Pettifer
  • 1,975
  • 1
  • 19
  • 34
13

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...

enter image description here

Avi Turner
  • 10,234
  • 7
  • 48
  • 75
6

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.

dean
  • 9,960
  • 2
  • 25
  • 26
6

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 ;
Chris Sim
  • 4,054
  • 4
  • 29
  • 36
  • this is temporarily turning off, it can be switched on. So the auto increment is not removed. – tibi Oct 30 '22 at 20:39
0

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

Avi Turner
  • 10,234
  • 7
  • 48
  • 75
Goutham Arul
  • 59
  • 1
  • 2
  • 10
  • 8
    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. – Steve Pettifer Nov 14 '14 at 16:07
0

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;
Brijesh Kavar
  • 1