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.

BC04ACEF43 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.