sqlsql-servert-sqlsql-server-2012

Convert an existing Column to Identity

I have a table in SQL Server with bundle of records. I want to convert the ID column which is Primary Key to an identity Column without loss of data. I thought of the following two approaches:

  1. Create a new table with identity & drop the existing table.
  2. Create a new column with identity & drop the existing column.

but it's clear that they can not be implemented because keeping records is my first priority.

Is there another way to do this?

Solution

  • As you are using SQL Server 2012, another possible alternative could be to create a sequence object that has a starting value of the highest ID +1 already in your table, then create a default constraint for your column using GET NEXT VALUE FOR and reference your sequence object you just created.