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
Author
Message
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: Wednesday, October 8, 2014 6:20 AM
Points: 247, Visits: 800
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 13,078, Visits: 12,528
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
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: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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
Post #1409247
Posted Monday, January 21, 2013 2:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 2,192, Visits: 3,299
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