Identity

IDENTITY:

It creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

Syntax

IDENTITY [ ( seed , increment) ]

Arguments

Seed

Is the value that is used for the very first row loaded into the table.

Initial seed and increment can be any integer value

Increment

Is the incremental value that is added to the identity value of the previous row that was loaded.

You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Remarks

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 ensure 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.

Use DBCC CHECKIDENT to check the current identity value and compare it with the maximum value in the identity column.

When the IDENTITY property is used with CREATE TABLE, Microsoft® SQL Server™ uses the NOT FOR REPLICATION option of CREATE TABLE to override the automatic incrementing of an identity column. Usually, SQL Server assigns each new row inserted in a table a value that is some increment greater than the previous highest value. However, if the new rows are replicated from another data source, the identity values must remain exactly as they were at the data source.

Identity Data types :

The identity must only contain integer values but the column can be of any numeric datatype (bigint, int, tinyint, numeric, decimal). But, we need to make sure identity column are not reaching the limit of their base data type.

An IDENTITY column of tinyint data type can go up to 255.

Smallint can go up to 32767

Int can go up to 2147483647 and

Bigint can go up to 9223372036854775807.

Example :

If you created an IDENTITY column of smallint datatype, its values can go upto 32767.

If you try to insert anymore rows, you will get the following error:

Arithmetic overflow error converting IDENTITY to data type smallint.

So, We need to monitor these IDENTITY columns, to avoid getting into such problems. If you can see in advance, that an IDENTITY column is reaching its limit, then we could do something about it, before it reaches the limit.

IDENT_SEED

Returns the seed value (returned as numeric (@@MAXPRECISION,0)) specified during the creation of an identity column in a table or a view that has an identity column.

Syntax

IDENT_SEED ('table_or_view' )

Arguments

'table_or_view'

Is an expression specifying the table or view to check for a valid identity seed value.  table_or_view can be a character string constant enclosed in quotation marks, a variable, a function, or a column name.  table_or_view is char, nchar, varchar, or nvarchar.

Return Types

Numeric

Examples

This example returns 1 for the jobs table in the pubs database because the jobs table includes an identity column with a seed value of 1.

USE pubs

SELECT TABLE_NAME, IDENT_SEED (TABLE_NAME) AS IDENT_SEED

FROM INFORMATION_SCHEMA.TABLES

WHERE IDENT_SEED (TABLE_NAME) IS NOT NULL

Here is the result set:

TABLE_NAME                                                   IDENT_SEED 

------------------------------------------------------------ -----------

jobs                                                                           1          

IDENT_INCR

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.

Syntax

IDENT_INCR ( 'table_or_view' )

Arguments

'table_or_view'

Is an expression specifying the table or view to check for a valid identity increment value. table_or_view can be a character string constant enclosed in quotation marks, a variable, a function, or a column name. table_or_view is char, nchar, varchar, or nvarchar.

Return Types

Numeric

Examples

This example returns 1 for the jobs table in the pubs database because the jobs table includes an identity column with an increment value of 1.

USE pubs

SELECT TABLE_NAME, IDENT_INCR (TABLE_NAME) AS IDENT_INCR

FROM INFORMATION_SCHEMA.TABLES

WHERE IDENT_INCR (TABLE_NAME) IS NOT NULL

Here is the result set:

TABLE_NAME                                                  IDENT_INCR  

------------------------------------------------------------ -----------

jobs                                                         1          

SET IDENTITY_INSERT

Allows explicit values to be inserted into the identity column of a table.

Syntax

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

Arguments

database

Is the name of the database in which the specified table resides.

owner

Is the name of the table owner.

table

Is the name of a table with an identity column.

Remarks

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Permissions

Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.

@@IDENTITY

Returns the last-inserted identity value.

Syntax

@@IDENTITY

Return Types

Numeric

Remarks

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value 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 will return 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.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

The scope of the @@IDENTITY function is the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure, which is executing in the context of the remote or linked server, gather the identity value and return it to the calling connection on the local server.

