Eliminación de registros duplicados en SQLServer

Eliminación de registros duplicados en SQLServer
Microsoft SQL Server
Carlos 11 Agosto, 2010 - 10:27
SQL

Tip de MSSQLTips para eliminar filas duplicadas en una tabla sin clave primaria, utilizando SET ROWCOUNT para limitar el número de filas afectadas por una consulta:

SELECT * FROM dbo.duplicateTest;
SET ROWCOUNT 1;
DELETE FROM dbo.duplicateTest WHERE ID = 1;
SET ROWCOUNT 0;

 

Otra propuesta más clásica sacada de un grupo de Linkedin (cuidado si la tabla es grande):

DELETE FROM TestTable WHERE
EXISTS
(SELECT Sid FROM TestTable T WHERE T.Tname = TestTable.Tname AND I.SId < TestTable.SId)

Subido por Anonimo (no verificado) el 16 Noviembre, 2014 - 15:47

Hola

Creo que es más escalable la opcion de Pinal Dave (entre otros) con un bloque CTE.

La pk yo aconsejaría crearla para el borrado, seria mas robusto.

Suponiendo dicha tabla y un collate CI:

Create table tabla(
 Id bigint identity primary key
 , nombre nvarchar(255)
)
Insert into tabla (nombre) values
('Hola'),('hola'),('HOLA')
;with duplicado as (
         select 
                  row_number() over ( partition by nombre order by id asc) fila
                 ,id
         From tabla)
Delete from duplicado where fila > 1

 

Muy útil para eliminar registros duplicados en tablas de grand volúmen y dentro de un WHILE con EXISTS y TOP en el CTE evitamos bloqueos, dura menos el lock table y reducimos el uso de transaccional y tempdb.

A partir de SQL Server 2005, eso si.

Subido por Carlos el 20 Noviembre, 2014 - 09:20

Muy buena solución para eliminar duplicados, gracias por compartirla.

Además acabo de encontrar este aviso en la documentación de SQLServer sobre SET ROWCOUNT:

Importante !!

La utilización de SET ROWCOUNT no afectará a las instrucciones DELETE, INSERT ni UPDATE en una futura versión de SQL Server. Evite utilizar SET ROWCOUNT con las instrucciones DELETE, INSERT y UPDATE en los nuevos trabajos de desarrollo, y modifique las aplicaciones que la utilizan en la actualidad. Para conseguir un comportamiento similar, utilice la sintaxis TOP. Para obtener más información, vea TOP (Transact-SQL).

Eso significa que el truco de utilizar el SET ROWCOUNT 1 en el DELETE dentro de poco ya no funcionará, así que mejor nos acostumbramos a hacerlo de otra manera, como con este bloque CTE (Expresión de Tabla Común) o, para decirlo de manera mas llana, 'utilizando un ;with'