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?
Best Answer
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