Find and Remove Duplicate Rows from a SQL Server Table

Delete Record from table without creating Temp table.

Step 1: Create Table

CREATE TABLE [dbo].[Student](
[ID] [int] NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL
) ON [PRIMARY]
GO

Step 2: Insert Records in the table

insert into Student values (1,'Jack','california')
insert into Student values(1,'Jack','california')
insert into Student values(1,'Jack','california')
insert into Student values(2,'Lira','texas')
insert into Student values(2,'Lira','texas')

ID Name City

1 Jack california

1 Jack california

1 Jack california

2 Lira texas

2 Lira texas

Step 3: Execute Query

;

--Ensure that any immediately preceding statement is terminated with a semicolon above

WITH cte

     AS (SELECT ROW_NUMBER() OVER (PARTITION BY ID, Name, City 

                                       ORDER BY ( SELECT 0)) RN

         FROM   Student)

delete FROM cte

WHERE  RN > 1;

Result:

ID Name City

1 Jack      california

2 Lira      texas