Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

set identity insert on Expand / Collapse
Posted Friday, January 18, 2013 10:02 AM

SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 23, 2015 1:08 PM
Points: 247, Visits: 829
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?

Post #1408997
Posted Friday, January 18, 2013 10:09 AM



Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 14,377, Visits: 14,123
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'


insert IdTest(MyId, SomeValue)
select 2, 'New Value'


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.


Need help? Help us help you.

Read the article at for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1409002
Posted Saturday, January 19, 2013 7:44 PM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:47 PM
Points: 369, Visits: 1,222
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.

Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers - Transaction log myths
Post #1409247
Posted Monday, January 21, 2013 2:24 PM


Group: General Forum Members
Last Login: Yesterday @ 4:40 PM
Points: 2,957, Visits: 4,513
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1409714
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse