Many of SQL developers do not know that SQL Server supports temporary stored procedure like temporary tables. A simple example is
create procedure #temp
as
select 1 as number
Have you ever used this? What is the real time use case to have a temporary stored procedure?
Beware of implicit conversion
You need to know that SQL Server does implicit conversion based on the precedence of the data types involved in the arithmetic operations.
Let us consider the following example
declare @num1 int, @num2 int
select @num1=3,@num2=2
select @num1/@num2
As you see the result is 1 and not 1.5 as expected because the data type of both the operands is INTEGER which results to 1 by truncation. The proper way to avoid this is to convert one of the operand to float or decimal
declare @num1 int, @num2 int
select @num1=3,@num2=2
select cast(@num1 as decimal(16,2))/@num2
The result is 1.5000000000000
Another method is just to multiply one of the operands by 1.0
declare @num1 int, @num2 int
select @num1=3,@num2=2
select 1.0*@num1/@num2
So be aware of this and whenever you use division make sure to convert one of the operand to decimal
Formatted dates , alias name and order by clause
One of my friends told me that when he was trying to order by datetime values, the result is not in proper order.
Consider the following data
create table #orders (order_id int, order_date datetime, order_value decimal(12,2))
insert into #orders (order_id,order_date,order_value)
select 1,'20150107',200 union all
select 2,'20151110',1300 union all
select 3,'20140404',500 union all
select 4,'20150110',2000 union all
select 5,'20150318',450
Suppose you want to show formatted dates and order by the order_date column, you can use the following code
select order_id,convert(varchar(10),order_date,103) as order_date,order_value
from #orders
order by order_date
The following is the result
order_id order_date order_value ----------- ---------- ------------ 3 04/04/2014 500.00 1 07/01/2015 200.00 4 10/01/2015 2000.00 2 10/11/2015 1300.00 5 18/03/2015 450.00
If you look at the result you can notice that the formatted dates are not in proper order although you order it by order_date column which is of datetime datatype. So what is the problem? It is the problem with using same column alias name for formatted dates. If you use different alias name for formatted dates and order by order_date you will get result properly
select order_id,convert(varchar(10),order_date,103) as order_date_formatted,order_value
from #orders
order by order_date
The result is
order_id order_date_formatted order_value ----------- -------------------- -------------- 3 04/04/2014 500.00 1 07/01/2015 200.00 4 10/01/2015 2000.00 5 18/03/2015 450.00 2 10/11/2015 1300.00
Please note that formatting datetime values is not recommended as they become characters and not datetime values so that any datetime related calculations are not possible until you convert them back to datetime values.
If you still want to do format for display purpose, and order it by original datetime value, use different alias name
If do not want to use different alias name, you need to again convert it back to DATETIME data type
select order_id,convert(varchar(10),order_date,103) as order_date,order_value
from #orders
order by convert(datetime,order_date,103)
