IDENTITY | SQL with Manoj

Jump/Gap Issue with IDENTITY property in SQL Server 2012 … and its workaround (not a bug)

July 27, 2012 5 comments

Sometime back there was discussion going on in an SQL Server forum regarding issues with IDENTITY property in the new SQL Server 2012. The issue was that, when restarting SQL Server (or service) the IDENTITY value in a particular table having IDENTITY column jumps to a higher random number and starts from there.

I tried to reproduce this issue, the SQL Server version I’m using is as follows:

Microsoft SQL Server 2012 RC0 - 11.0.1750.32 (X64) 
	Nov  4 2011 17:54:22 
	Copyright (c) Microsoft Corporation
	Business Intelligence Edition (64-bit) on Windows NT 6.1  
	(Build 7601: Service Pack 1)

I reproduced the issue with following code:

-- CREATE a test table:
CREATE TABLE TEST_IDENTITY (
	ID INT IDENTITY(1,1), 
	NAME VARCHAR(200)
	);

-- INSERT some records:
INSERT INTO TEST_IDENTITY (NAME)
SELECT 'Manoj Pandey'

INSERT INTO TEST_IDENTITY (NAME)
SELECT 'Gaurav Pandey'
GO

-- Check recently inserted records:
SELECT * FROM TEST_IDENTITY -- 2 records, with ID value 1, 2.

-- Check the current IDENTITY value:
DBCC CHECKIDENT ('TEST_IDENTITY')
--Checking identity information: current identity value '2', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
GO


-- RESTART SQL Server and check the current IDENTITY value:
DBCC CHECKIDENT ('TEST_IDENTITY')
--Checking identity information: current identity value '11', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- INSERT a new record:
INSERT INTO TEST_IDENTITY (NAME)
SELECT 'Garvit Pandey'
GO

-- Check recently inserted records:
SELECT * FROM TEST_IDENTITY -- 3 records, with ID value 1, 2, 12.
GO

--// Final cleanup
DROP TABLE TEST_IDENTITY

Finally I got the following output:

As you can see by running the above test before I restarted SQL Server the IDENTITY value was 2, but when I restarted the IDENTITY value changed to 11.
Thus the new records was inserted with value = 12.

The above bug/issue has been logged in the Microsoft Connect site, [here].

Workaround: Right now there is no fix for this, so you need to check all tables in you database every time your SQL Server restarts and reseed the correct IDENTITY value. Check my blog post to reseed the IDENTITY value.

All about IDENTITY columns in SQL Server

May 3, 2012 1 comment

We know that IDENTITY column property creates an IDENTITY column in a table. This property can be used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
But there are certain things that some of us are not aware of about the IDENTITY property.

Do you know that:

1. Not only INT, but you can also use IDENTITY property with other datatypes like: SMALLINT, TINY INT, BIGINT, NUMERIC and DECIMAL.

2. System function @@IDENTITY returns the last identity value used by the current session.

3. Function IDENT_SEED returns the seed value which is the starting number of IDENTITY column, default is 1.

4. Function IDENT_INCR returns the increment value of IDENTITY column, default is 1.

5. Function IDENT_CURRENT accepts table name as parameter and returns the last identity value inserted into that table.

6. IDENTITY() function as mentioned above can be used with CREATE/ALTER TABLE statements, but it can also be used with SELECT INTO statement while creating a table on the fly with SELECT statement. Check [here].

7. With INSERT statement you can’t provide IDENTITY column name and value, unless you explicitly SET IDENTITY_INSERT ON.

8. SET IDENTITY_INSERT ON does not work with Table Variables.

9. Keyword IDENTITYCOL automatically refers to the IDENTITY column of the table.

–> Let’s check how we can use IDENTITYCOL keyword mentioned on 9th point discussed above:

-- Using IDENTITYCOL at WHERE clause and ORDER BY clause:
SELECT *
FROM [Sales].[SalesOrderDetail]
WHERE IDENTITYCOL <= 100
ORDER BY IDENTITYCOL


-- Using IDENTITYCOL with COUNT() function instead of the original column name & with Column name separately:
SELECT 
	SalesOrderID, 
	COUNT(IDENTITYCOL) AS Cnt1,
	COUNT(SalesOrderDetailID) AS Cnt2
FROM [Sales].[SalesOrderDetail]
WHERE ModifiedDate BETWEEN '01/01/2006' AND '02/01/2006'
GROUP BY SalesOrderID


-- Using IDENTITYCOL at column level and at JOIN's ON clause:
SELECT 
	D.SalesOrderID, D.IDENTITYCOL, D.OrderQty, D.UnitPrice, D.UnitPriceDiscount, [ProductID], 
	H.AccountNumber, H.SubTotal, H.TotalDue
FROM [Sales].[SalesOrderDetail] D
INNER JOIN [Sales].[SalesOrderHeader] H
	ON H.IDENTITYCOL = D.SalesOrderID
WHERE H.OrderDate BETWEEN '01/01/2006' AND '02/01/2006'

Check the output of the last 2 SQL statements:
– The 1st output shows same COUNTS for both the columns.
– And 2nd output shows the original name of Column name appearing on the header even when we are using the IDENTITYCOL keyword.

As shown above you don’t have to go, look and type the actual IDENTITY column names of different tables and can be taken care of by using the common IDENTITYCOL keyword.

Check the same demo here in YouTube:

Categories: SQL Tips Tags: IDENTITY, IDENTITY Columns

Using IDENTITY function with SELECT statement in SQL Server

December 15, 2011 6 comments

In MS BOL for IDENTITY columns it is mentioned that [link]: “It creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.”

