How To Create, Check And Change Identity Column In Microsoft SQL Server

Microsoft SQL Server has built-in an identity column (fields) which used for generating key values.  It may refer as an AutoNumber field in Microsoft Access or a sequence in Oracle.  An identity property on a column will generate a new value based on the current seed and increment when a new record is inserted into a table.  Each new value generated for a particular transaction will different from other concurrent transactions on the table.  This identity column can be used as uniqueness column when assigned it’s as primary key.

This tutorial will show you how to create a new table using identity property, check current identity value and forcing current identity value to new value. I will assume that you were familiar with SQL Management Studio (SQL Query).

1. Create table using identity property.

   In this example, we will create a new table called as TechJourney with three fields:
   TechJourneyID (identity with primary key), TechJourneyCode and Description.

   SQL Syntax

   Create Table TableName
   ( ID int IDENTITY(1,1) NOT NULL,
   Column1 datatype,
   Column2 datatype,
   Column3 datatype,
   PRIMARY KEY (ID))

   Run the following SQL Statement in SQL Management Studio.

   CREATE TABLE TechJourney (
   TechJourneyID int IDENTITY(1,1) NOT NULL,
   TechJourneyCode [nvarchar](10) NULL,
   Description [nvarchar](100) NULL,
   PRIMARY KEY(TechJourneyID))

   

   After that, insert two records into table ‘TechJourney’ for us to check the identity values.

   Insert into TechJourney( TechJourneyCode, Description)
   Values( 'TJ1', 'Tech Journey 1')

   Insert into TechJourney( TechJourneyCode, Description)
   Values( 'TJ2', 'Tech Journey 2')



2. Check current identity value.

   SQL Syntax

   DBCC CHECKIDENT(table_name, NORESEED)

   Note: NORESEED – the current identity value should not be changed.

   Now we will checking what is the current value after insert two records.

   Run the following SQL Statement in SQL Management Studio:

   DBCC CHECKIDENT(TechJourney, NORESEED)

   Message Return:

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

   We will select the records in table TechJourney to see TechJourneyID value as below:

   

3. Forcing current identity value to new value.

   SQL Syntax

   DBCC CHECKIDENT(table_name, RESEED, new_reseed_value)

   Now we want to change the current value from 2 to 20.

   Run the following SQL Statement in SQL Management Studio:

   DBCC CHECKIDENT(TechJourney, RESEED, 20)

   Message Return:

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

   Check again the current identity value as below:

   Run the following SQL Statement in SQL Management Studio:

   DBCC CHECKIDENT(TechJourney, NORESEED)

   Message Return:

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

   In the previous step, we already insert two records into the table and the identity value now is 2.

   Again, insert two records into table ‘TechJourney’ with the following SQL Statement.

   Insert into TechJourney( TechJourneyCode, Description)
   Values( 'TJ3', 'Tech Journey 3')

   Insert into TechJourney( TechJourneyCode, Description)
   Values( 'TJ4', 'Tech Journey 4')

   

   We will select the records in table TechJourney to see TechJourneyID value as below:

   

   The TechjourneyID for ‘TJ3’ is 21 = 20(new idenetity value) + 1(increment)).