SET IDENTITY_INSERT

  • Article

ON/OFF

 

This command is from SQL Server. This command is to enable the users to set their own value for IDENTITY Column in case they want to. This has been the very useful for us. We use it in MERGE and RDA when we send the client side inserts to server where we want to make sure to insert the row on server with the IDENTITY value generated by client.

 

Today in SQL Server CE (SSCE), we have no way to set our own value for IDENTITY column. Now there is a strong need for this feature in SQL Server Compact Edition. Here are some more details:

SQL Server CE 2.0 supported setting the IDENTITY Column value and the apps migrated to SQL Mobile 3.0 have severely broken on this front. Ex: Data Port Wizard from prime works

 

What has been given as a work around to customer right now is given below:

We can change the seed and step by “ALTER TABLE ALTER COLUMN”. So, before doing an INSERT into SQL Server Compact database, one has to execute this “ALTER TABLE ALTER COLUMN” command to set the seed such a way that the next INSERT-ed row gets the value required. But this is really a pain, because:

- ALTER TABLE ALTER COLUMN is a DDL. Hence, the cursor needs to be closed and opened again.

- ALTER TABLE ALTER COLUMN is required for every INSERT. Which means, an INSERT becomes:

a) Open Cursor b) ALTER TABLE c) Close Cursor d) Open Cursor e) INSERT Row f) Close Cursor

 

It is much like another session variable similar to “SET SHOWPLAN_XML ON”. We skip identity checks, when this session variable for a table is ON.

 

USAGE-HOW-TO:

CREATE TABLE testIdentity (id int IDENTITY (1,1), name nvarchar(100));

INSERT INTO testIdentity(name) VALUES ('name1'); è Row will be (1, name1)

INSERT INTO testIdentity(name) VALUES ('name2'); è Row will be (2, name2)

SET IDENTITY_INSERT testIdentity ON;

INSERT INTO testIdentity (id, name) VALUES (10, 'name10'); è Row will be (10, name10)

INSERT INTO testIdentity (id, name) VALUES (20, 'name20'); è Row will be (20, name20)

SET IDENTITY_INSERT testIdentity OFF;

INSERT INTO testIdentity(name) VALUES ('name??'); è Row will be (3, name??)

 

Here you can observe that SQL Server automatically uses 21 for the last row. That is, SQL Server just leaves holes. However, we chose to not leave holes. Hence, in SQL Server Compact the last row would get value ‘3’ for IDENTITY column. This is basically for the reason that, when we are a merge replicated database we have a certain slot allocated to a subscriber and this range/slot is better managed by merge. If we chose to behave as SQL Server here, IDENTITY_INSERT updates the AUTOINC_NEXT which MAY fall out of our allocated range which would pose a serious issue. However, user can always get SQL Server behavior by “ALTER TABLE <Table Name> ALTER COLUMN <Column Name> <Datatype> IDENTITY(<New Seed>, <New Step>)”.

 

Thanks,

Laxmi Narsimha Rao ORUGANTI