But experimenting with IDENTITY function I learnt this new thing with is not mentioned (or I could not find) anywhere in MS BOL that: “IDENTITY function can also be used with a SELECT statement but with some condition.”

Let’s check what’s that condition is:

USE [AdventureWorks]
GO

-- Let's use Person.Contact table in our SELECT statement and add a new column as IDENTITY function to it:
SELECT 
	IDENTITY (INT, 100, 5) AS NEW_ID, 
	ContactID, Title, 
	FirstName, 
	MiddleName, 
	LastName, 
	EmailAddress, 
	Phone
FROM Person.Contact

Error Msg thrown:
Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

As per the error msg we cannot add an IDENTITY column to a SELECT query. The SELECT should be followed by an INTO clause. This way a new table will be created and records will be entered with the new IDENTITY column.

-- Let's add INTO clause in SELECT statement:
SELECT 
	IDENTITY (INT, 100, 5) AS NEW_ID, 
	ContactID, 
	Title, 
	FirstName, 
	MiddleName, 
	LastName, 
	EmailAddress, 
	Phone
INTO #tempTable
FROM Person.Contact

Again an Error Msg:
Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table ‘#tempTable’, which already has column ‘ContactID’ that inherits the identity property.

Completely agree with this!!! You cannot have 2 IDENTITY columns in a single table.

-- So we will remove the ContactID column as we want to have new ID column.
SELECT 
	IDENTITY (INT, 100, 5) AS NEW_ID, 
	Title, 
	FirstName, 
	MiddleName, 
	LastName, 
	EmailAddress, 
	Phone
INTO #tempTable
FROM Person.Contact
-- This worked perfectly.

-- Now let's check the newly created temp table and inserted records:
select * from #tempTable

-- Output below shows selected records from Person.Contact table with a new column having values starting from 100 with a gap of 5:

-- Final Cleanup
DROP TABLE #tempTable

>> Check & Subscribe my [YouTube videos] on SQL Server.

Check the same demo here in YouTube:

Categories: DB Concepts, SQL Tips Tags: IDENTITY, SELECT IDENTITY INTO

Check and ReSeed IDENTITY column value in a table

July 8, 2011 3 comments

In my [previous post] I discussed about IDENTITY property with a [demo video].

Here in this post we will see how you can Re-Seed a particular IDENTITY Column value.

There are times when you want to Re-Seed or reset the value of an identity column in a table.

When you delete a lot of records from a table and then try to insert more records. You would expect the identity values to start after the max value present after delete. But it preserves the max value ever present in the table and continues from there.

Or when you delete all records form a table and want to reseed the identity column value to start from afresh 1.

All you need is following statement, syntax:
DBCC CHECKIDENT (TableNameWithSingleQuotes, reSeed, NewseedValue);

-- Example:
DBCC CHECKIDENT ('Person.Contact', reseed, 100);

This will start assigning new values starting from 101. But make sure that there are no records that have value greater than 100, otherwise you might duplicates.

If all records are deleted from the table and you want to reseed it to 0 then you can also TRUNCATE the table again. This will reset the IDENTITY column as per the DDL of that table.

–> You can check the same demo here:

–> Sample SQL Code:

-- Check current IDENTITY value, and re-seeds the value with largest value of the column:
DBCC CHECKIDENT ('dbo.Employee'); -- do not use it.
GO

-- Re-seed IDENTITY column value:
DBCC CHECKIDENT ('dbo.Employee', reseed, 102);
GO

-- Check current IDENTITY value (without Re-seeding):
DBCC CHECKIDENT ('dbo.Employee', noreseed);
GO

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Deepak B')

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Manish A')
GO

SELECT * FROM [dbo].[Employee]
GO

Categories: SQL Tips Tags: DBCC CHECKIDENT, IDENTITY, IDENTITY Column, ReSeed IDENTITY

SQL Basics – IDENTITY property of a Column (in a table)

October 19, 2010 1 comment

With IDENTITY property you can:

1. Creates an identity column in a table.

2. Used for generating key values, based on the current seed & increment.

3. Each new value for a particular transaction is different from other concurrent transactions on the table.

–> You can check the demo about IDENTITY property here:

–> IDENTITY property on a column does not guarantee:

1. Uniqueness of the value,

2. Consecutive values within a transaction,

3. Consecutive values after server restart or other failures,

4. Reuse of values,

–> SQL Script used in Demo:

-- IDENTITY property of a Column

CREATE TABLE [dbo].[Employee](
	[EmployeeID]	int				NOT NULL IDENTITY (100, 1),
	[EmployeeName]	nvarchar(100)	NOT NULL,
	[Gender]		nchar(1)		NULL,
	[DOB]			datetime		NULL,
	[DOJ]			datetime		NULL,
	[DeptID]		int				NULL
)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('JHON K', 'M', NULL, '2010-01-01', NULL)

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Brock H')
GO

SELECT * FROM [dbo].[Employee]
GO


-- Inserting Explicit value in IDENTITY column:

SET IDENTITY_INSERT [dbo].[Employee] ON

INSERT INTO [dbo].[Employee] ([EmployeeID],[EmployeeName])
VALUES (1000, 'Brock H')

SET IDENTITY_INSERT [dbo].[Employee] OFF
GO


SELECT * FROM [dbo].[Employee]
GO

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Jhon B')
GO

SELECT * FROM [dbo].[Employee]
GO

–> Check more articles on IDENTITY property:

– RE-SEED an IDENTITY value of a Column

– Using IDENTITY() function with SELECT into statement

– All about IDENTITY columns, some more secrets

– IDENTITY property behavior with SQL Server 2012 and above

Categories: SQL Basics Tags: IDENTITY, IDENTITY Columns