Connection Reference
Database parameters and supported database interfaces

Description

Specifies the syntax the SNC database interface uses to obtain the identity value of a new row in a database table.

When a DataWindow update request inserts a new row into a Microsoft SQL Server table containing an IDENTITY column, the DataWindow engine calls the SNC interface to obtain the identity value of the newly inserted row.

This parameter allows you to define how this request is implemented.

Applies to

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

ADO.NET for SQL Server

Syntax

Identity=value

Default value

Identity='@@IDENTITY'

Usage

Identity is a dynamic database parameter, it can be set and reset at any time during an application.

By default, the SNC interface issues SELECT @@identity to obtain the IDENTITY column value of the newly inserted row. It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

SELECT IDENT_CURRENT('tablename') returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

SELECT SCOPE_IDENTITY() returns the last IDENTITY value produced on a table and by a statement in the same scope, regardless of the table that produced the value.

When the DisableBind parameter is set to 0 (enabled), selecting SCOPE_IDENTITY() can cause the SQL request to be sent to the database as a nested sp_executesql statement, which creates a new scope. This new scope can lead to null values being returned by SCOPE_IDENTITY() because the identity value is tied to the scope in which the insert was made. In this case, it is recommended to use either IDENT_CURRENT or @@IDENTITY.

Examples

To specify the syntax for obtaining the identity value of a newly inserted row: