Question of the month August 2015 – Do gaps in identity column matter?

I read questions in forums frequently that developers are very much worried about gaps in identity column. Suppose you have a table with identity column and addition and deletion are frequently performed. But the newly added identity column value is not always next number of lastly available value in the table. It is always next number of lastly generated value no matter if it exists in the table.

Do you have any real time scenario that gaps in identity are not acceptable?

Millisecond values and separator in time values

When you express millisecond values in a time, you need to make sure that it has three digits or use dot as a millisecond separator.

Consider the following example

declare @date datetime
set @date='20101201 14:23:12.77'
select @date as date

The result is

date
-----------------------
2010-12-01 14:23:12.770

Now use the same code as below

declare @date datetime
set @date='20101201 14:23:12:77'
select @date as date

The result is
date
-----------------------
2010-12-01 14:23:12.077

Did you notice the change in the millisecond value. In the first example it is 770 and in second example it is 077?

It is because in the first example dot (.) is used as a millisecond separator whereas in the second example colon (:) is used as a millisecond separator. SQL Server always make the number of digits of milliseconds into 3.

If you use a dot as a separator and if the number of digits is less than three, the number is left justified and missing digits are filled with 0 on the right side. So 77 becomes 770

If you use a colon as a separator and if the number of digits is less than three, the number is right justified and missing digits are filled with 0 on the left side. So 77 becomes 077

So you need to be aware of this and as a best practice always use a dot as a separator for millisecond.