In the previous posts we have examined IDENTITY columns in detail. We discussed several IDENTITY related functions, commands, system variables and terms. This post intends to summarize them.
IDENTITY Property
IDENTITY is an attribute that you can assign to an integer column in SQL Server. It is NOT a DATA TYPE as many people think. It is just an attribute that instructs SQL Server to generate a new value every time a row is inserted. IDENTITY property is different from IDENTITY() function and this post explains them both.
SEED value
Every IDENTITY column has a SEED value/attribute. The IDENTITY value starts with the SEED value. If you create an IDENTITY column with a SEED value of 1000, the first IDENTITY value generated will be 1000. The default value of an IDENTITY SEED is 1. If you have not specified a SEED value, SQL Server will assume 1 as the SEED value and the first IDENTITY value generated will be 1.
INCREMENT value
Every IDENTITY column has an INCREMENT value/attribute. The INCREMENT value is used to generate the next IDENTITY value. New IDENTITY value is generated by adding INCREMENT to the PREVIOUS IDENTITY value. The default value of INCREMENT is 1, but you can change it. To generate ODD numbers (1, 3, 5 etc), set SEED to 1 and INCREMENT to 2. To generate EVEN numbers (0, 2, 4 etc), set SEED to 0 and INCREMENT to 2.
@@IDENTITY System Variable
@@IDENTITY is a system variable that holds the last IDENTITY value generated in the current session for any table. Note that it does not necessarily give you the IDENTITY value generated by the INSERT statement you just fired. If you try to insert a row into TableA and if the table has a trigger that performs an insert into TableB, @@IDENTITY will return the value generated for TableB; not TableA. This is explained in this post.
SCOPE_IDENTITY() Function
SCOPE_IDENTITY() returns the last IDENTITY value generated in the current scope. Let us revisit the example I wrote earlier. If you try to insert a row into TableA and if the table has a trigger that performs an inserts into TableB, SCOPE_IDENTITY() will return the IDENTITY value generated for Table A. See this post for more information.
IDENT_CURRENT() Function
IDENT_CURRENT() returns the last generated IDENTITY value of a given table. You can use this function to find the current IDENTITY value of tables. See this post for an example.
IDENT_SEED() Function
IDENT_SEED() returns the IDENTITY SEED value of an IDENTITY column. See this post for an example.
IDENT_INCR() Function
IDENT_INCR() function returns the IDENTITY INCREMENT value of an IDENTITY column. See this post for an example.
IDENTITY() Function
IDENTITY() function can be used to generate a sequence number in a SELECT query. This function can be used only in an INSERT INTO .... SELECT.. type of query. You can pass the Data Type, IDENTITY SEED and INCREMENT values to the function and it generates IDENTITY values accordingly. See this post for more details.
DBCC CHECKIDENT Command
DBCC CHECKIDENT() can be used to query the current IDENTITY value of a table or to change the SEED and INCREMENT attributes of an IDENTITY column. See this post for a demonstration.
SET IDENTITY_INSERT ON|OFF Command
SET IDENTITY_INSERT ON can be used to insert explicit values to an IDENTITY column. When IDENTITY_INSERT is set to ON, you MUST provide an explicit IDENTITY value, else the insert operation will fail. Only one table at a time (in a single session) can have IDENTITY_INSERT set to ON. See this post for more details.
is_identity column in sys.columns
System catalog view sys.columns lists all the columns in all the tables of the current database. The is_identity column of sys.columns can be used find out the IDENTITY column of a table. See this post for an example.
sys.identity_columns Catalog View
sys.identity_column lists all the identity columns in all the tables of the current database. It exposes the following attributes of an IDENTITY column.
IDENTITY Property
IDENTITY is an attribute that you can assign to an integer column in SQL Server. It is NOT a DATA TYPE as many people think. It is just an attribute that instructs SQL Server to generate a new value every time a row is inserted. IDENTITY property is different from IDENTITY() function and this post explains them both.
SEED value
Every IDENTITY column has a SEED value/attribute. The IDENTITY value starts with the SEED value. If you create an IDENTITY column with a SEED value of 1000, the first IDENTITY value generated will be 1000. The default value of an IDENTITY SEED is 1. If you have not specified a SEED value, SQL Server will assume 1 as the SEED value and the first IDENTITY value generated will be 1.
INCREMENT value
Every IDENTITY column has an INCREMENT value/attribute. The INCREMENT value is used to generate the next IDENTITY value. New IDENTITY value is generated by adding INCREMENT to the PREVIOUS IDENTITY value. The default value of INCREMENT is 1, but you can change it. To generate ODD numbers (1, 3, 5 etc), set SEED to 1 and INCREMENT to 2. To generate EVEN numbers (0, 2, 4 etc), set SEED to 0 and INCREMENT to 2.
@@IDENTITY System Variable
@@IDENTITY is a system variable that holds the last IDENTITY value generated in the current session for any table. Note that it does not necessarily give you the IDENTITY value generated by the INSERT statement you just fired. If you try to insert a row into TableA and if the table has a trigger that performs an insert into TableB, @@IDENTITY will return the value generated for TableB; not TableA. This is explained in this post.
SCOPE_IDENTITY() Function
SCOPE_IDENTITY() returns the last IDENTITY value generated in the current scope. Let us revisit the example I wrote earlier. If you try to insert a row into TableA and if the table has a trigger that performs an inserts into TableB, SCOPE_IDENTITY() will return the IDENTITY value generated for Table A. See this post for more information.
IDENT_CURRENT() Function
IDENT_CURRENT() returns the last generated IDENTITY value of a given table. You can use this function to find the current IDENTITY value of tables. See this post for an example.
IDENT_SEED() Function
IDENT_SEED() returns the IDENTITY SEED value of an IDENTITY column. See this post for an example.
IDENT_INCR() Function
IDENT_INCR() function returns the IDENTITY INCREMENT value of an IDENTITY column. See this post for an example.
IDENTITY() Function
IDENTITY() function can be used to generate a sequence number in a SELECT query. This function can be used only in an INSERT INTO .... SELECT.. type of query. You can pass the Data Type, IDENTITY SEED and INCREMENT values to the function and it generates IDENTITY values accordingly. See this post for more details.
DBCC CHECKIDENT Command
DBCC CHECKIDENT() can be used to query the current IDENTITY value of a table or to change the SEED and INCREMENT attributes of an IDENTITY column. See this post for a demonstration.
SET IDENTITY_INSERT ON|OFF Command
SET IDENTITY_INSERT ON can be used to insert explicit values to an IDENTITY column. When IDENTITY_INSERT is set to ON, you MUST provide an explicit IDENTITY value, else the insert operation will fail. Only one table at a time (in a single session) can have IDENTITY_INSERT set to ON. See this post for more details.
is_identity column in sys.columns
System catalog view sys.columns lists all the columns in all the tables of the current database. The is_identity column of sys.columns can be used find out the IDENTITY column of a table. See this post for an example.
sys.identity_columns Catalog View
sys.identity_column lists all the identity columns in all the tables of the current database. It exposes the following attributes of an IDENTITY column.
- seed_value - Stores the SEED value of an IDENTITY column
- increment_value - Stores the INCREMENT value of an IDENTITY column
- last_value - Stores the Last IDENTITY value generated for the column.
No comments:
Post a Comment