SQL Server Identity Functions

Introduction:

In SQL Server IDENTITY column is a special type of column that used to generate out-increment key values based on a specified SEED and INCREMENT. SQL Server gives us a number of functions that work with the IDENTITY column.

Today’s agenda:
  • IDENT_CURRENT(‘Table_Name’)
  • @@IDENTITY PROPERTY
  • SCPOPE_IDENTITY()
  • SQL SERVER IDENTITY FUNCTION
  • SQL SERVER OUTPUT CLAUSE

Before going further explanation, let me tell you something, every IDENTITY column must have SEED and INCREMENT. SEED is nothing but the start point of identity and increment means how much to be increased each time when next record will be inserted. For example IDENTITY (1, 1), it means that seed is one and increment is one or IDENTITY (10, 2), in this case seed is ten and increment is two. Note that if the IDENTITY column does not set explicitly by default it will be IDENTITY (1, 1) implicitly.

SQL Server IDENT_CURRENT (‘Table_Name’) Function

The IDENT_CURRENT is a system function returns the last identity value produced by a specific table under any connection, regardless of the lifetime and scope of the T-SQL statement that generated that valued. In other words, the IDENT_CURRENT function is limited only to a specific table, without affected by the connection lifetime.

SQL Server @@IDENTITY PROPERTY

The @@IDENTITY is a system function that returns the last identity value generated for any table with an identity column under the current session, regardless of the scope of the T-SQL statement that generated the value. In other words, if you have multiple rows recorded using different INSERT OR SELECT INTO operations, the @@IDENTITY function will return the last IDENTITY value generated at the end of the last operation. Keen on mind that if these operations do not affect any table with and identity column, then @@IDENTITY function will return a NULL value. In addition, the identity property allows you to specify a counter of values for a specific column of a table. Columns with numeric data types, such as TINYINT, SMALLINT, INT, and BIGINT, can have this property. The Database Engine generates values for such columns sequentially, starting with an initial value.

SQL Server SCPOPE_IDENTITY () Function

The SCOPE_IDENTITY is a system function that returns last identity value that generated for any table with IDENTITY column under the current connection, explicitly by the statement executing in the current scope or lifetime. This scope may be a store procedure, a trigger, a function or a SQL queries. Note that the SCOPE_IDENTITY function works same as @@IDENTIY function, but closed under a specific scope. Keen on mind that if no INSERT statement operations performed on the table with an identity column before calling the SCOPE_IDENTITY function, then SCOPE_IDENTITY function will return a NULL value.

As a conclusion for what we learned previously, both the @@IDENTITY and SCOPE_IDENTITY functions work same manner and will return the LAST IDENTITY values generated in any table with IDNETITY COLUMN in the current session. Nevertheless, the SCOPE_IDENTITY function will return the values that inserted in the current scope only, while the @@IDENTITY function is not limited to a specific scope. In the case of IDENTI_CURRENT function, you people should specify the tablet that you needed to check for its last identity value.

Examples on SQL Server IDENTITY

Let us check the differences between these functions. Here we will create two new tables with IDENTITY columns, the SEED of the first table is 1 and the second table is 10. Both tables have same increment equal to 1

We can create tables using following T-SQL scrip:

The tables are created and now we can check the current identity value for the two tables using the IDENTITY_CURRENT function providing table name as a parameter as shown below:

The result will show us the current identity value for each table is equal to the provided SEED value, as no records have been inserted yet:

Now we will insert five records to the FirstTest table and three records to the SecondTest table using the INSERT statement shown below:

Then let’s check the current identity values again after insertion process using IDENTITY_CURRENT function:

Fortunately, as we expecting the identity SEED for the FirstTest table will be increased by five and the SecondTest table by three. The EDENTITY_CURRENT function result will show us the current identity for each table as shown below:

If we check the result of the @@IDENTITY function after the insertion process using following T-SQL script:

@@IDENTITY function will return the last identity value generated from the two insert T-SQL statements, which is equal to 12 from the SecondTest table as shown below:

The result of the SCOPE_IDENTITY function will be similar to the @@IDENTITY function result as the identity generation performed only under the current scope, as shown below:

