reserve_identity

reserve_identity allows a process to reserve a block of identity values for use by that process.

Syntax

reserve_identity (table_name, number_of_values)

Parameters

Examples

Usage

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

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

See also sp_configure in Reference Manual: Procedures.

Permissions

You must have insert permission on the table to reserve identity values. Permission checks do not differ based on the granular permissions settings.