SQL Server Performance

Adding Identity column to existing table

Discussion in 'SQL Server 2005 General DBA Questions' started by ruancra, Jan 20, 2009.

  1. ruancra New Member

    Hi guys
    Is it possible to add a identity column to an existing table and to let it increment by 1when new transactions are inserted?
    Thank you
  2. FrankKalis Moderator

    No easy way for that. You can not simply add a new column with the identity property and have it started only with new rows.
    A workaround could be to create a copy of the table with the new structure, do an IDENTITY_INSERT with some default value for the identity column, drop the original table and rename the copy table. You know, something along these lines:
    DROP TABLE dbo.t;

    CREATE TABLE dbo.t (c1 int);
    CREATE TABLE dbo.t2 (i1 int IDENTITY(0, 1), c1 int);

    INSERT INTO dbo.t(c1) SELECT 2 UNION ALL SELECT 3;
    SELECT * FROM dbo.t;
    GO
    SET IDENTITY_INSERT dbo.t2 ON;
    INSERT INTO dbo.t2(i1, c1) SELECT 0, c1 FROM dbo.t;
    SELECT * FROM dbo.t2;
    SET IDENTITY_INSERT dbo.t2 OFF;

    DROP TABLE dbo.t;
    EXEC sp_rename 'dbo.t2', 't'

    INSERT INTO dbo.t SELECT 4
    SELECT * FROM dbo.t
  3. Adriaan New Member

    To add an identity column to an existing table is not difficult in itself:
    alter table dbo.mytable
    add myidentitycolumn int identity(0,1)
    go
    You should also add a unique constraint, unless you're already making this new column the PK.
    The problem that Frank is hinting at is when the identity values must be in ascending order corresponding to the data on an existing column. This is when you need to use Frank's logic.
    However, the whole idea behind identity is that the column doesn't carry any specific meaning, so its values are irrelevant (besides that they are unique). The data on the existing column will still have its order, wouldn't it?
    When you add new rows, the maximum value + 1 will be inserted on the identity column. However, when an insert is cancelled, please note that the maximum + 1 value is considered to already have been used, so the next insert will use the first "unused" value. This can cause gaps in the series of identity values. Again, this is why you should disregard the value on an identity column.
    If you need an auto-numbering system, you will need to implement other logic.
  4. moh_hassan20 New Member

    ... add that hint:
    Avoid using that field as a clustered index , or PK for performance issues, especially for heavy insertion table.
    sure you can use unclustered index

Share This Page