I have a table table1
in SQL server 2008 and it has records in it.
I want the primary key table1_Sno
column to be an auto-incrementing column. Can this be done without any data transfer or cloning of table?
I know that I can use ALTER TABLE to add an auto-increment column, but can I simply add the AUTO_INCREMENT option to an existing column that is the primary key?
7条答案
按热度按时间q5iwbnjs1#
Changing the
IDENTITY
property is really a metadata only change. But to update the metadata directly requires starting the instance in single user mode and messing around with some columns insys.syscolpars
and is undocumented/unsupported and not something I would recommend or will give any additional details about.For people coming across this answer on SQL Server 2012+ by far the easiest way of achieving this result of an auto incrementing column would be to create a
SEQUENCE
object and set thenext value for seq
as the column default.Alternatively, or for previous versions (from 2005 onwards), the workaround posted on this connect item shows a completely supported way of doing this without any need for size of data operations using
ALTER TABLE...SWITCH
. Also blogged about on MSDN here . Though the code to achieve this is not very simple and there are restrictions - such as the table being changed can't be the target of a foreign key constraint.Example code.
Set up test table with no
identity
column.Alter it to have an
identity
column (more or less instant).Test the result.
Gives
Clean up
jei2mxaa2#
SQL Server: How to set auto-increment on a table with rows in it:
This strategy physically copies the rows around twice which can take a much longer time if the table you are copying is very large.
You could save out your data, drop and rebuild the table with the auto-increment and primary key, then load the data back in.
I'll walk you through with an example:
Step 1, create table foobar (without primary key or auto-increment):
Step 2, insert some rows
Step 3, copy out foobar data into a temp table:
Step 4, drop table foobar:
Step 5, recreate your table with the primary key and auto-increment properties:
Step 6, insert your data from temp table back into foobar
Step 7, drop your temp table, and check to see if it worked:
You should get this, and when you inspect the foobar table, the id column is auto-increment of 1 and id is a primary key:
7y4bm7vi3#
If you want to do this via the designer you can do it by following the instructions here "Save changes is not permitted" when changing an existing column to be nullable
pzfprimi4#
Yes, you can. Go to Tools > Designers > Table and Designers and uncheck "Prevent Saving Changes That Prevent Table Recreation".
vnzz0bqm5#
No, you can not add an auto increment option to an existing column with data, I think the option which you mentioned is the best.
Have a look here .
ktca8awb6#
If you don't want to add a new column, and you can guarantee that your current int column is unique, you could select all of the data out into a temporary table, drop the table and recreate with the IDENTITY column specified. Then using
SET IDENTITY INSERT ON
you can insert all of your data in the temporary table into the new table.kx7yvsdv7#
Below script can be a good solution.Worked in large data as well.
ALTER DATABASE WMlive SET RECOVERY SIMPLE WITH NO_WAIT
ALTER TABLE WMBOMTABLE DROP CONSTRAINT PK_WMBomTable
ALTER TABLE WMBOMTABLE drop column BOMID
ALTER TABLE WMBOMTABLE ADD BomID int IDENTITY(1, 1) NOT NULL;
ALTER TABLE WMBOMTABLE ADD CONSTRAINT PK_WMBomTable PRIMARY KEY CLUSTERED (BomID);
ALTER DATABASE WMlive SET RECOVERY FULL WITH NO_WAIT