Welcome to EMC Consulting Blogs Sign in | Join | Help

Claypole's World - The SQL Server Side

Concerning development of bespoke applications and database administration.

T-SQL IDENTITY : Fun and Games with DBCC CHECKIDENT

 

This issue caught me out today whilst putting together some build scripts for reference data. So I thought I’d share it.

USE [tempdb]

GO

CREATE TABLE [dbo].[Table_1](

      [Col1] [int] IDENTITY(1,1) NOT NULL,

      [Col2] [char](1) NOT NULL

) ON [PRIMARY]

GO

delete from dbo.table_1

dbcc checkident(Table_1,Reseed,0)

insert into table_1

values('1')

--Checking identity information: current identity value 'NULL', current column value '0'.

--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Select * from table_1

Basically when you first create a table with an identity column the value of the identity prior to inserting any data is NULL and not 0. 

Therefore the first DBCC CHECKIDENT does not behave as expected. 

The first row you write has an identity value of 0 and not 1. 

Now Run it again and the problem magically disappears…(the helpful messages in green didn’t come out in the build scripts – at least I didn’t see them.)

delete from dbo.table_1

dbcc checkident(Table_1,Reseed,0)

insert into table_1

values('1')

--Checking identity information: current identity value '0', current column value '0'.

--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Select * from table_1

drop table dbo.table_1

I should also point out that I was using Database Edition and this code was in my post-deployment script. I therefore didn't see the messages as it was all executed in msbuild. What was even more interesting was that the build scripts carried on regardless even though as a result of my bug a foreign key violation occurred. More on that in another post methinks...

Cheers, James

 

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(optional)
(required) 
Submit

About James.Rowland-Jones

James is an Advisory Practice Consultant with EMC Consulting. He works primarily with SQL Server technologies in architecture, development and administration capacities. He also co-ordinates our Internal SQL Server Community.

View James Rowland-Jones's profile on LinkedIn

Powered by Community Server (Personal Edition), by Telligent Systems