A modern business theme
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.
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
