Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
IDENTITY- and GUID-Related Functions
The @@IDENTITY, IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT(), and NEWID() functions deal with IDENTITIY values or globally unique identifiers.
The NEWID() function must be used if you want to provide a default value for a column with the UNIQUEIDENTIFIER data type. It returns a new GUID each time it is executed.
The IDENTITY function has a limited use; in rare cases, when you use SELECT…INTO syntax you can supply identity values for the newly created table using the IDENTITY function. For instance, suppose we want to add an identity column to the sales table (within a temporary table). We could use the following statement to copy all rows from sales table into #new_sales and add an identity column, all in one shot:
SELECT IDENTITY(INT, 1,1) AS sales_key, * INTO #new_sales FROM sales
The other three IDENTITY-related functions deserve more attention. You will often need to populate multiple related tables, perhaps within a single transaction. For instance, you could be populating the order and order_details tables in one transaction. If the order table has an identity column, you'll have to look up the identity value just inserted into the order table before you can add a related record in order_details. The @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() functions help you look up the last identity value inserted, but their behavior is slightly different from each other, as follows:
@@IDENTITY returns the last IDENTITY value inserted on the current connection. Suppose that you have an INSERT trigger on the order table that populates the audit_trail table, which also has an IDENTITY column. In such a case, the @@IDENTITY function will return the last identity value inserted, which would be the identity inserted in the audit_trail table instead of the identity value added to the order table. Therefore, if you try populating order_details with a value returned by the @@IDENTITY function, your data integrity will be compromised.
The IDENT_CURRENT() function accepts a table name as the parameter and returns the last identity value generated in that table by any connection. If you're trying to populate order_details with the last identity value inserted into the order table, you might think that IDENT_CURRENT('order') would suffice. Well, it would if you were the only user of the system, but it is possible that some other user might add a row to the order table a few milliseconds after you added the row to the same table. Therefore, using IDENT_CURRENT() in a multiuser system might also compromise your data integrity.
The SCOPE_IDENTITY() function takes no parameters and returns the last identity value inserted within the current scope. What this means is that if an INSERT statement populating the order table fires a trigger and adds a row to the audit_trail table, SCOPE_IDENTITY() will return the last value added to the order table, whereas @@IDENTITY will return the last value added to audit_trail. These functions have appropriate uses, but they're often used incorrectly.