Finding Duplicates in MS SQL Server

Here is a quick snippet on how to find duplicates:

SELECT a.[dr_id]

      ,a.[sch_type]

      ,COUNT(*) as '# records'

  FROM [LZ].[dbo].[lz_src_address1] a

  where a.sch_type = '23'

  group by a.dr_id, a.sch_type

  having COUNT(*) > 1

You only include the fields that you need to determine if its a duplicate and put them in the GROUP BY clausing.  the HAVING clause tells you if there is more than 1 record.

There are several ways to delete duplicates.  They way you choose will depend on if you have a unique key for each row.  If so, you can delete them with a single statement.  If not, you may need to use a cursor and/or temp tables.