Clue: Anyone who begins by thinking "SQL Server doesn't have what Oracle has" is going to arrive at a solution that fails to make best use of SQL Server.  Simply add a column of datatype int and set the identity property to "Yes (not for replication)" and all the existing rows in the table will be given a row identifier.

>>>

create table test (name varchar(10))
insert into test values ('Kate')
insert into test values ('Kate')
insert into test values ('Sue')

-- Enterprise Manager is the easiest way to add a column, so add column ID using EM.  Datatype is int and property

create view testUniqueID as select name, min(Id) as minID
from test
group by name

delete test
from testuniqueID join test
on testuniqueID.name=test.name
where testuniqueID.minID < test.id

drop table test

drop view testUniqueID

>>>

Lesson: SQL Server does have a row identifier.  It stays out of the way unless you need it.  Anything Oracle does, SQL Server does more elegantly.

Didn't find this very helpful.

For very large tables, the cursor approach would be dreadfully slow.

There are other techniques that exist, such as using duplicate indexes with IGNORE_DUP_KEY option that would be better for very large tables.

Cheers,

Yeah I totally agree with SQL does things better than what Oracle does.My article is aimed at novice SQL developers who can find the script very convinent in situations wherein the data in a table is not very large and they can do data cleansing easily .

I thought the information was helpful as providing an example.  As to the performance, I try to look for ways to performing row oriented operations without cursors.  I would try to use new @table variable declaration instead for SQL 2000  and a temp table in 7.0.

Thanks for the the article

set nocount on
/* Script for Creation of Employee Table*/
CREATE TABLE [dbo].[#Employee] (
            [id] [int] NULL ,
            [name] [Varchar] (50)  NULL ,
            [salary] [Numeric](18, 2) NULL
  ON [PRIMARY]
GO
/*********************************************************/

/**********************************************************/
/* Script for Insertion of duplicate records to the Table */
Insert into #employee values (1,'Ram', 1000.00)
Insert into #employee values (1,'Ram', 1000.00)
Insert into #employee values (2,'Joe', 2000.00)
Insert into #employee values (2,'Joe', 1000.00)
Insert into #employee values (3,'Mary', 1000.00)
Insert into #employee values (4,'Julie', 5000.00)
Insert into #employee values (2,'Joe', 1000.00)
Insert into #employee values (1,'Ram', 1000.00)

select id,name,salary into #temp from #employee
group by id,name,salary
having count(*)>1

delete from e
from #employee e
  inner join #temp t on e.id=t.id and e.name=t.name and e.salary=t.salary

insert into #employee
select * from #temp

select * from #employee

drop table #Employee
drop table #temp

The best way of dealing with duplicates is to avoid them in the first place!  Design your databases right and you don't have to worry about it.  There is no logically sound reason to allow duplicates.

If you find yourself in a situation (not of your own making, of course) where you have to deal with them, I have found this method helpful:

CREATE TABLE dupes (col1 int, col2 int)

GO
INSERT INTO dupes VALUES (1,1)
INSERT INTO dupes VALUES (1,1)
INSERT INTO dupes VALUES (1,1)
INSERT INTO dupes VALUES (2,2)
INSERT INTO dupes VALUES (2,2)
INSERT INTO dupes VALUES (2,2)
INSERT INTO dupes VALUES (2,2)

GO

CREATE TABLE nodupes (col1 int, col2 int)

GO

CREATE UNIQUE CLUSTERED INDEX ix
ON nodupes(col1, col2)
WITH IGNORE_DUP_KEY

GO

INSERT INTO nodupes
SELECT * FROM dupes

GO

SELECT * FROM nodupes

This gives you the safety of being able to do some "sanity checks" before actually modifying data, and it generally performs much better than cursor based methods.