SCOPE_IDENTITY (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

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.

Syntax

SCOPE_IDENTITY()

Return Types

Numeric (38,0)

Remarks

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.

USE tempdb;

GO

CREATE TABLE TZ (

   Z_id  int IDENTITY(1,1)PRIMARY KEY,

   Z_name varchar(20) NOT NULL);

INSERT TZ

   VALUES ('Lisa'),('Mike'),('Carla');

CREATE TABLE TY (

   Y_id  int IDENTITY(100,5)PRIMARY KEY,

   Y_name varchar(20) NULL);

INSERT TY (Y_name)

   VALUES ('boathouse'), ('rocks'), ('elevator');

/*Create the trigger that inserts a row in table TY

when a row is inserted in table TZ.*/

CREATE TRIGGER Ztrig

ON TZ

FOR INSERT AS

   BEGIN

   INSERT TY VALUES ('')

   END;

/*FIRE the trigger and determine what identity values you obtain

with the @@IDENTITY and SCOPE_IDENTITY functions.*/

INSERT TZ VALUES ('Rosalie');

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

GO

SELECT @@IDENTITY AS [@@IDENTITY];

GO

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.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

IDENT_CURRENT (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

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.

Syntax

IDENT_CURRENT (‘table_name’)

Arguments

table_name

Is the name of the table whose identity value is returned. table_name is varchar, with no default.

Return Types

Numeric (38,0)

Exceptions

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_CURRENT may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.

Remarks

IDENT_CURRENT is similar to the 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:

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.

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

DBCC CHECKIDENT:

Checks the current identity value for the specified table and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

Syntax:

                DBCC CHECKIDENT

(

    table_name

        [ , { NORESEED | { RESEED [ ,new_reseed_value ] } } ]

)

[ WITH NO_INFOMSGS ]

Arguments

table_name

Is the name of the table for which to check the current identity value.

The table specified must contain an identity column.

Table names must comply with the rules for identifiers.

noreseed

Specifies that the current identity value should not be changed.

reseed

Specifies that the current identity value should be changed.

new_reseed_value

Is the new value to use as the current value of the identity column.

With no_infomsgs

Suppresses all informational messages.

DBCC CHECKIDENT (table_name, NORESEED)

Current identity value is not reset. DBCC CHECKIDENT returns the current identity value and the current maximum value of the identity column. If the two values are not the same, you should reset the identity value to avoid potential errors or gaps in the sequence of values.

DBCC CHECKIDENT ( table_name )   or  DBCC CHECKIDENT ( table_name, RESEED )

If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.

DBCC CHECKIDENT ( table_name, RESEED,new_reseed_value )

Current identity value is set to the new_reseed_value. If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT usesnew_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.

If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions:

·         If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.

·         If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

Exceptions

The current identity value is larger than the maximum value in the table.

Execute DBCC CHECKIDENT (table_name, NORESEED) to determine the current maximum value in the column, and then specify that value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) command.                            

Or

Execute DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) with new_reseed_value set to a very low value, and then run DBCC CHECKIDENT (table_name, RESEED) to correct the value.

All rows are deleted from the table.

Execute DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) with new_reseed_value set to the desired starting value.

Changing the Seed Value

The seed value is the value inserted into an identity column for the very first row loaded into the table. All subsequent rows contain the current identity value plus the increment value where current identity value is the last identity value generated for the table or view. For more information, see Creating and Modifying Identifier Columns.

You cannot use DBCC CHECKIDENT to perform the following tasks:

·         Change the original seed value that was specified for an identity column when the table or view was created.

·         Reseed existing rows in a table or view.

To change the original seed value and reseed any existing rows, you must drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values. The order in which the rows are updated is not guaranteed

Permissions

Caller must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Some notes about DBCC CHECKIDENT

·         You need to be a db_owner (dbo) of the database. If you are not a dbo, you would need permission to execute on behalf of dbo using WITH EXECUTE AS

·         Reseed as (n – 1). So if you wanted a value of 5 as your next value, reseed using 4

·         Setting a value which is less than values which are already in the table will result in unique constraint violations as soon as the value inserted hits a value which already exists in the table.

·         Data type limits apply, you cannot exceed the max value of a TINYINT for example when reseeding.

·         Running it on an empty table produces a change in behaviour. The following demonstrates this….

DBCC CHECKIDENT('Table_1', RESEED, 0);

Now after running that DBCC statement and then adding a row, you would expect the next value to be a 1 but it’s not….

INSERT INTO Table_1(DateAdded)

      VALUES(GETDATE());

       

DBCC CHECKIDENT ('Table_1', NORESEED)

Checking identity information: current identity value ‘0’, current column value ‘0’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is normal behaviour however. If the table is empty, then the next value is the reseed value as opposed to the reseed value plus the increment value.

Scenarios when you might use DBCC CHECKIDENT

It’s already clear what the function of this is but when might you use it?

I’m nervous at the thought of this being called in a production environment but in a dev environment, sure it could come in handy if you need to reset things to a known state.

TRUNCATE TABLE is a command which will enable you to delete all rows from a table and at the same time, reset the identity value. However you need permission to be able to do this and it will only work if the table is not referenced by a foreign key constraint. This is when DBCC CHECKIDENT is useful.

Adding Identity Property to an existing column in a table.

How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action?

For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table.

However, there is an easy way to accomplish this action. It can be done through SSMS.

The below is sample script generated while doing through SSMS.

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_example1

(

eid INT NOT NULL IDENTITY (1, 1)

) ON [PRIMARY]

GO

SET IDENTITY_INSERT dbo.Tmp_example1 ON

GO

IF EXISTS(SELECT * FROM dbo.example1)

