how to find duplicated rows in a table

use tempdb



/*--------------------------------------------------------------------*\

 This example show how to find duplicated rows in a table
 To have a look, just paste the code into Query Analyzer

 Marcello Miorelli 15/05/08 Thursday
\*--------------------------------------------------------------------*/


if exists (select * from dbo.sysobjects

 where id = object_id(N't1')

 and OBJECTPROPERTY(id, N'IsUserTable') = 1) begin



 drop table t1



end /*if*/


--the original table

create table t1 (



y integer,

x char(15)



)

go



insert into t1 (y,x) values (1,'krishna')

insert into t1 (y,x) values (2,'govinda')

insert into t1 (y,x) values (3,'madhava')

insert into t1 (y,x) values (4,'nrisimha')

insert into t1 (y,x) values (1,'krishna')





--adding an identity row to table

ALTER TABLE t1 add rowid int identity(1,1)

go



-- i will show the duplicate y s



select t.*

 from t1 t

 inner join t1 m

 on t.y = m.y

 and t.rowid <> m.rowid

go



--then I can remove the identity row

ALTER TABLE t1 DROP COLUMN rowid

go





alter table t1

add z integer not null default 0

go