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?

Advertisements

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.

Multipurpose CASE expression

CASE expression evaluates the conditions given in WHEN clause and return the result of matched condition. It is very handy and useful in certain scenarios. We can see how it can be effectively used in many cases.

1 Simple condition

declare @gender char(1)
select @gender='M'
select case when @gender='M' then 'Male' when @gender='F' then 'Female' end as gender

The result is

gender
------
Male

2 Conditional aggregation

Using CASE expression you can conditional do some aggregations like SUM, COUNT etc. This is particularly using if you want to PIVOT the data.

Consider the following data and suppose you want to find out male and females counts as separate columns, you can use as shown below

declare @employee table(emp_id int, emp_name varchar(100), gender char(1))
insert into @employee
select 1,'Sankar','M' union all
select 2,'John','M' union all
select 3,'sumithra','F' union all
select 4,'Nilesh','M'


select
sum
(case when gender='M' then 1 else 0 end) as male_count,
sum(case when gender='F' then 1 else 0 end) as female_count
from
@employee


The result is

male_count female_count
----------- ------------
3           1

3 Conditional ordering

Suppose you want to order the resultset in such a way that particular number comes first followed by others in ascending order. You can do this via CASE expression in the ORDER BY clause. The flowing always displays 4 on top followed by other numbers ordered in ascending order.

declare @t table(number int)
insert into @t
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5

select number from @t order by case when number=4 then 1 else 2 end, number

The result is

number
-----------
4
1
2
3
5

So CASE expression can be effectively used in many cases