Remove duplicate records from a table in SQL server

Here I create a table and inserted some duplicate records for testing purpose.

create table temp(cust_id varchar(50))

insert into temp(cust_id)

values

('oms1'),

('oms2'),

('oms1'),

('oms2'),

('oms3')

select *from temp

Method 1[Using ROWCOUNT]: 
You can simply use ROWCOUNT to delete duplicate record.

      --deleting 1 by 1

set ROWCOUNT 1

DELETE from temp where cust_id='oms1'

SET ROWCOUNT 0

select *from temp

                  --cust_id

                  --oms2

                  --oms1

                  --oms2

                  --oms3

          

insert into temp (cust_id) values('oms1')

Deleting all duplicate rows 

SET NOCOUNT ON

SET ROWCOUNT 1

WHILE 1 = 1

   BEGIN

        DELETE   FROM temp WHERE cust_id IN (SELECT  cust_id FROM temp GROUP BY          cust_id HAVING  COUNT(*) > 1)

   IF @@Rowcount = 0

         BREAK ;

   END

SET ROWCOUNT 0   

select *from temp

Output:

                  --cust_id

                  --oms1

                  --oms2

                  --oms3

Method 2[Using TOP Clause]: 

--Remove 1 by 1

delete top(1) from temp where cust_id='oms1'   

select *from temp

Output:

                  --cust_id

                  --oms2

                  --oms1

                  --oms2

                  --oms3

                 

Method 3[With the help of Another Table]: 

If your data is small then you can use this way.Here I created a table with distinct records.Drop first one and rename second one.

SELECT DISTINCT cust_id

INTO    temp1

FROM    temp

GO

DROP TABLE temp

exec sp_rename 'temp1', 'temp'

select *from temp

Output:

                  --cust_id

                  --oms1

                  --oms2

                  --oms3

Method 4[Using ROW_NUMBER]:
It is very efficient way to achieve the same.]  

WITH  TEST

          AS (
             SELECT   cust_id

             , row_number() OVER ( PARTITION BY cust_id ORDER BY cust_id )                     AS rowcnt

             FROM  temp

             )

          DELETE  FROM TEST

          WHERE  rowcnt  > 1

   

select *from temp

Output:

                  --cust_id

                  --oms2

                  --oms1

                  --oms3