EXEC('INSERT INTO dbo.Tmp_example1 (eid)

SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT dbo.Tmp_example1 OFF

GO

DROP TABLE dbo.example1

GO

EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'

GO

COMMIT

Removing Identity Property from an existing column in a table.

Same is the case when you want to remove Identity column property from a column.

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_example1

(

eid INT NOT NULL

) ON [PRIMARY]

GO

IF EXISTS(SELECT * FROM dbo.example1)

EXEC('INSERT INTO dbo.Tmp_example1 (eid)

SELECT eid FROM dbo.example1 WITH (HOLDLOCK TABLOCKX)')

GO

DROP TABLE dbo.example1

GO

EXECUTE sp_rename N'dbo.Tmp_example1', N'example1', 'OBJECT'

GO

COMMIT

Add the identity column to existing table:

ALTER TABLE MyTable ADD ID INT IDENTITY(1,1) NOT NULL

SELECT *, IDENTITY(INT,1,1) AS id INTO #tbl FROM sysobjects

Detecting Identity Columns And Their Properties

The existance of an identity column on a table can be checked via

SELECT OBJECTPROPERTY(OBJECT_ID('<tablename>'),'TableHasIdentity')

Which will return 1 if an identity exists on the table.

Similarly ...

SELECT COLUMNPROPERTY(OBJECT_ID('<tablename>'),'<columnname>','IsIdentity')

... Will show if a column has the identity property.

A more useful way of obtaining this information is by using the catalog view sys.identity_columns which returns a row for each column in the database with an identity property.

SELECT TableName = OBJECT_NAME(OBJECT_ID) ,

       ColumnName = name ,

       OriginalSeed = seed_value ,

       Step = increment_value ,

       LastValue = last_value ,

       IsNotForReplication = is_not_for_replication

FROM sys.identity_columns

We can also reset the current seed to it's original value via a truncate table.

Note - a delete does not do this.

From SQL Server 2012 version, when SQL Server instance is restarted, then table's Identity value is jumped and the actual jumped value depends on identity column data type. If it is integer (int) data type, then jump value is 1000 and if big integer (bigint), then jump value is 10000. From our application point of view, this increment is not acceptable for all the business cases specially when the value shows to the client. This is the special case/issue ships with only SQL Server 2012 and older versions have no such issue.

Update a column with sequence number in SQL Server

Method 01: (Using IDENTITY)

ALTER TABLE #Employee

ADD ID INT IDENTITY(1,1)

GO

SELECT * FROM #Employee

GO

--Droping the column for next examples

ALTER TABLE #Employee

drop column ID

Method 02: (Using local variable)

ALTER TABLE #Employee

ADD ID INT

GO

DECLARE @i INT = 0

UPDATE #Employee

SET @i = ID = @i + 1

SELECT * FROM #Employee

--OR--

GO

DECLARE @i INT = 0

UPDATE #Employee SET ID = NULL

UPDATE #Employee SET Id = @i , @i = @i + 1

SELECT * FROM #Employee

Method 03: (Using CTE)

UPDATE #Employee SET ID = NULL

;WITH CTE AS (

SELECT ROW_NUMBER() OVER(ORDER BY Hiredate) AS ID, Name FROM #Employee

)

UPDATE a

SET ID = b.ID

FROM #Employee a

INNER JOIN CTE b ON a.Name = b.name

SELECT * FROM #Employee

Method 04: (Using SEQUENCE)

UPDATE #Employee SET ID = NULL

GO

CREATE SEQUENCE idnum

START WITH 1

INCREMENT BY 1

NO MAXVALUE

NO CYCLE

GO

UPDATE #Employee

SET id = NEXT value FOR idnum

SELECT * FROM #Employee

GO

DROP SEQUENCE idnum --Droping the sequence

If a table is having only one identity column how can we insert records into this table?

Consider table customer which is created with below statement in Sql server

create table customer

( id int identity(1,1))

Now to insert  records into this table we can do it in two ways:

INSERT INTO customer DEFAULT VALUES

SET IDENTITY_INSERT dbo.customer ON

INSERT INTO customer (id)

values (1),(2),(3)

Disadvantages of Identity Column:

·         Honestly, identity columns are bad relational design and are not ANSI SQL. They have no meaning to the data in the table, and are hard to read if you are looking at a table that uses it as a foreign key.

·         An Identity column is not guaranteed to be unique nor consecutive.

·         If any insert failure is there then also it can change the current seed value.

·         If we truncate a table (but not delete), It will update the current seed to the original seed value.

·         Moreover, once a table with an identity column is populated, deleting all the rows in the table and reinserting new rows does not cause the numbering to restart from 1. Numbering continues from the last generated number of the table.

Advantages of Identity Column:

A non-SQL based solution to the problem of having an default unique value assigned to a row. SQL Server prefetches identity values into cache and adds them automatically to rows as they're inserted into tables that have a type Identity column. There's no concurrency issues, no deadlocking in high-insert situations, and no possibility of duplicate values.

A high performance Unique identifier; SQL server's optimizer is tuned to work well with Unique indexes based on the identity value.

The flexibility to insert into the identity field a specific value in the case of a mistaken row deletion. (You can never update however). You accomplish this by:

set identity_insert [datababase]..[table] on

go

Note however that the System will not verify the uniqueness of the value you specifically insert (unless of course you have a unique index existing on the identity column).            

That said, using a Surrogate Key (i.e. an identity column) does have some advantages. Indexes can be faster on INT columns compared to a varchar column, and they take up less space on disk and in RAM when you start using it as a foreign key in other tables (Thus speeding up queries on the child tables.)