What is an Identity Gap?

This video explains what an Identity Gap is.

I'm currently working for FM Global, an industry leading engineering & insurance company, as a DBA. I've done development of large scale applications in languages such as VB, C# and Java. I've worked in SQL Server from the hoary days of 6.0. My nickname at work is the "The Scary DBA." I even have an official name plate with it. I wear it proudly. I was awarded a Microsoft MVP in April of '09.

Leo Njampa on 10/23/2008

Good!

melt on 10/23/2008

mm i coudnt see it

Michael on 10/23/2008

something I never knew

DavidB on 10/23/2008

Great presentation showing the basics of the identity columns.

ML Stephens on 10/23/2008

really interesting

WChaster on 10/23/2008

Here I thought I was going to learn something about my personality, who would have thought this was about computers. Thanks Grant, are you going to follow-up with How the GAPs can be avoided, or was this simply a clear explanation as to how they become?

lisa on 10/23/2008

So is there a recommended practice to prevent the gaps from happening? Is there a rollback function?

Christopher Korzekwinski on 10/23/2008

Nothing new

Carla Wilson on 10/23/2008

Excellent little tidbit - makes me wonder how many gaps I've caused in my databases! :)

Grant Fritchey on 10/24/2008

It was just an explanation of how they become. Generally, gaps don't mean anything. If you really, absolutely, need a continuous run of numbers, you should use a mechanism other than IDENTITY.

Tahir A. Syed on 10/27/2008

Marvelous!

Jason Wall on 10/27/2008

Thanks for the info!

Pat on 10/28/2008

Gaps can also be created if table data is deleted.

F952CE7089 on 10/30/2008

Good

Ramkumar Murugesan on 11/25/2008

Excellent and simple Explanation

Carlos E. Mosquera on 12/26/2008

Please create a way to avoid this!

Grant Fritchey on 12/27/2008

That's just it, there is no way to avoid it using the identity column. If you need absolute, perfect incrementing of numbers (and you shouldn't for just a primary key) then you can create your own mechanism.

Robert Cain on 1/15/2009

Great video, short, straight to the point, and clearly demonstrates the issue with an easy to understand example.

Mark on 12/25/2009

But how to avoid identity gap ?

Grant Fritchey on 12/26/2009

You really can't. If a rollback occurs, an error... you get a gap. If you have to have a perfectly incremented set of values, identity is probably not your best device.

Morten Dvinge on 4/26/2011

Yes .... And what to do about it? Think that is more interesting to tell

concoran on 4/26/2011

Good video. There are always small things that skip your mind.

Grigore Dolghin on 4/26/2011

Just use a table containing the table name and next value, and increment that value at each insert using a trigger, everything bound in a transaction.

Carlos B. Vasquez on 4/26/2011

Simple and quick example of identity Gap. I like it.

Tim Curtin on 4/26/2011

So, you're saying there is no solution to recapture the missing values in production?????

Chun Chang on 4/26/2011

Simple, Clear, Right to the point.

Tom Stafford on 4/26/2011

Good information

Douglas Duncan on 4/26/2011

Almost seems like the video was cut off and that it should have kept going. Maybe that is how the author meant it to be though.

Maurice Ivory on 4/26/2011

It was okay

Eric on 4/26/2011

I assumed I knew what an "Identity gap" is, and I did, but I never thought to look at an execution plan for such an insert. Thanks for teaching me WHY it happens the way it does.

Lanny Mullis on 4/26/2011

Could have explained how to fix identity gaps for those who want to.

Amy Neumann on 4/26/2011

Wow, I didn't know that!

Susan Keel on 4/26/2011

I'm way behind on my SQL and this will be very helpful. Are there new videos like this every day ... week ... month ... ??

Irving Díaz on 4/26/2011

simple but efficent

Steve Harris on 4/26/2011

Good explanation, but how is this information helpful?

J. Parrish Preston on 4/26/2011

nice explaination, but is there a way to correct it without using the reseed function?

Don Weigend on 4/26/2011

Good tip!

Michael Neymit on 4/26/2011

I didn't know you could right-click and view "properties" in the execution plan. This was the most useful part :)

Robert Johnson on 4/26/2011

Audio quality was lower than expected for this video.

Don Gilman on 4/26/2011

Interesting

Charlie Bruno on 4/27/2011

Excellent topic and presentation.

Shashank Banerjea on 4/27/2011

Good information to have. The information can be enhanced if the author commented if having an identity gap is necessarily bad and if so, is there a way to resolve it? I do not think, it is bad, unless you have a really large data set and you are suddenly running out of identity values. I believe, we need to start considering options, even before we reach that point.

Martin Miller on 4/27/2011

Good to know. Thanks.

Jeff Moden on 4/28/2011

Short, sweet, and to the point. The explanation using the actual execution plan drives the point home. Well done, Grant. --Jeff Moden

DP on 4/30/2011

Nice video, but didn't mention how to avoid the identity gaps.

Mark Sopczak on 5/4/2011

Wow... I had no idea about failed inserts and indentity columns.

Leigh Anne on 5/9/2011

I always wondered what caused this. Thanks!

ravi on 5/12/2011

good one