NULL values in Unique keys

Recently I posted a post at SQLTeam.com

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=145497

asking about NULL values in Unique keys.

Cleary BOL states

One of the differences between a primary key constraint and a unique key constraint is that null values are allowed in a column with a unique constraint.

But it never states how many NULL values it allows.
Just if you read at my above post at SQLTeam.com you will get nowhere.
Man talk about confusion Microsoft as usual is leading the way.

Lets play with XML

Below is a nested XML which contains Customer info and Order Details.I need to get the Order Details for each customer in the XML.One way to do it was using OPENXML and sp_xml_preparedocument.

With new enhancements for XML datatype from SQL 2005 and beyond it can be easily done by using Xquery.This is how it can be done.

declare @xmlData XML
set @XMLData =
'<root><row>
<custid>1</custid>
<custname>name1</custname>
<order>
<orderdetail orderid = "1" orderdate = "2008-01-01" />
<orderdetail orderid = "2" orderdate = "2008-01-02" />
</order>
</row>
<row>
<custid>2</custid>
<custname>name2</custname>
<order>
<orderdetail orderid = "3" orderdate = "2008-01-03" />
<orderdetail orderid = "4" orderdate = "2008-01-04" />
</order>
</row>
</root> '

select @xmldata

select x.i.value('../../custid[1]','varchar(40)') as custid,
x.i.value('../../custname[1]','varchar(40)')as custname,
x.i.value('@orderid','int')as orderid,
x.i.value('@orderdate','date')as orderdate
from @xmldata.nodes('/root/row/order/orderdetail')x(i)

The resultset for the above query is this.

Getting numeric or character values from an alphanumeric string

Sometimes it becomes necessary to get all the numeric or all the character values from a alphanumeric string.
The idea in the solution I have posted is to convert all the characters from the string into rows in a derived table
using a look table @tbl & then concating the charater values excluding the numeric values in my example below.
If you need to get the numeric values from the string simply replace like '[a-z]' to like '[0-9]'

Declare @str As Varchar(50)
Select @str='klasdjfkl034ioerut'
Declare @value As Varchar(40)=''

Declare @tbl As Table(number Int Identity)
Insert Into @tbl Default Values
While SCOPE_IDENTITY()< len(@str)
Insert Into @tbl Default Values

Select @value=@value + '' + value From
(
Select number,SUBSTRING(@str,number,1)As value From @tbl Where number Between 1 And LEN(@str)
)t Where value like '[a-z]'

Select @value

Difference between 2 dates in Hours

Sometimes it is necessary to show the difference between 2 dates in hours.The query can be modified to be used as an function accepting 2 parametrs Date1 and Date2
The accuracy can be checked hereto compare the results. Here is the query

Declare @dt1 datetime='12-May-2010 09:00:00'
Declare @dt2 datetime='13-May-2010 04:01:00'

Select
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,@dt1,@dt2)/60),2)+ ':' +
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(mi,@dt1,@dt2)%60),2)+ ':' +
RIGHT('0'+CONVERT(varchar(2),DATEDIFF(SECOND,@dt1,@dt2)%60),2) As DiffInHours

Recently one of my friend had asked me a query by which he could get all such vendors
who sell all the ItemTypes present in the table.Of course for sake of illustration purpose I have de normalised the table as my blog is not for sake of teaching the fundamentals of database but rather to provide tweaks for non trivial problems a developer faces.
Here is the table structure

In the above condition the o/p should be vendor id 1 and 2
To my surprise on first glance I found the pivot query working much faster than other queries.
Will post more about the performance implications later.But as for now here is the query.

declare @tbl as table(vendor int,ItemType varchar(40))

insert into @tbl
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'a' union all
select 2,'b' union all
select 2,'c' union all
select 3,'c'

select * from @tbl

--Method 1
select vendor from
(
select vendor,ItemType from @tbl
)u
pivot
(max(ItemType) for ItemType in ([a],[b],[c]))v
where a is not null and b is not null and c is not null

