Beginning SQL : Delete duplicate records from table

There are a situation in which you want to delete duplicate records from table.

Here are example in which i created on temp table and add four columns,
in this three columns are data columns and one column is ID column.

There is a situation in which duplicate records are inserted in tables only ID column data is unique and other columns data are repeated
in this case you can use following query :

Create Temp table and insert some dummy records :

Create table #Test (colA int not null, colB int not null, colC int not null, id int not null identity) on [Primary]
GO
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)

INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)

INSERT INTO #Test (colA,colB,colC) VALUES (4,5,6)
GO

Before Execute delete query table data looks like this :

Select * from #Test
GO

Output : 

Here are query to delete duplicate records :

Delete from #Test where id <
(Select Max(id) from #Test t where #Test.colA = t.colA and #Test.colB = t.colB and #Test.colC = t.colC)
GO

After Execute delete query table data looks like this :

Select * from #Test
GO

Output : 

This query is really very useful at a time of data migration.