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

Advertisement

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)

CONVERT_TO_DATE function

Often I see people asking this question “Sometimes clients give date values as integers, float or formatted string values. How should we handle this and store them properly and query on it?”

For example October 04, 2010 can be expressed one of the following ways (the number format is dmy)

04102010
04102010.0
041010
4102010
04-oct-2010
04/10/2010
04.10.2010
Oct 4, 2010

In this case the correct way is to convert all of these into proper DATE value and store/use them. You can use the following function

Create function convert_to_date (@date_val varchar(25))
returns datetime
as Begin

Select @date_val=
case when @date_val like '%.0%' then substring(@date_val,1,charindex('.',@date_val)-1)
else @date_val
      
end
return
cast(
      case when @date_val like '%[a-zA-Z-/]%' then
            
case when ISDATE(@date_val)=1 then @date_val else NULL end
      
when len(@date_val)=8 then
            right(@date_val,4)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
      
when len(@date_val)=7 then
            right(@date_val,4)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
      
when len(@date_val)=6 then
            case when right(@date_val,2)<50 then '20' else '19' end
            
+right(@date_val,2)+'-'+substring(@date_val,3,2)+'-'+left(@date_val,2)
     
when len(@date_val)=5 then
            
case when right(@date_val,2)<50 then '20' else '19' end
            
+right(@date_val,2)+'-'+substring(@date_val,2,2)+'-0'+left(@date_val,1)
else
            
case when ISDATE(@date_val)=1 then @date_val else NULL end
end
as datetime
)
END


Now execute and see how these are converted to proper date values

select
dbo.convert_to_date('04102010'),
dbo.convert_to_date('04102010.0'),
dbo.convert_to_date('041010'),
dbo.convert_to_date('4102010'),
dbo.convert_to_date('04-oct-2010'),
dbo.convert_to_date('10/04/2010'),
dbo.convert_to_date('10-04-2010'),
dbo.convert_to_date('Oct 4,2010'),
dbo.convert_to_date('04 oct ,2010'),
dbo.convert_to_date('October 04 2010')

 
All the above give the output 2010-10-04 00:00:00.000

So you can make use of this function to convert the string values into proper DATETIME value

Question of the month April 2015 – Why is extra comma giving error for table variable and not for temporary table?

Consider the following statement

CREATE TABLE #T (I INT,)

It will get executed without error

But when you use table variable as shown below

DECLARE @T TABLE(I INT,)

You get the following error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

Why is extra comma giving error for table variable and not for temporary table?

Sequence – Union / Union all

You have known the difference between UNION and UNION ALL. But there is similarity when used sequence. It is not possible to use them to combine sequence values.

Create the following sequence

CREATE SEQUENCE MYSEQ
AS INT
START WITH 1 INCREMENT BY 1

You get the sequence value using

SELECT NEXT VALUE FOR MYSEQ 

If you want to use UNION or UNION ALL combing different sequence values, it is not possible. Both the following codes when executed throws error

SELECT NEXT VALUE FOR MYSEQ
UNION
SELECT NEXT VALUE FOR MYSEQ 

SELECT NEXT VALUE FOR MYSEQ
UNION ALL
SELECT NEXT VALUE FOR MYSEQ 

There error is

Msg 11721, Level 15, State 1, Line 2

NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

Scripting out a DDL trigger

A DDL trigger helps you to monitor when a table is Created , altered or dropped. You can perform some auditing whenever these happen.

You can create a database scoped DDL trigger as shown below

create trigger ddl_trigger on database
for
drop_table,
alter_table
as
select
'access denied to alter/drop this table'
rollback

But you can not see the script of DDL trigger using SP_HELPTEXT like other DML triggers

When you execute the below

EXEC sp_helptext ddl_trigger

You get the following error


Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object 'ddl_trigger' does not exist in database 'test' or is invalid for this operation.

The other way is to make use of sys.triggers catalog view to identify and script out the definition using Object_definition function

select object_definition(object_id) as definition from sys.triggers
where parent_class=0

Ordering the result set randomly

Sometimes you may need to show some data randomly. You can use NEWID() function to get data randomly

Let us consider the following data

Create table #countries (country_name varchar(100), city_name varchar(100))

insert into #countries
select 'India', 'Chennai' union all
select 'India', 'Bangalore' union all
select 'India', 'New Delhi' union all
select 'India', 'Hyderabad' union all
select 'India', 'Mumbai' union all
select 'United States', 'New York' union all
select 'United States', 'Boston' union all
select 'United States', 'Miami' union all
select 'Canada', 'Toronto' union all
select 'Canada', 'North bay'

You can order the result set randomly using NEWID() function as shown below

1 Order entire result set randomly.

select country_name,city_name from #countries
order by newid()

 

The result is

country_name           city_name
-------------------    --------------------------
United States          New York
India                  Chennai
United States          Miami
India                  Bangalore
United States          Boston
Canada                 North bay
India                  Mumbai
India                  Hyderabad
Canada                 Toronto
India                  New Delhi

2 Order city name randomly for each country.

select country_name,city_name from #countries
order by country_name, newid()

The result is

country_name                 city_name
---------------------------  --------------------
Canada                       North bay
Canada                       Toronto
India                        Hyderabad
India                        Bangalore
India                        Mumbai
India                        Chennai
India                        New Delhi
United States                New York
United States                Boston
United States                Miami

3 Get one city randomly for each country.

select country_name,city_name from
(
select *,
row_number() over ( partition by country_name order by country_name, newid()) as sno
from #countries
) as t
where sno=1

There result is

country_name                 city_name
---------------------------  --------------------
Canada                       Toronto
India                        Chennai
United States                Miami

Total days in a Year

Pinal Dave, Blogger and Speaker, posted about “SQL SERVER – Trivia – Days in a Year“. There are two methods shown in the post to find out total number of days for a given year. Here is one more method

DECLARE @year AS INT
SET @year=2012
select 365+
case
when @year%400=0 then 1
when @year%100=0 then 0
when @year%4=0 then 1
else 0
end

The result is 366.

The logic is that every year has 365 days. We need to find if it is a Leap year. If it is add 1 to it