reserve_identity allows a process to reserve a block of identity values for use by that process.
After a process calls reserve_identity to reserve the block of values, subsequent identity values needed by this process are drawn from this reserved pool. When these reserved numbers are exhausted, or if you insert data into a different table, the existing identity options apply. reserve_identity can retain more than one block of identity values, so if inserts to different tables are interleaved by a single process, the next value in a table’s reserved block is used.
Reserves a specified size block of identity values for the specified table, which are used exclusively by the calling process. Returns the reserved starting number, and subsequent inserts into the specified table by this process use these values. When the process terminates, any unused values are eliminated.
reserve_identity (table_name, number_of_values)
is the name of the table for which the reservation are made. The name can be fully qualified; that is, it can include the database_name, owner_name, and object_name (in quotes).
is the number of sequential identity values reserved for this process. This must be a positive value that will not cause any of the reserved values to exceed the maximum values for the datatype of the identity column.
Describes a typical usage scenario for reserve_identity, and assumes that table1 includes col1 (with a datatype of int) and a col2 (an identity column with a datatype of int). This process is for spid 3:
select reserve_identity("table1", 5 )
--------------- 10
Insert values for spids 3 and 4:
Insert table1 values(56) -> spid 3 Insert table1 values(48) -> spid 3 Insert table1 values(96) -> spid 3 Insert table1 values(02) -> spid 4 Insert table1 values(84) -> spid 3
Select from table table1:
select * from table1
Col1 col2 -------- ----- 3 1-> spid 3 reserved 1-5 3 2-> spid 3 3 3-> spid 3 4 6<= spid 4 gets next unreserved value 3 4<= spid 3 continues with reservation
The result set shows that spid 3 reservered identity values 1 – 5, spid 4 receives the next unreserved value, and then spid 3 reserves the subsequent identity values.
The sp_configure system procedure’s “identity reservation size” parameter specifies a server-wide limit on the value passed to the number_of_values parameter.
The return value, start_value, is the starting value for the block of reserved identity values. The calling process uses this value for the next insert into the specified table
reserve_identity allows a process to:
Reserve identity values without issuing an insert statement.
Know the values reserved prior issuing the insert statement
“Grab” different size blocks of identity values, according to need.
Better control “over gaps” by reserving only what is needed (that is, they are not restricted by preset server grab size
Values are automatically used with no change to the insert syntax.
NULL values are returned if:
A negative value or zero is specified as the block size.
The table does not exist.
The table does not contain an identity column.
If you issue reserve_identity on a table in which this process has already reserved these identity values, the function succeeds and the most recent group of values is used.
You cannot use reserve_identity to reserve identity values on a proxy table. Local servers can use reserve_identity on a remote table if the local server calls a remote procedure that calls reserve_identity. Because these reserved values are stored on the remote server but in the session belonging to the local server, subsequent inserts to the remote table use the reserved values.
If the identity_gap is less than the reserved block size, the reservation succeeds by reserving the specified block size (not an identity_gap size) of values. If these values are not used by the process, this results in potential gaps of up to the specified block size regardless of the identity_gap setting.
You must have insert permission on the table to reserve identity values. Permission checks do not differ based on the granular permissions settings.