Now if we try to insert one more record to the FirstTest table:

Checking the current identity value for both tables using the IDENT_CURRENT function:

The identity value for the FirstTest table will be increased one and the identity value for the SecondTest table will not affect, as shown below:

Checking the @@IDENTITY function using following script:         

The result will show the last value generated in the current connection, as shown below:

As the insert statement done under the current scope, the result of SCOPE_IDENTITY function will return similar result as @@IDENTITY function using following script:

The result will be 6 for the SCOPE_IDENTITY function as shown below:

As we mention earlier, that triggers will affect operations of SCOPE_IDENTITY function, assume that we have created below trigger that inserts a new record to the SecondTest table each time we performed an insert operation to the FirstTest table, as shown following script:

Then if we try to insert a record to the FirstTest table, the output message will show us that there are two records inserted by this INSERT statement. The first is to table FirstTest and the second is to table SecondTest from the trigger. Following script is doing this job for us, as shown below:

Let us check the current identity value for both tables by using the IDENT_CURRENT function as following:

The result shows us that the identity value for both tables increased by one after the insertion process, as shown here:

On the other hand, comparing the result of @@IDENTITY and SCOPE_IDENTITY functions using following T-SQL statements, it will be:

You people will see that the @@IDENTITY function returns the last generated identity value, without caring the scope , it means that it returns the identity value generated by the insert statement performed by the trigger in which inserts the record to  the SecondTest table. However, the SCOPE_IDENTITY function returns the last identity value generated in the current scope only, which is the value inserted to the FirstTest table using INSERT statement as shown below:

Lastly, if you people check the @@IDENTITY and SCOPE_INDENTITY functions results in an empty query window without performing any other operation, the result return from both functions will be NULL as we mention earlier of our discussion. Because of no change is done in the session on any table with an IDENTITY column.

Now open a new Query Window in SQL Server and type following script by executing it.

It shows us the following output, which is NULL value because no change is performed for both tables as shown below:

SQL Server INDENITY Function

This is nothing but another function that works with identity value. It is different from the IDENTITY property that is used in the CREATE OR TALTER table statements. IDENTITY function is used to insert identity values into a new table using the SELECT…INTO T-SQL statement.

It is must to provide a valid and suitable format data type for the identity column from integer data type categories (bit and float data types are not allowed). You people be aware that you have to provide SEED and INCREMENT values for the IDENTITY function on the identity column and finally the name of the column that will be inserted to the new table.

Suppose that we want to create a new table that contains an identity column and the F_Name from the FirstTest table created previously. The following SELECT…INTO T-SQL statement, we used to create new table (NewIdenTable) with desired data, as shown below:

Using SELECT T-SQL statement the result of above will be:

The output from the IDENT_CURRENT,@@IDENTITY and SCOPE_IDENTITY functions will be the same for the NewIdenTable table, as all identity values returned 7 under the current scope.

After executing this above T-SQL statements, result will be shown below:

SQL Server Output Clause

The OUTPUT CLAUSE is nothing but is a function that generates information about the rows affected by DML statements or MERGE T-SQL commands. You people can easily benefit from the captured result by inserting it into another temporary table or table variable to use the data later in your SQL statement. The OUTPUT CLAUSE is a good alternative for SCOPE_IDENTITY function, as it can be used to return the new identity value that is inserted into a specific table.

To test OUTPUT CLAUSE, you people need first to TRUNCATE FirstTest table, which we created previously. Then we create now a TMP table to keep the inserted identity values and insert three new rows into that table. Now the INSERT statement will contain a new clause, OUTPUT … INTO, that is used to write the T_ID identity column values that are inserted into the created TMP table. The T-SQL script is shown below:

Creating #TMP table:

By calling again TRUNCATE command for FirstTest table will delete all records and resets the identity seed value. It insert three name values into FirstTest table with identity column value from #TMP table. Keep on mind that last identity value with in this query scope will be 3 as shown below:

Finally, thank you guys for your time and make sure you like and share your friends to get next upcoming articles. Be in touch and see you soon.

Follow us:

Leave a Reply

Your email address will not be published. Required fields are marked *