--Method 2
select vendor from @tbl where ItemType in('a','b','c')
group by vendor
having COUNT(case ItemType when 3 then 1 else 0 end)=3

--Method 3
select distinct vendor from
(
select vendor,(select COUNT(distinct ItemType)as rid from @tbl t2 where t1.vendor=t2.vendor and t2.ItemType in ('a','b','c'))as rid
from @tbl t1
)T where T.rid=3

--Method 4
select vendor from @tbl where ItemType in ('a','b','c')
group by vendor
having COUNT(distinct ItemType)=3

Here I needed to get the sum of values for intervals of every 5 minutes.First thing that came to my mind was to use the evil "Cursors" to achieve it.But somehow I found a different way of doing it.Here is the sample data

The o/p should be 7,13 & 3 as slot for which I needed to take the sum of values was every 5 minutes.Here is the query.

declare @tbl as table(dt datetime,value int)
insert into @tbl
select '03-03-2009 10:15:10',5 union all
select '03-03-2009 10:17:10',2 union all
select '03-03-2009 10:22:25',3 union all
select '03-03-2009 10:24:00',10 union all
select '03-03-2009 10:29:00' ,3

select * from @tbl

declare @slot int=5
select SUM(value)as SumOfSlots from @tbl group by DATEPART(minute,dt)/@slot*@slot

You can set the value of the @slot variable to value in minutes for which you want to have the Sum.

Recently I had a very non trivial situation where I had to display the max value in multiple columns of a table.I will like to illustrate a very simple example
Suppose I have a table with 3 columns namely color1,color2,color3 with different color values in all the column.My task is to display the color which is the most repeating in all the three columns for each id.With the below table structure and data the o/p for id 1 should be 'red' and for id 2 should be 'green'

So here is the query on how to do it.

declare @tbl as table(id int,color1 varchar(40),color2 varchar(40),color3 varchar(40))
insert into @tbl
select 1,'red','red','blue' union all
select 2,'green','red','green'

select T2.id,T1.color1,T1.color2,T1.color3,T2.col as MaxColor from @tbl T1 inner join
(
select *,ROW_NUMBER()over(PARTITION by id order by id )as rid from
(select * from @tbl)u
unpivot
(col for columns in (color1,color2,color3))v
)T2 on T1.id=T2.id where T2.rid=1

The trick here is to use unpivot & then by using ranking function assign an id which has the maximum number of color values for each distinct id & selecting the colors having rowid as 1.

Sort on Alphanumeric values

I have seen so many post where developers face problem in sorting the data on alphanumeric values.The most simpls way to do is to use Isnumeric function (applicable for SQL 2005 and beyond).
Here is one of the solutions.

Declare @tbl as table(id int identity(1,1),value varchar(10))
insert into @tbl
select '123AS' union all
select '1245' union all
select 'ABC123AS' union all
select 'WE123AS' union all
select 'AS123AS' union all
select '123AS' union all
select '1'

select * from @tbl
order by isnumeric(value)desc,len(value)

The order will be descending on pure numeric values .

If you need it to be ascending then simply use

select * from @tbl
order by isnumeric(value),len(value) desc

My name is Sachin Nandanwar. I like to think I speak for the average developer. Here's a few facts about me:

• My current focus is in Microsoft .NET, primarily in Web development using ASP.NET but currently putting more of my effort into database developement.
• My database of choice is SQL Server 2005 & 2008. I also know Microsoft Access very well and have dabbled in MySQL. The first database I used on the PC was R:Base.
• I program in both C# and VB. I also programmed in VB 6.0 and JavaScript.
  
• I dont beleive in degrees because they hardly teach you anything practical in life.But still I have a bachelors degree in Mathematcis. I was born in Amchi Mumbai.
• The only website I really really love to haunt is www.sqlteam.com.
• Like body building & was a fitness trainer before fully concentrating into IT.
• I am married to a beautiful wife
• If God willing hope to write a book on Database Development and become a SQL Server MVP.

Thanks for reading my blog.

Sachin Nandanwar