set identity insert on

• Sailor

  SSCertifiable

  Points: 5246

  More actions

  January 18, 2013 at 10:02 am

  #393967

  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?

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  January 18, 2013 at 10:09 am

  #1578795

  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/

• Vedran Kesegic

  SSCrazy Eights

  Points: 9302

  More actions

  January 19, 2013 at 7:44 pm

  #1579002

  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
  

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  January 21, 2013 at 2:24 pm

  #1579383

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".