set identity insert on

Question

Post reply

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?

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 · Answer 2

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: 101134 More actions · Answer 3

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".