SQL Server IDENTITY
The IDENTITY keyword is a property in SQL Server. When a table column is defined with an identity property, its value will be auto-generated incremental value. This value is created by the server automatically. Therefore, we can't manually enter a value into an identity column as a user. Hence, if we mark a column as identity, SQL Server will populate it in an auto-increment manner.
The following are the syntax to illustrate the use of IDENTITY property in SQL Server:
The above syntax parameters are explained below:
Let us understand this concept through a simple example.
Suppose we have a "Student" table, and we want StudentID to be generated automatically. We have a beginning student ID of 10 and want to increase it by 1 with each new ID. In this scenario, the following values must be defined.
NOTE: Only one identify column is allowed per table in SQL Server.
SQL Server IDENTITY Example
Let us understand how we can use the identity property in the table. The identity property in a column can be set either when the new table is created or after it has been created. Here we will see both cases with examples.
IDENTITY property with new table
The following statement will create a new table with the identity property into the specified database:
Next, we will insert a new row into this table with an OUTPUT clause to see the auto-generated person id:
Executing this query will display the below output:
This output shows that the first row has been inserted with the value ten in the PersonID column as specified in the table definition identity column.
Let's insert another row into the person table as below:
This query will return the following output:
This output shows that the second row has been inserted with the value 11 and the third row with the value 12 in the PersonID column.
IDENTITY property with existing table
We will explain this concept by first deleting the above table and create them without identity property. Execute the below statement to drop the table:
Next, we will create a table using the below query:
If we want to add a new column with the identity property in an existing table, we need to use the ALTER command. The below query will add the PersonID as an identity column in the person table:
Adding value into the identity column explicitly
If we add a new row into the above table by specifying identity column value explicitly, SQL Server will throw an error. See the below query:
Executing this query will through the following error:
To insert the identity column value explicitly, we need to first set the IDENTITY_INSERT value ON. Next, execute the insert operation to add a new row into the table and then set the IDENTITY_INSERT value OFF. See the below code script:
IDENTITY_INSERT ON lets users to put data into identity columns, while IDENTITY_INSERT OFF prevents them from adding value to this column.
Executing the code script will display the below output where we can see that the PersonID with value 14 is inserted successfully.
SQL Server provides some identity functions for working with the IDENTITY columns in a table. These identity functions are listed below:
Let's have a look at the IDENTITY functions with some examples.
The @@IDENTITY is a system-defined function that displays the last identity value (maximum used identity value) created in a table for the IDENTITY column in the same session. This function column returns the identity value generated by the statement after inserting a new entry in a table. It returns a NULL value when we execute a query that does not create IDENTITY values. It always works under the scope of the current session. It cannot be used remotely.
Suppose we have the current maximum identity value in the person table is 13. Now we will add one record in the same session that increments identity value by one. Then we will use the @@IDENTITY function to get the last identity value created in the same session.
Here is the full code script:
Executing the script will return the following output where we can see the maximum used identity value is 14.
The SCOPE_IDENTITY() is a system-defined function to display the most recent identity value in a table under the current scope. This scope can be a module, trigger, function, or a stored procedure. It is similar to the @@IDENTITY() function, except this function only has a limited scope. The SCOPE_IDENTITY function returns NULL if we execute it before the insert operation that generates a value in the same scope.
The below code uses both the @@IDENTITY and SCOPE_IDENTITY() function in the same session. This example will first display the last identity value, then insert one row into the table. Next, it executes both identity functions.
Executing the code will display the same value in the current session and similar scope. See the below output image:
Now we will see how both functions are different with an example. First, we will create two tables named employee_data and department using the below statement:
Next, we create an INSERT trigger on the employee_data table. This trigger is invoked to insert a row in the department table whenever we insert a row in the employee_data table.
The below query creates a trigger for inserting a default value 'IT' in the department table on each insert query in the employee_data table:
After creating a trigger, we are going to insert one record into the employee_data table and see the output of both @@IDENTITY and SCOPE_IDENTITY() functions.
Executing the query will add one row into the employee_data table and generates an identity value in the same session. Once the insert query is executed in the employee_data table, it automatically calls a trigger to add one row in the department table. The identity seed value is 1 for the employee_data and 100 for the department table.
Finally, we execute the below statements that display the output 100 for the SELECT @@IDENTITY function and 1 for the SCOPE_IDENTITY function because they return identity value in the same scope only.
Here is the result:
The IDENT_CURRENT is a system-defined function to display the most recent IDENTITY value generated for a given table under any connection. This function does not consider the scope of the SQL query that creates the identity value. This function requires the table name for which we want to get the identity value.
We can understand it by first opening the two connection windows. We will insert one record in the first window that generates the identity value 15 in the person table. Next, we can verify this identity value in another connection window where we can see the same output. Here is the full code:
Executing the above codes in two different windows will display the same identity value.
The IDENTITY() function is a system-defined function used for inserting an identity column into a new table. This function is different from the IDENTITY property that we use with the CREATE TABLE and ALTER TABLE statements. We can use this function only in a SELECT INTO statement, which is used while transferring data from one table to another.
The following syntax illustrates the use of this function in SQL Server:
If a source table has an IDENTITY column, the table formed with a SELECT INTO command inherits it by default. For example, we have previously created a table person with an identity column. Suppose we create a new table that inherits the person table using the SELECT INTO statements with the IDENTITY() function. In that case, we will get an error because the source table already has an identity column. See the below query:
Executing the above statement will return the following error message:
Let's create a new table without identity property using the below statement:
Then, copy this table using the SELECT INTO statement including IDENTITY function as follows:
Once the statement executes, we can verify it using the sp_help command that displays table properties.
You can see the IDENTITY column in the TEMPTABLE properties as per the specified conditions.
If we use this function with the SELECT statement, SQL Server will through the following error message:
Msg 177, Level 15, State 1, Line 2 The IDENTITY function can only be used when the SELECT statement has an INTO clause.
Reusing IDENTITY values
We cannot reuse the identity values in the SQL Server table. When we delete any row from the identity column table, a gap will be created in the identity column. Also, SQL Server will create a gap when we insert a new row into the identity column, and the statement is failed or rolled back. The gap indicates that the identity values are lost and cannot be generated again into the IDENTITY column.
Consider the below example to understand it practically. We already have a person table containing the following data:
Next, we will create two more table named 'position', and 'person_position' using the following statement:
Next, we try to insert a new record into the person table and assign them a position by adding a new row into the person_position table. We will do this by using the transaction statement as below:
The above transaction code script executes the first insert statement successfully. But the second statement failed since there was no position with id ten in the position table. Hence, the entire transaction was rolled back.
Since we have maximum identity value in the PersonID column is 16, the first insert statement consumed the identity value 17, and then the transaction was rolled back. Therefore, if we insert the next row in the Person table, the next identity value will be 18. Execute the below statement:
After checking the person table again, we see that the newly added record contains identity value 18.
Two IDENTITY columns in a single table
Technically, it is not possible to create two identity columns in a single table. If we do this, SQL Server throws an error. See the following query:
When we execute this code, we will see the following error:
However, we can create two identity columns in a single table by using the computed column. The following query creates a table with a computed column that uses the original identity column and decreases it by 1.
Next, we will add some data into this table using the below command:
Finally, we check the table data using the SELECT statement. It returns the following output:
We can see in the image how the SecondID column acts as a second identity column, decreasing by ten from the starting value of 9990.
SQL Server's IDENTITY column misconceptions
The DBA user has many misconceptions regarding SQL Server identity columns. The following are the list of most common misconceptions concerning identity columns that would be seen:
IDENTITY column is UNIQUE: According to SQL Server's official documentation, the identity property cannot guarantee that the column value is unique. We must use a PRIMARY KEY, UNIQUE constraint, or UNIQUE index to enforce column uniqueness.
IDENTITY column generates consecutive numbers: Official documentation clearly states that the assigned values in the identity column can be lost upon a database failure or server restart. It can cause gaps in the identity value during insertion. The gap can also be created when we delete the value from the table, or the insert statement is rolled back. The values that generate gaps cannot be used further.
IDENTITY column can't auto-generate existing values: It is not possible for the identity column to auto-generate existing values until the identity property is reseeded by using the DBCC CHECKIDENT command. It allows us to adjust the seed value (starting value of the row) of the identity property. After performing this command, SQL Server will not check the newly created values already present in the table or not.
IDENTITY column as a PRIMARY KEY is enough to identify the row: If a primary key contains the identity column in the table without any other unique constraints, the column can store duplicate values and prevent column uniqueness. As we know, the primary key cannot store duplicate values, but the identity column can store duplicates; it is recommended not to use the primary key and identity property on the same column.
Using the wrong tool to get identity values back after an insert: It is also a common misconception about unawareness of the differences between the @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT, and IDENTITY() functions to get the identity value directly inserted from the statement we have just executed.
Difference between SEQUENCE and IDENTITY
We use both SEQUENCE and IDENTITY for generating auto numbers. However, it has some differences, and the main difference is that identity is table-dependent, whereas sequence is not. Let us summarise their differences into the tabular form:
This article will give a complete overview of IDENTITY property in SQL Server. Here we have learned how and when identity property is used, its different functions, misconceptions, and how it is different from the sequence.