set identity insert on

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » set identity insert on

set identity insert on

Rate Topic

Display Mode

Topic Options

Author

Message

Sailor

Posted Friday, January 18, 2013 10:02 AM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:18 PM
Points: 236, Visits: 748

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

Sean Lange

Posted Friday, January 18, 2013 10:09 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1409002

Vedran Kesegic

Posted Saturday, January 19, 2013 7:44 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 343, Visits: 1,077

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

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1409247

ScottPletcher

Posted Monday, January 21, 2013 2:24 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1409714

« Prev Topic | Next Topic »

Permissions