Delete Duplicate Rows

You may like to try these methods.
1. Add an idenity Column to the table
Alter table yourtable Add myseq int identity(1,1)

2. Delete all the rows where rows are duplicate based on the condition
Delete from yourTable where Exists ( Select 1 from YourTable B where Yourtable.column1=B.column1 and Yourtable.column2=B.column2 and Yourtable.myseq < B.myseq )

Or simple
1. Select distinct * into newTable from YourTable
2. delete from Yourtable
or
truncate table Yourtable
3. insert into Yourtable select * from newTable

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

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.

Reporting Tool

I am looking for the development of a reporting tool (something like Crystal Reports). Can someone provide links and material which will help me in developing this tool. I wish to use SQL Standards for the queries that will be generated intermediately , so no cube , rollup

Trigger

Hi All,
While inserting date value I wish to take only date part So I tried this
Create Table JTrial ( XYZ int, d datetime default convert(varchar,getdate(),112) )

insert into JTrial(XYZ) values(1)
insert into JTrial(XYZ) values(2)
insert into JTrial(XYZ) values(3)
insert into JTrial(XYZ) values(4)
select * from JTrial
Is there any better alternative.
Check constraint like this check ( d = convert(varchar,d,112) ) Will not allow me to insert row insert into JTrial(XYZ,d) values(5,getdate()) -- Because here date has time part
Is writing A trigger better alternative ?