Thursday, December 10, 2009

Using Xpaths in Sql server

When we were using Sql 2000, we had very less functionalities available for xml operations. After Sql 2005, xml operations are very easy and make it very simple to handle. We have new datatype XML which allows to do lot on XML on sql server side. I am just going to take a simple example of a table which will save the xml data and how to fetch using conditions.


Ok. Here is our table. Let us call it as Contents

CREATE TABLE [dbo].[Contents](
[ContentID] [int] NOT NULL,
[ContentData] [xml] NULL,
[ContentCreatedDate] [datetime] NULL
)


We have column ContentData which is of type xml. I am going to insert few records to this table now.

insert into Contents values (1,'<customer><info id="1" fname="Subbu" lname="P"> <address address1="#444 bangalore" phone="12345"></customer>',GETDATE())

insert into Contents values (2,'<customer><info id="2" fname="Ram" lname="T"> <address address1="#111 Mumbai" phone="12345"></customer>',GETDATE())

insert into Contents values (3,'<customer><info id="3" fname="Raj" lname="M"> <address address1="#222 Pune" phone="12345"></customer>',GETDATE())

insert into Contents values (4,'<customer><info id="4" fname="Ravi" lname="R"> <address address1="#333 Mysore" phone="12345"></customer>',GETDATE())

Now let’s do a select from Contents table. You will get below result set.


You can see all the data inserted and the xml. Now lets say you need to pick the data based on the xml value. Let’s consider you need to find all the Customers from Pune. How to retrieve the data?


Select *,ContentData.value('(/Customer/Address/@Address1)[1]','varchar(20)') as Address from Contents where ContentData.value('(/Customer/Address/@Address1)[1]','varchar(20)') like '%Pune'

So here is our result set

We have passed the Xpath /Customer/Address/@Address1 to the value method which will process the xml and provide the result set. The value method takes two parameters.

With XML, we can also pass information between sps without worrying about the paramenters which gives lots of flexibility on design. I hope this small piece gives a brief idea on how to use xml and xpaths on your sql.

Tuesday, December 8, 2009

How to delete duplicate records on table without primary key

Deleting from the table which has a primary key is straight forward. If we have to delete a record from a table which does not have any primary key, then how do we delete it?
Lets take an example of table


CREATE TABLE [dbo].[Employee](
[Name] [varchar](50) NULL,
[DeptId] [int] NULL
)

Now I will insert few records to this table


Insert into Employee values ('User1',1)
Insert into Employee values ('User2',1)
Insert into Employee values ('User1',1)

So now we have 2 records with User1 and one record with User2. Lets try to delete the User1 from above table using below query

delete from Employee where Name='User1'

(2 row(s) affected)

But I just wanted to delete only one record. Here is how we can do it


SET ROWCOUNT 1
delete from Employee where Name='User1'
SET ROWCOUNT 0

(1 row(s) affected)

SET ROWCOUNT Causes SQL Server to stop processing the query after the specified number of rows are returned. For more details you can check http://technet.microsoft.com/en-us/library/ms188774.aspx


In Sql 2005 and above we can also use Top 1 in our Delete query.

Monday, December 7, 2009

IDENT_CURRENT and SCOPE_IDENTITY

IDENT_CURRENT is new built in function introduced in SQl 2005. By looking at the function name it looks very similar to SCOPE_IDENTITY.

I had one of the sp which was using IDENT_CURRENT and it was all working fine till I came across a problem which was reported last week. The sp which was returning Identity value had returned same idenity value for 2 sessions which occurred at the same time. Yes. the IDENT_CURRENT considers only last identity for the specific table in any session and any scope.

So if you have stored procedure running different sessions which is returning IDENT_CURRENT, there is a chance that you might get the latest inserted identity value from another session.

Here is the exact definition and difference between SCOPE_IDENTITY, IDENT_CURRENT, @@IDENTITY

•IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

•@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

•SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

So be careful when you use above functions. If you need a identity of the current scope in current session, you should use SCOPE_IDENTITY. If you need latest identity of a specified table no matter what is the session then you should use the IDENT_CURRENT

Thursday, December 3, 2009

Thread safe Singleton

I was just wondering if the singleton classes are threadsafe. I found some explanations how to make it threadsafe. Here are my findings

Let’s take an example of singleton class

class MyDocument
{
private static MyDocument _instance;
protected MyDocument()
{
}

public static MyDocument GetInstance()
{
if (_instance == null)
{

_instance = new MyDocument();
}
return _instance;
}

public string GetData
{
get
{
return "From Singleton class";
}
}

}

Now my above class will work fine. Can you guess what might happen if we use this on multi threading? While one thread is processing on _instance = new MyDocument();
Another thread can be on if (_instance == null) which is still true, so there is chance of getting two instances. So how to resolve this?

Here are some of the steps we can use

Modify your code something like this

class MyDocument
{
private static MyDocument _instance = new MyDocument();
protected MyDocument()
{
}

public static MyDocument GetInstance()
{

return _instance;
}

public string GetData
{
get
{
return "From Singleton class";
}
}

}

So now we are not checking for null and returning only the object. One more option that can be used as below


class MyDocument
{
private static MyDocument _instance;

private static object objectLock = new object();

protected MyDocument()
{
}

public static MyDocument GetInstance()
{
if (_instance == null)
{
lock (objectLock)
{
if (_instance == null)
{

_instance = new MyDocument();
}
}
}
return _instance;
}

public string GetData
{
get
{
return "From Singleton class";
}
}

}


In the above class, the _instance object creation part is put inside lock.The lock keyword marks a statement block as a critical section by obtaining the mutual-exclusion lock for a given object, executing a statement, and then releasing the lock. For more info on Lock you can check http://msdn.microsoft.com/en-us/library/c5kehkcz(VS.80).aspx