Just Learned - KEEPIDENTITY in SQL SERVER

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

KEEPIDENTITY in SQL SERVER

Oct 7 2011 5:11AM by Robert Dennyson

KEEPIDENTITY applicable only in an INSERT statement when the BULK option is used with OPENROWSET. Specifies that the identity value or values in the imported data file are to be used for the identity column. If KEEPIDENTITY is not specified, the identity values for this column are verified but not imported and the query optimizer automatically assigns unique values based on the seed and increment values specified during table creation.

Here is the Example

INSERT INTO HumanResources.myDepartment
   with (KEEPIDENTITY)
   (DepartmentID, Name, GroupName, ModifiedDate)
   SELECT *
      FROM  OPENROWSET(BULK 'C:\myDepartment-n.Dat',
      FORMATFILE='C:\myDepartment-f-n-x.Xml') as t1;

6 Comments

This make me think of SET IDENTITY_INSERT (http://msdn.microsoft.com/fr-fr/library/ms188059.aspx).

commented on Oct 7 2011 5:22AM

Sergejack

41 · 4% · 1393

Yep, So close to it.

commented on Oct 7 2011 5:39AM

Robert Dennyson

11 · 14% · 4419

Yes, this can be done with SET IDENTITY_INSERT TABLENAME ON and OFF settings also. But this is new to known. Thanks.

commented on Oct 7 2011 5:39AM

Paresh Prajapati

6 · 22% · 7054

All: SET IDENTITY_INSERT ON is dangerous, and is not the same thing as KEEPIDENTITY. Here's why:

Per Books-On-Line (http://msdn.microsoft.com/en-us/library/ms188059.aspx): "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."

Please be careful - no ISV will provide two alternate methods to do the same thing, with the same results & side-effects. In this case, KEEPIDENTITY and SET IDENTITY_INSERT ON appear to be the same thing, but are not.

commented on Oct 8 2011 8:56AM

Nakul Vachhrajani

4 · 33% · 10575

I don't get why that makes it dangerous, Nakul. Plus, this behavior is to be expected.

commented on Oct 10 2011 2:15AM

Sergejack

41 · 4% · 1393

For one, if SET IDENTITY_INSERT is left ON, we would end up in problems. I have seen this blowing up in the face a couple of times when performed by relatively inexperienced developers, or when they are in a hurry.

I guess the main argument is to modify a table's properties only for the required duration. During BULK operations, if the KEEPIDENTITY is used, the setting takes effect only for the duration of the query, and no more.

commented on Oct 10 2011 11:22AM

Nakul Vachhrajani

4 · 33% · 10575

Your Comment

Sign Up or Login to post a comment.

"KEEPIDENTITY in SQL SERVER" rated 5 out of 5 by 19 readers