Code Project

Link Unit

Tuesday, May 16, 2006

Union to make missing Operator INTERSECT,MINUS

SQL Server misses MINUS and INTERSECT , whereas in Oracle they are provided as part of the parcel.

But we can create the same effect of these operators using UNION and group by

Let us say we have two sets(tables) with same structure

StageShow(table)
RollNo int references Student
...
&
Sports(table)
RollNo int references Student
...

StageShow
1
2
5
....

Sports
1
3
5
.....

In Oracle Finding out MINUS and INTERSECT is easy by using the respective operator
i.e All students participating in both activities (although it can be a single activity table ,but just to show how we can implement I had done splitting )

Select Rollno from StageShow
Intersect
Select Rollno from Sports


Similarly we can say MINUS as
Select Rollno from StageShow
MINUS
Select Rollno from Sports

Now how to do it in SQL Server

How to implement INTERSECT ?

Select Rollno From (
Select 1 AS dummy,Rollno From StageShow
Union ALL
Select 2 AS dummy,Rollno From Sports
) X group By Rollno having count(*)=2

How to implement MINUS (A-B) Rows in A not in B?

Select Rollno From (
Select 1 AS dummy,Rollno From StageShow
Union ALL
Select 2 AS dummy,Rollno From Sports
) X group By Rollno having Max(Dummy)=1

Friday, May 12, 2006

Is Using Cursor Good?

We cannot avoid the usage of cursor in certain cases , but most of the
time appropriate where clauses can be used and hence cursor can be
avoided.
Let us take a simple example where a person's salary is to be increased
by 10% if his salary is <1000

and decrease by 10% if salary >=1000

If we use these two statements

Update emp set salary = salary *1.10 where salary <1000

Update emp set salary = salary *0.90 where salary >=1000

It will correctly update the salary of most but the one which fall in
the range ( 900-1100) would be afftected
So what normal procedural approach suugests. declare a cursor open cursor fetch row by row till @@fetch_status = 0 if salary <1000 then increase otherwise decrease for each row. close cursor
But with the introduction of 'case when' expression we can use
something like this
update emp set salary = salary * (case when salary<1000 then 1.10 else
0.90 end)
The only place I found it useful is where we need to do some DBA
activity. e.g truncate all tables (although it can be done with single
undocumented procedure 'sp_MSforeachtable' in SQL Server )
Exec sp_MSforeachtable 'truncate table ?' -- this will truncate all the
tables.
Cursor is like an evil beast we must learn to live without. Cursor is
not meant for DML activities. It should be used for DBA activities
only.

Conclusion: One must use the power given by the features not abuse them. Overusing
cursors for all activities means abusing the feature given.