reserve_identity

Description

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.

Syntax

reserve_identity (table_name, number_of_values)

Parameters

table_name

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).

number_of_values

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.

Examples

Example 1

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.

Usage

Permissions

You must have insert permission to reserve identity values.