Applying Identity auto increment constraint

Posted on 2012-08-24
Last Modified: 2012-09-10
Hi All,

i have couple of tables in my database that does not have identity auto incerement constraint defined on its primary key column. but these tables has lot data init.

I would like to apply this constraint to its primary ket column but at the same time i dot not want to loss the existing keys (values).

Please suggest me the best approach to acheive this.
Question by:dumpalpr
    LVL 67

    Accepted Solution

    According to SQL ServerCentral (, there is no other way than to copy the table into a new one, having the column defined as identity and identity_insert on that table switched on. That is what Enterprise Manager / Management Studio do, and there seems to be no alternative.

    It might be better (performance wise) to:
    * add a new (identity) column,
    * set identity_insert on,
    * update the identity column with the primary key
    * drop the PK constraint
    * drop the PK column
    * rename the identity column
    * set the PK
    if the table is very big. I can't tell whether the copy or the latter is faster, but both require you to have the table offline for a while.
    LVL 9

    Expert Comment

    You can't add identity for existing column with data, first you need to create new table with same set of columns with identity column and the identity seed set as max value + 1 of your existing data and then switch off the identity and import the data using DTS package. after import switch on the identity.
    LVL 9

    Expert Comment

    you must need to add a new column and set it as identity column..

    if you dont want to loose data of your existing column....

    anither way of doing this is...

    if you dont want to add new can go for this..

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now