Exploring IDENTITY in MS SQL Server

You might be aware of Identity column in the database, in case you are new to database lets go through the its definition So as per wiki[1] "An Identity column is a column (also known as a field) in a database table that is made up of values generated by the database. This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle. Because the concept is so important in database science, many RDBMS systems implement some type of generated key, although each

has its own terminology. An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key, however this is not always the case. It is a common misconception that an identity column will enforce uniqueness, however this is not the case. If you want to enforce uniqueness on the column you must include the appropriate constraint too. In Microsoft SQL Server you have options for both the seed (starting value) and the increment. By default the seed and increment are both 1.". See the below figure, it will clear all the doubts. Lets see some important facts about Identity columns and its most asked questions in the SQL Server / .Net interview. General Syntax for Identity is IDENTITY [ (seed , increment) ] . You must specify both the seed and increment or neither. If neither is specified, the default is (1,1). IDENTITY columns got populated automatically. No need to insert data into IDENTITY columns explicitly but we can insert by turning on the identity insert on the table . See the below example: It cannot accept NULL values. As soon as you define a column as IDENTITY, it becomes a NOT NULL column. Use DBCC CHECKIDENT to check the current identity value and compare it with the maximum value in the identity column. If a table with an identity column is published for replication, the identity column must be managed in a way that is appropriate for the type of replication used If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to make sure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON IDENT_SEED [syntax : IDENT_SEED ( 'table_or_view' ) ] returns the original seed value (returned as numeric(@@MAXPRECISION,0)) that was specified when an identity column in a table or a view was created. Changing the current value of an identity column by using DBCC CHECKIDENT does not change the value returned by this function. IDENT_CURRENT [syntax : IDENT_CURRENT( 'table_name' ) ] returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope. IDENT_INCR [syntax : IDENT_INCR ( 'table_or_view' ) ] returns the increment value (returned as numeric (@@MAXPRECISION,0)) specified during the creation of an identity column in a table or view that has an identity column. Now , I have created Identity column in my table, Now the next problem is how to get its current value or the value which is just inserted in the table? So for this we have mainly three functionality available till MS SQL Server 2012. Let us discuss each functionality. @@IDENTITY[2] After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back. SCOPE_IDENTITY[3] Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch. IDENT_CURRENT[4] Returns the last identity value generated for a specified table in any session and any scope. IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ. In short we can conclude the following: SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger. Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope. IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. Please go through the below references from where the above topic is composed. It will be more helpful for you. References: [1]: WIKI http://en.wikipedia.org/wiki/Identity_column [2] : MSDN http://msdn.microsoft.com/en-us/library/ms187342.aspx [3] : MSDN http://msdn.microsoft.com/en-us/library/ms190315.aspx [4] : MSDN http://msdn.microsoft.com/en-us/library/aa933217(v=sql.80).aspx