set identity insert on

  • I'm doing unicode conversion. I am recreating a mirror image of some 100 databases and all their objects. Before I create the objects, I am changing from varchar to nchar etc. Then I do the copy from the old to new unicode database. If I do an insert with identity insert on and then turn it off, do I need to do a reseed the identities?

  • No. This is incredibly easy to evaluate. Create a table and try it out.

    create table IdTest

    (

    MyId int identity not null,

    SomeValue varchar(10)

    )

    insert IdTest

    select 'Row 1'

    SET IDENTITY_INSERT IdTest on

    insert IdTest(MyId, SomeValue)

    select 2, 'New Value'

    SET IDENTITY_INSERT IdTest off

    insert IdTest

    select 'Final Test'

    Select * from IdTest

    drop table IdTest

    Now unless your identity column is a primary key or has a unique index there is nothing to prevent duplicates.

    You can read about the Set IDENTITY_INSERT on BOL. http://msdn.microsoft.com/en-us/library/ms188059.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • How do you copy the data?

    With BCP OUT you can dump all tables into files (use -N option to convert all textual fields to unicode while keeping other fields native), and then bulk-load them with BCP IN.

    Speed i get on my laptop is about 200 000 rows per second for both directions.

    BCP also has -E option to preserve identity value: kind of automatic set identity insert on ... off for you.

    HTH,

    Vedran

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • No, you should not have to; the db engine takes care of that itself.

    From Books Online:

    "

    If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

    "

    So, SQL Server auto-adjusts the identity value up as you add higher-valued rows.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply