In this demonstration, we will be showing how to use IDENTITY columns in a table. First, an IDENTITY field is similar to an auto number field, where the number will automatically be added as each record is inserted. By default, the value will start at 1 and then count by 1.

This can be changed by modifying the SEED and INCREMENT values when you create the IDENTITY field. For Example, if we want to start at the number 1000 and count by 2, we create a field like this IDENTITY(1000, 2). However, as you can see in our code, we will be going with the default settings.

CREATE TABLE dbo.TestTable
(TestID smallint IDENTITY, 
 TestName char(15) NOT NULL)
GO

In the code above, you will notice we are creating a table with two columns named TestID and TestName with the TestID field being created as an IDENTITY field. After we create the table, we will need to populate the table by using the INSERT statement.

--Different ways of writing an INSERT statement
INSERT dbo.TestTable VALUES ('First Row')
INSERT INTO dbo.TestTable (TestName)
	VALUES ('Second Row')
INSERT INTO dbo.TestTable VALUES
	('Third Row'), ('Forth Row'), ('Fifth Row')
GO

On line 2 we are using the shortest way of adding a record by NOT including the INTO statement or the name of the columns. You may also notice that we are only supplying the value for the second column as the first column will be auto populated with the next number available for the IDENTITY field.

On lines 3 and 4 we are also inserting a row but in this case, we did include the INTO statement as well as the column name for the TestName field. There is no functional difference between the two INSERT statements. (With one exception we will see later.)

Finally on lines 5 and 6, we are adding more than one column at a time. (Row constructors were introduced in SQL Server 2008.) Now when we run the INSERT statements, we should see the following messages. (There are three separate Insert Statements.)

Now to see the actual rows that are in this table we would need to run a SELECT statement which would return the following results. Notice that the first column had numbers automatically added. This is because it was an IDENTITY field.

-- Show records from the dbo.TestTable
SELECT TestID, TestName FROM dbo.TestTable

What if later we want to insert an explicit value into the IDENTITY field? First, we need to turn the IDENTITY_INSERT statement ON using a SET statement. Then we add an explicit value, in the example below, we are inserting the value of -99 into the first column.  It is important to note that we are required to supply the column names when explicitly inserting values.

----Inserting a value into an IDENTITY field.
SET IDENTITY_INSERT dbo.TestTable ON
INSERT INTO dbo.TestTable(TestID, TestName)
	VALUES (-99, 'Explicit Row')
SET IDENTITY_INSERT dbo.TestTable OFF

If we want to return to the auto-numbering of the IDENTITY field, we will need to turn IDENTITY_INSERT OFF. Then when we insert another row, the numbering starts counting again from where the last auto-number finished.

INSERT INTO dbo.TestTable VALUES ('Seventh Row')

When working with larger tables it may be necessary to find out what the current IDENTITY value is in the table. You can do this by using the DBCC CHECKIDENT statement.

--Different ways of writing an INSERT statement
INSERT INTO dbo.TestTable VALUES ('First Row')

Notice that the message shows the current identity value of 6.

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

You can also use the DBCC CHECKIDENT statement to change the IDENTITY value. Notice after we reseed to the value of 20 the next row being inserted will be 21.

--Reseed IDENTTY Values
DBCC CheckIdent('dbo.TestTable', RESEED, 20)

--Insert another IDENTITY row after Reseeding
INSERT INTO dbo.TestTable VALUES ('Row 20')

-- Show records from the dbo.TestTable
SELECT TestID, TestName FROM dbo.TestTable