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?
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.
Please note that this post is available now at https://exploresql.com/2015/08/18/multipurpose-case-expression/
Madhivanan's SQL blog 