A beauty of Set Based theory in SQL Server

Thursday, November 26, 2009 |

I always prefer Set Based theory versus step-by-step procedural programming. Today I am going to show you how efficiently it works. Suppose you have two variable containing one value each. You want to swap it so there are few different ways to do so but the most common way is to do it with the help of third variable, pseudo code would be something like:

VAR v1,v2,v3;
v1=1;
v2=2;
v3=v1;
v1=v2;
v2=v3
print v1,v2,v3

So, this is something procedural approach, now let me show the way in SQL Server which won’t use third container and will swap value without any external logic.

--create table for demonstration
create table SetBaseTest
(
name1 varchar(10),
name2 varchar(10)
)
GO

--insert some records for testing puropse
insert into SetBaseTest
select 'ritesh','shah' union all
select 'rajan','shah'
GO

--look at the record set
SELECT * FROM SetBaseTest
GO

--execute update and swap value
--you might think that name2 will be assign to name1
--so now name1 should be name2 and then
--reassign name1 (which itself contain value of name2 right now)
--to name2, so both column should be same.
--but no, you are WRONG, look at the result after UPDATE statement
UPDATE SetBaseTest SET name1=name2,name2=name1
GO

SELECT * FROM SetBaseTest
GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

3 comments:

Anonymous said...

wonderful observation. I liked this.

Vijaya Kadiyala said...

This is really good one.. and the right candidate to ask in the interviews...
Thanks -- Vijaya kadiyala
www.DotNetVJ.com

Ritesh Shah said...

Hi Vijay, great to see your post here in my blog. Its an honor. thanks.