Adding an identity to an existing column

Question

I need to change the primary key of a table to an identity column  and there s already a number of rows in table    I ve got a script to clean up the IDs to ensure they re sequential starting at 1  works fine on my test database    What s the SQL command to alter the column to have an identity property

User · Answer

Consider to use SEQUENCE instead of IDENTITY.

IN sql server 2014 (I don't know about lower versions) you can do this simply, using sequence.

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;

ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

From here: Sequence as default value for a column

User · Answer

generates a script for all tables with primary key   bigint which do not have an identity set  this will return a list of generated scripts with each table    SET NOCOUNT ON   declare  sql table s varchar max   id int identity   DECLARE  table name nvarchar max            table schema nvarchar max    DECLARE vendor cursor CURSOR FOR  SELECT   t name  s name FROM sys schemas AS s INNER JOIN sys tables AS t   ON s  schema id    t  schema id  WHERE EXISTS       SELECT      c   name      from sys columns  c      join sys types  y  on  y  system type id    c  system type id     where  c   object id     t   object id  and  y  name    bigint  and  c   column id    1   and NOT EXISTS      SELECT 1 FROM sys identity columns     WHERE  object id    t  object id    and exists       select 1 from sys indexes as  i       inner join sys index columns as  ic   ON  i OBJECT ID   ic OBJECT ID AND i index id   ic index id     where object name  ic   object id      t   name    OPEN vendor cursor  FETCH NEXT FROM vendor cursor  INTO  table name   table schema  WHILE   FETCH STATUS   0 BEGIN  DELETE FROM  sql  declare  pkname varchar 100        pkcol nvarchar 100   SELECT  top 1          pkname   i name           pkcol   COL NAME ic OBJECT ID ic column id  FROM    sys indexes AS  i  INNER JOIN sys index columns AS  ic  ON  i OBJECT ID   ic OBJECT ID AND i index id   ic index id WHERE   i is primary key   1 and OBJECT NAME ic OBJECT ID     table name  declare  q nvarchar max     SELECT     pkcol   FROM     table schema        table name    ORDER BY    pkcol   DESC   DECLARE  ident seed nvarchar max  -- Change this to the datatype that you are after SET  q   REPLACE  q   SELECT    SELECT TOP 1  output      EXEC sp executeSql  q  N  output bigint OUTPUT    ident seed OUTPUT  insert into   sql s  values   BEGIN TRANSACTION   insert into   sql s  values   BEGIN TRY    -- create statement insert into   sql s  values   create table     table schema          table name     Temp       -- column list insert into  sql s   select             c   name             y name          case when  y   name  like   varchar  then     coalesce      case when   c   max length   lt  0 or  c   max length   gt   1024  then  max  else cast  c  max length as varchar  end              else    end                    case when  c  name    pkcol then  IDENTITY    COALESCE  ident seed   1     1   else    end               case when c is nullable   0 then  NOT   else    end      NULL          coalesce  DEFAULT              REPLACE              REPLACE                  LTrim                      RTrim                          REPLACE                              REPLACE                                  REPLACE                                      REPLACE                                          LTrim                                              RTrim                                                  REPLACE                                                      REPLACE                                                          object definition  c  default object id                                                                                                                                                                                                                                                                                                                                                                                                                                                                              case when object definition  c  default object id  like   get date   then      else    end                          from sys columns c  JOIN sys types y ON y system type id   c system type id   where OBJECT NAME c  object id      table name and  y  name     sysname   order by  c  column id    update  sql set s left s len s -1  where id   identity  -- closing bracket insert into  sql s  values         insert into  sql s  values   SET IDENTITY INSERT     table schema        table name   Temp  ON    declare  cols nvarchar max  SELECT  cols   STUFF                select      c name             from sys columns c         JOIN sys types y ON y system type id   c system type id         where c  object id    OBJECT ID  table name          and  y  name     sysname          and  y  name     timestamp          order by  c  column id         FOR XML PATH                   1  1       insert into  sql s  values   IF EXISTS SELECT   FROM     table schema        table name       insert into  sql s  values   EXEC   INSERT INTO     table schema        table name   Temp      cols      insert into  sql s  values   SELECT    cols   FROM     table schema        table name          insert into  sql s  values   SET IDENTITY INSERT     table schema        table name   Temp  OFF     insert into  sql s  values   DROP TABLE     table schema        table name       insert into  sql s  values   EXECUTE sp rename N      table schema        table name   Temp     N     table name        OBJECT      if    pkname is not null   begin     insert into  sql s  values  ALTER TABLE     table schema        table name    ADD CONSTRAINT     pkname    PRIMARY KEY CLUSTERED         insert into  sql s          select       COLUMN NAME    ASC   from information schema key column usage         where constraint name    pkname         GROUP BY COLUMN NAME  ordinal position         order by ordinal position      -- remove trailing comma     update  sql set s left s len s -1  where id   identity     insert into  sql s  values         end  insert into   sql s  values   --Run your Statements   insert into   sql s  values   COMMIT TRANSACTION   insert into   sql s  values   END TRY   insert into   sql s  values   BEGIN CATCH   insert into   sql s  values           ROLLBACK TRANSACTION   insert into   sql s  values           DECLARE  Msg NVARCHAR MAX      insert into   sql s  values           SELECT  Msg ERROR MESSAGE      insert into   sql s  values           RAISERROR   Error Occured   s    20  101  msg  WITH LOG   insert into   sql s  values   END CATCH    declare  fqry nvarchar max   -- result  SELECT  fqry    select char 10    s from  sql order by id FOR XML PATH         SELECT  table name as  Table Name    fqry as  Generated Query  PRINT  Table     table name EXEC sp executeSql  fqry      FETCH NEXT FROM vendor cursor      INTO  table name   table schema END  CLOSE vendor cursor  DEALLOCATE vendor cursor

User · Answer

To modify the identity properties for a column    In Server Explorer  right-click the table with identity properties you want to modify and click Open Table Definition  The table opens in Table Designer  Clear the Allow nulls check box for the column you want to change  In the Column Properties tab  expand the Identity Specification property  Click the grid cell for the Is Identity child property and choose Yes from the drop-down list  Type a value in the Identity Seed cell  This value will be assigned to the first row in the table  The value 1 will be assigned by default    That s it  and it worked for me

User · Answer

You can t alter the existing columns for identity  You have 2 options   Create a new table with identity  amp  drop the existing table  Create a new column with identity  amp  drop the existing column   Approach 1   New table  Here you can retain the existing data values on the newly created identity column  Note that you will lose all data if  if not exists  is not satisfied  so make sure you put the condition on the drop as well  CREATE TABLE dbo Tmp Names             Id int NOT NULL              IDENTITY 1  1         Name varchar 50  NULL       ON   PRIMARY  go  SET IDENTITY INSERT dbo Tmp Names ON go  IF EXISTS   SELECT                FROM    dbo Names        INSERT  INTO dbo Tmp Names   Id  Name               SELECT  Id                      Name             FROM    dbo Names TABLOCKX go  SET IDENTITY INSERT dbo Tmp Names OFF go  DROP TABLE dbo Names go  Exec sp rename  Tmp Names    Names   Approach 2  New column  You can   t retain the existing data values on the newly created identity column  The identity column will hold the sequence of number  Alter Table Names Add Id new Int Identity 1  1  Go  Alter Table Names Drop Column ID Go  Exec sp rename  Names Id new    ID    Column   See the following Microsoft SQL Server Forum post for more details  How to alter column to identity 1 1

User · Answer

Right click on table name in Object Explorer  You will get some options  Click on  Design   A new tab will be opened for this table  You can add Identity constraint here in  Column Properties

User · Answer

Simple explanation  Rename the existing column using sp RENAME  EXEC sp RENAME  Table Name Existing ColumnName     New ColumnName    COLUMN   Example for Rename    The existing column UserID is renamed as OldUserID  EXEC sp RENAME  AdminUsers UserID     OldUserID    COLUMN    Then add a new column using alter query to set as primary key and identity value  ALTER TABLE TableName ADD Old ColumnName INT NOT NULL PRIMARY KEY IDENTITY 1 1    Example for Set Primary key  The new created column name is UserID  ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY 1 1    then Drop the Renamed Column  ALTER TABLE Table Name DROP COLUMN Renamed ColumnName   Example for Drop renamed column  ALTER TABLE Users DROP COLUMN OldUserID   Now we ve adding a primarykey and identity to the existing column on the table

User · Answer

Basically there are four logical steps    Create a new Identity column  Turn on Insert Identity for this new column  Insert the data from the source column  the column you wished to convert to Identity  to this new column  Turn off the Insert Identity for the new column  Drop your source column  amp  rename the new column to the name of the source column    There may be some more complexities like working across multiple servers etc   Please refer the following article for the steps  using ssms  amp  T-sql   These steps are intended for beginners with less grip on T-SQL   http   social technet microsoft com wiki contents articles 23816 how-to-convert-int-column-to-identity-in-the-ms-sql-server aspx

User · Answer

In SQL 2005 and above  there s a trick to solve this problem without changing the table s data pages  This is important for large tables where touching every data page can take minutes or hours   The trick also works even if the identity column is a primary key  is part of a clustered or non-clustered index  or other gotchas which can trip up the the simpler  add remove rename column  solution    Here s the trick  you can use SQL Server s ALTER TABLE   SWITCH statement to change the schema of a table without changing the data  meaning you can replace a table with an IDENTITY with an identical table schema  but without an IDENTITY column   The same trick works to add IDENTITY to an existing column   Normally  ALTER TABLE   SWITCH is used to efficiently replace a full partition in a partitioned table with a new  empty partition   But it can also be used in non-partitioned tables too   I ve used this trick to convert  in under 5 seconds  a column of a of a 2 5 billion row table from IDENTITY to a non-IDENTITY  in order to run a multi-hour query whose query plan worked better for non-IDENTITY columns   and then restored the IDENTITY setting  again in less than 5 seconds   Here s a code sample of how it works     CREATE TABLE Test       id int identity 1 1      somecolumn varchar 10        INSERT INTO Test VALUES   Hello     INSERT INTO Test VALUES   World      -- copy the table  use same schema  but no identity  CREATE TABLE Test2       id int NOT NULL     somecolumn varchar 10        ALTER TABLE Test SWITCH TO Test2    -- drop the original  now empty  table  DROP TABLE Test    -- rename new table to old table s name  EXEC sp rename  Test2   Test     -- update the identity seed  DBCC CHECKIDENT  Test      -- see same records  SELECT   FROM Test     This is obviously more involved than the solutions in other answers  but if your table is large this can be a real life-saver   There are some caveats    As far as I know  identity is the only thing you can change about your table s columns with this method  Adding removing columns  changing nullability  etc  isn t allowed   You ll need to drop foriegn keys before you do the switch and restore them after  Same for WITH SCHEMABINDING functions  views  etc   new table s indexes need to match exactly  same columns  same order  etc   Old and new tables need to be on the same filegroup  Only works on SQL Server 2005 or later I previously believed that this trick only works on the Enterprise or Developer editions of SQL Server  because partitions are only supported in Enterprise and Developer versions   but Mason G  Zhwiti in his comment below says that it also works in SQL Standard Edition too   I assume this means that the restriction to Enterprise or Developer doesn t apply to ALTER TABLE   SWITCH    There s a good article on TechNet detailing the requirements above   UPDATE - Eric Wu had a comment below that adds important info about this solution  Copying it here to make sure it gets more attention       There s another caveat here that is worth mentioning  Although the   new table will happily receive data from the old table  and all the   new rows will be inserted following a identity pattern  they will   start at 1 and potentially break if the said column is a primary key    Consider running DBCC CHECKIDENT   lt newTableName gt    immediately after   switching  See msdn microsoft com en-us library ms176057 aspx for more   info    If the table is actively being extended with new rows  meaning you don t have much if any downtime between adding IDENTITY and adding new rows  then instead of DBCC CHECKIDENT you ll want to manually set the identity seed value in the new table schema to be larger than the largest existing ID in the table  e g  IDENTITY  2435457  1   You might be able to include both the ALTER TABLE   SWITCH and the DBCC CHECKIDENT in a transaction  or not-- haven t tested this  but seems like setting the seed value manually will be easier and safer    Obviously  if no new rows are being added to the table  or they re only added occasionally  like a daily ETL process  then this race condition won t happen so DBCC CHECKIDENT is fine

User · Answer

I m a java developer that happened to get on a team without a DBA and one where as a developer  I can t get DBA rights   I was tasked with moving an entire schema between two databases  so without having a DBA  I had to do it and do it by running scripts  not being able to use the GUI in SQL Server 2008 because I didn t have admin privileges   Everything was moved without issue  however  when running a stored procedure on the new schema table  I found I lost the identity field in a table   I double checked the script that created the table and it was there  however  SQL Server didn t get it when I ran the script   I was told later by a DBA that he had seen this same problem before   In any event  for SQL Server 2008  these are the steps I took to get this resolved and they worked  so I m posting this here in the hopes it will be a help to someone   This is what I did as I had FK dependencies on another table that made this more difficult   I used this query to verify the identity was indeed missing and to view dependencies on the table   1   Find statistics on a table   exec sp help  dbo table name old     2   Create a duplicate  identical new table  except add an identity field on the PK field where it had been before   3   Disable the identity to move data   SET IDENTITY INSERT dbo table name ON    4   Transfer the data   INSERT INTO dbo table name new   field1  field2  etc      SELECT  field1  field2  etc    FROM  dbo table name old    5   Verify the data is there   SELECT   FROM dbo table name new   6   Re-enable the identity   SET IDENTITY INSERT ToyRecP ToyAwards lkpFile New OFF   7   This is the best script I found to get all the FK relationships to verify which table s  the original table references as dependencies and I came across many  so it is a keeper   SELECT f name AS ForeignKey     OBJECT NAME f parent object id  AS TableName     COL NAME fc parent object id  fc parent column id  AS ColumnName     OBJECT NAME  f referenced object id  AS ReferenceTableName     COL NAME fc referenced object id  fc referenced column id  AS ReferenceColumnName FROM sys foreign keys AS f INNER JOIN sys foreign key columns AS fc    ON f OBJECT ID   fc constraint object id    ORDER BY ReferenceTableName    8   Make sure you have all the PK and FK scripts for all the tables involved  before this next step   9   You can right-click on each key and script this using SQL Server 2008  10   Drop the FK s  from the dependency table s  using this syntax   ALTER TABLE  dbo   table name  DROP CONSTRAINT  Name of FK    11   Drop the original table   DROP TABLE dbo table name old    13   These next steps rely on the scripts you created in SQL Server 2008 in step 9   --Add the PK to the new table   --Add the FK to the new table   --Add the FK s back to the dependency table   14   Verify everything is correct and complete   I used the GUI to look at the tables   15   Rename the new table to the original tables name   exec sp RENAME   Schema Name OldTableName       NewTableName      Finally  everything worked

User · Answer

By design there is no simple way to turn on or turn off the identity feature for an existing column   The only clean way to do this is to create a new column and make it an identity column or create a new table and migrate your data   If we use SQL Server Management Studio to get rid of the identity value on column  id   a new temporary table is created  the data is moved to the temporary table  the old table is dropped and the new table is renamed   Use Management Studio to make the change and then right click in the designer and select  Generate Change Script    You will see that this is what SQL server in doing in the background

User · Answer

I don t believe you can alter an existing column to be an identity column using tsql   However  you can do it through the Enterprise Manager design view   Alternatively you could create a new row as the identity column  drop the old column  then rename your new column   ALTER TABLE FooTable ADD BarColumn INT IDENTITY 1  1                 NOT NULL                PRIMARY KEY CLUSTERED

User · Answer

If the original poster was actually wanting to set an existing column to be a PRIMARY KEY for the table and actually did not need the column to be an IDENTITY column  two different things  then this can be done via t-SQL with   ALTER TABLE  YourTableName  ADD CONSTRAINT  ColumnToSetAsPrimaryKey  PRIMARY KEY   ColumnToSetAsPrimaryKey     Note the parenthesis around the column name after the PRIMARY KEY option   Although this post is old and I am making an assumption about the requestors need  I felt this additional information could be helpful to users encountering this thread as I believe the conversation could lead one to believe that an existing column can not be set to be a primary key without adding it as a new column first which would be incorrect

User · Answer

There isn t one  sadly  the IDENTITY property belongs to the table rather than the column   The easier way is to do it in the GUI  but if this isn t an option  you can go the long way around of copying the data  dropping the column  re-adding it with identity  and putting the data back   See here  for a blow-by-blow account

User · Answer

you can t do it like that  you need to add another column  drop the original column and rename the new column or or create a new table  copy the data in and drop the old table followed by renaming the new table to the old table  if you use SSMS and set the identity property to ON in the designer here is what SQL Server does behind the scenes  So if you have a table named  user  this is what happens if you make UserID and identity  BEGIN TRANSACTION SET QUOTED IDENTIFIER ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ARITHABORT ON SET NUMERIC ROUNDABORT OFF SET CONCAT NULL YIELDS NULL ON SET ANSI NULLS ON SET ANSI PADDING ON SET ANSI WARNINGS ON COMMIT BEGIN TRANSACTION  GO  GO CREATE TABLE dbo Tmp User           UserID int NOT NULL IDENTITY  1  1       LastName varchar 50  NOT NULL      FirstName varchar 50  NOT NULL      MiddleInitial char 1  NULL         ON  PRIMARY  GO  SET IDENTITY INSERT dbo Tmp User ON GO IF EXISTS SELECT   FROM dbo  User    EXEC  INSERT INTO dbo Tmp User  UserID  LastName  FirstName  MiddleInitial      SELECT UserID  LastName  FirstName  MiddleInitialFROM dbo  User  TABLOCKX   GO SET IDENTITY INSERT dbo Tmp User OFF GO  GO DROP TABLE dbo  User  GO EXECUTE sp rename N dbo Tmp User   N User    OBJECT  GO ALTER TABLE dbo  User  ADD CONSTRAINT     PK User PRIMARY KEY CLUSTERED            UserID       ON  PRIMARY   GO COMMIT   Having said that there is a way to hack the system table to accomplish it by setting the bitwise value but that is not supported and I wouldn t do it

User · Answer

You cannot alter a column to be an IDENTITY column  What you ll need to do is create a new column which is defined as an IDENTITY from the get-go  then drop the old column  and rename the new one to the old name   ALTER TABLE  yourTable  ADD NewColumn INT IDENTITY 1 1   ALTER TABLE  yourTable  DROP COLUMN OldColumnName  EXEC sp rename  yourTable NewColumn    OldColumnName    COLUMN    Marc

User · Answer

If you happen to be using Visual Studio 2017    In Server Object Explorer right-click on your table and select  view code   Add the modifier  IDENTITY  to your column Update   This will do it all for you

User · Answer

As per my current condition  I follow this approach  I want to give identity to a primary table after data inserted via script   As I want to append identity  so it always start from 1 to End of record count that I want    --first drop column and add with identity ALTER TABLE dbo tblProductPriceList drop column ID  ALTER TABLE dbo tblProductPriceList add ID INT IDENTITY 1 1   --then add primary key to that column  exist option you can ignore  IF  NOT EXISTS  SELECT   FROM sys key constraints  WHERE object id   OBJECT ID N  dbo   PK tblProductPriceList    AND parent object id   OBJECT ID N  dbo   tblProductPriceList         ALTER TABLE  tblProductPriceList  ADD PRIMARY KEY  id  GO   This will create the same primary key column with identity  I used this links   https   blog sqlauthority com 2014 10 11 sql-server-add-auto-incremental-identity-column-to-table-after-creating-table   Add primary key to existing table

User · Answer

There is cool solution described here  SQL SERVER     Add or Remove Identity Property on Column  In short edit manually your table in SQL Manager  switch the identity  DO NOT SAVE changes  just show the script which will be created for the changes  copy it and use it later   It is huge time saver  because it  the script  contains all the foreign keys  indices  etc  related to the table you change  Writting this manually    God forbid

User · Answer

As I understood in normal cases we are creating a table with Primary key which is having Identity property So Rename or Delete a column which is associated with Primary Key constraint will not be possible because constraint Rules are validating column structure  Tto achieve this we have to process some steps in the following way  Let us assume TableName    Employee  and ColumnName    EmployeeId    1   Add new column  EmployeeId new  in the  Employee  table   ALTER TABLE Employee ADD EmployeeId new INT IDENTITY 1 1    Now remove column  EmployeeId  from  Employee  table   ALTER TABLE Employee DROP COLUMN EmployeeId This will throw error because of Primary Key Constraint rules are applicable and validating column structure         Msg 5074  Level 16  State 1  Line 1 The object  PK dbo Employee  is dependent on colmn  EmployeeId       So we have to remove the Primary Key constraint first from the table  Employee  then we can remove the column   ALTER TABLE Employee DROP constraint  PK dbo Employee  Now we can remove the column  EmployeeId  from  Employee  table  as did in the previous step where we got error  ALTER TABLE Employee DROP COLUMN EmployeeId Now Column  EmployeeId  removed from table  So we will Rename the newly added new column  EmployeeId new  with  EmployeeId   sp rename  Employee EmployeeId    EmployeeId new    COLUMN  To rearrange the table in the same form as It was  we have to add Primary Key Constraint for the column  EmployeeId   ALTER TABLE Employee add constraint  PK dbo Employee  primary key   EmployeeId    8  Now the table  Employee  with  EmployeeId  is modified for Identity rules along with existing primary key constraint

[sql] Adding an identity to an existing column

Examples related to sql

Examples related to sql-server

Examples related to alter-table

Examples related to identity-column