Cuando diseñamos objetos en SQL Server, debemos seguir ciertas mejores prácticas. Por ejemplo, una tabla debe tener claves primarias, columnas de identidad, índices agrupados y no agrupados, integridad de los datos y limitaciones de rendimiento. La tabla de SQL Server no debe contener filas duplicadas de acuerdo con las mejores prácticas de diseño de bases de datos. Sin embargo, a veces es necesario tratar con bases de datos donde estas reglas no se siguen o donde se pueden hacer excepciones cuando estas reglas se eluden intencionalmente. Aunque seguimos las mejores prácticas, es posible que enfrentemos problemas como colas duplicadas.
Por ejemplo, también podríamos obtener este tipo de datos al importar tablas intermedias y nos gustaría eliminar las filas redundantes antes de agregarlas a las tablas de producción. Además, no debemos renunciar a la posibilidad de duplicar filas porque la información duplicada permite el manejo múltiple de solicitudes, la presentación de resultados incorrectos y mucho más. Sin embargo, si ya tenemos filas duplicadas en la columna, debemos seguir métodos específicos para limpiar los datos duplicados. Veamos algunas formas en este artículo para eliminar la duplicación de datos.
La tabla que contiene las filas duplicadas.
¿Cómo se eliminan las filas duplicadas de una tabla de SQL Server?
Hay varias formas en SQL Server de manejar registros duplicados en una tabla según circunstancias particulares como:
Eliminar filas duplicadas de una única tabla de índice de SQL Server
Puede utilizar el índice para ordenar los datos duplicados en tablas de índice únicas y luego eliminar los registros duplicados. Primero, necesitamos crear una base de datos llamada «test_base», y luego crear una tabla «Empleado» con un índice único usando el siguiente código.
Utiliza el maestro GO CREAR BASE DE DATOS test_database GO USE [test_database] GO CREAR MESA Empleado ( NO ES UNA IDENTIDAD NULA (1,1), INT, [Nombre] varchar(200), [email] varchar (250) NULL, Varchar (250) NULL, [dirección] varchar(500) NULL CLAVE PRIMARIA CONSTRAINT CLAVE PRIMARIA ID )
El resultado será el siguiente.
Creando la tabla «Empleado»
Ahora inserte los datos en la tabla. También insertaremos filas duplicadas. El «Dep_ID» 003,005 y 006 son filas duplicadas con datos similares en todos los campos excepto la columna de identidad con un índice de clave único. Ejecute el siguiente código.
USE [test_database] GO INSERTAR EN EL EMPLEADO(Dep_ID,Nombre,email,ciudad,dirección) VALORES (001, $0027Aaaronboy Gutiérrez$0027, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Or 97124$0027), (002, $0027Aabdi Maghsoudi$0027, [email protected]$0027,$0027BRENTWOOD$0027,$0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni$0027, [email protected]$0027,$0027HYATTSVILLE$0027,$00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni$0027, [email protected]$0027,$0027HYATTSVILLE$0027,$00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mughal$0027, [email protected]$0027,$0027OMAHA$0027,$00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo$0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St St Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo$0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St St Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman$0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027); SELECCIONE * DE EMPLEADO
El resultado será el siguiente.
Inserte datos en la tabla llamada «Empleado» y obtenga datos de la misma tabla.
Ahora encuentre el número de filas en la tabla ejecutando el siguiente código. La función de conteo
SELECCIONAR DEP_ID,Nombre,email,ciudad,direccion,CUENTA(*) COMO cuenta_de_filas_duplicadas DEL EMPLEADO GRUPO POR DEP_ID,Nombre,email,ciudad,dirección
contará el número de filas.
El resultado será el siguiente. Las filas no (3, 4), (6, 7), (8, 9) resaltadas en el cuadro rojo son duplicados.
Esta figura resalta filas duplicadas que tienen row_no mayor que 1
Nuestra tarea es reforzar la singularidad eliminando duplicados de columnas duplicadas. Es un poco más fácil eliminar valores duplicados de la tabla con un índice único que eliminar las filas de una tabla sin él. Aquí hay dos métodos para lograrlo. El primer método le da filas duplicadas de la tabla usando la función «row_number ()», mientras que el segundo método usa la función «NOT IN». Estos dos métodos tienen su propio costo que se discutirá más adelante.
seleccione * de (SELECCIONE Identificación, nombre, correo electrónico, ciudad, dirección, ROW_NUMBER() SOBRE ( PARTICIPACIÓN DE Dep_ID,Nombre,email,ciudad,dirección ORDENAR POR Dep_ID,Nombre,email,ciudad,dirección ) row_no DE BASE DE DATOS DE PRUEBAS.dbo.Empleado) x donde row_no>1
Método 1: seleccionar registros duplicados mediante la función «ROW_NUMBER ()»
SELECT * FROM test_database.dbo.Employee DONDE ID NO ESTÁ (SELECCIONE MAX(ID) FROM test_database.dbo.Empleado GRUPO POR DEP_ID, Nombre, email, ciudad, dirección)
Método 2: selección de registros duplicados mediante la función «NO EN ()»
Ejecute el código anterior y verá el siguiente resultado. Ambos métodos dan el mismo resultado, pero tienen costos diferentes.
Selección de filas duplicadas de la tabla denominada «Empleado» utilizando el método 1 y 2 respectivamente
Ahora eliminaremos las filas duplicadas seleccionadas anteriormente usando «CTE» usando el siguiente código. El siguiente código selecciona filas duplicadas para eliminarlas mediante la función «ROW_NUMBER ()».
CON cte_delete AS (
SELECCIONE
Identificación, nombre, correo electrónico, ciudad, dirección,
ROW_NUMBER() SOBRE (
PARTICIPACIÓN DE
Dep_ID,Nombre,email,ciudad,dirección
ORDENAR POR
Dep_ID,Nombre,email,ciudad,dirección
) row_no
DE
test_database.dbo.Empleado
)
BORRAR DE cte_borrar DONDE fila_no> 1;
Método 1: eliminar registros duplicados mediante la función «ROW_NUMBER ()»
El resultado será el siguiente.
Eliminando registros duplicados de la tabla indexada usando la función «ROW_NUMBER ()
Método 2: eliminar registros duplicados mediante la función «NO EN ()»
USE [test_database] GO truncar la tabla test_database.dbo.Empleado INSERTAR EN EL EMPLEADO(Dep_ID,Nombre,email,ciudad,dirección) VALORES (001, $0027Aaaronboy Gutiérrez$0027, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Or 97124$0027), (002, $0027Aabdi Maghsoudi$0027, [email protected]$0027,$0027BRENTWOOD$0027,$0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni$0027, [email protected]$0027,$0027HYATTSVILLE$0027,$00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni$0027, [email protected]$0027,$0027HYATTSVILLE$0027,$00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mughal$0027, [email protected]$0027,$0027OMAHA$0027,$00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo$0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St St Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo$0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St St Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman$0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027); SELECCIONE * DE EMPLEADO
Ahora, para probar otro método, necesitamos truncar la tabla que eliminará todas las filas de la tabla. Luego, el comando de inserción agregará valores a la tabla. Ejecute el siguiente código ahora.
El resultado será el que se indica a continuación.
Inserte datos en la tabla llamada «Empleado» y obtenga datos de la misma tabla.
Borrar de base de datos de pruebas.dbo.Empleado DONDE ID NO ESTÁ (SELECCIONE MAX(ID) FROM test_database.dbo.Empleado GRUPO POR DEP_ID, Nombre, email, ciudad, dirección)
Ejecute el siguiente código para eliminar todas las filas duplicadas de la tabla «Empleado».
El resultado será el siguiente.
Elimine todas las filas duplicadas de la tabla indexada denominada «Empleado
Plan de ejecución y costo de consulta para eliminar filas duplicadas de la tabla indexada:
Ahora tenemos que comprobar qué método será más rentable y requerirá menos recursos. Seleccione el código y haga clic en el plan de ejecución. Aparecerá la siguiente pantalla mostrando todos los planes de ejecución junto con el porcentaje de costo.
Podemos ver que el método 1 «eliminar registros duplicados usando la función» ROW_NUMBER () «tiene un costo del 33% y el método 2» eliminar registros duplicados usando la función NOT IN () «tiene un costo del 67%. Por lo tanto, el método uno es el más rentable en comparación con el método dos.
El método 1 tiene un costo del 33% y el método 2 tiene un costo del 67%, lo que revela que el método 1 es más rentable.
Elimine duplicados de una tabla de SQL Server sin un índice único:
Es un poco más difícil eliminar filas o tablas duplicadas sin un índice único. En este escenario, el uso de una expresión de tabla común (CTE) y la función NÚMERO DE FILA () nos ayuda a eliminar registros duplicados. Para eliminar duplicados de la tabla sin un índice único, necesitamos generar identificadores de fila únicos.
USE [test_database] GO PONER ANSI_NULOS EN GO PONER QUOTED_IDENTIFIER EN GO CREAR TABLA [dbo]. [Empleado sin índice]( [Dep_ID] [int] NULL, [Nombre] [varchar](200) NULL, [email] [varchar](250) NULL, NULL, [dirección] [varchar](500) NULL, ) GO
Ejecute el siguiente código para crear la tabla sin un índice único.
El resultado será el siguiente.
Creando la tabla llamada «Employee_with_no_index» sin un índice único
USE [test_database] GO INSERTAR EN EL EMPLEADO_CON_SIN_INDICE(Dep_ID,Nombre,email,ciudad,direccion) VALORES (001, $0027Aaaronboy Gutiérrez$0027, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Or 97124$0027), (002, $0027Aabdi Maghsoudi$0027, [email protected]$0027,$0027BRENTWOOD$0027,$0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni$0027, [email protected]$0027,$0027HYATTSVILLE$0027,$00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni$0027, [email protected]$0027,$0027HYATTSVILLE$0027,$00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mughal$0027, [email protected]$0027,$0027OMAHA$0027,$00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo$0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St St Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo$0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St St Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman$0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027); SELECT * FROM Employee_with_out_index
Ahora inserte los registros en la tabla creada llamada «Employee_with_out_index» ejecutando el siguiente código.
El resultado será el siguiente.
Inserte datos en la tabla con un índice de salida llamado «Employee_with_no_index»
Método 1: eliminar filas duplicadas de una tabla mediante la función «ROW_NUMBER ()» y UNIR.
CON temp_tablr_con_row_ids AS ( SELECCIONAR NÚMERO DE FILA() SOBRE (ORDENAR POR DEP_ID,NOMBRE,EMAIL,CIUDAD,DIRECCIÓN) COMO número de fila, Dep_ID,Nombre,email,ciudad,dirección FROM test_database.dbo.Employee_with_out_index ) BORRAR una de temp_tablr_ con_row_ids a WHERE row_no < (SELECT MAX(row_no) FROM temp_tablr_with_row_ids i WHERE a.Dep_ID=i.Dep_ID y a.Name=i.Name y a.email=i.email y a.city=i.city y a.address=i.address GRUPO POR DEP_ID, Nombre, email, ciudad, dirección)
Ejecute el siguiente código que usa la función ROW_NUMBER () y JOIN para eliminar las filas duplicadas de la tabla sin índice. Primero cree una identidad única para asignar row_no a todas las filas y mantenga solo una fila eliminando los duplicados.
El resultado será el siguiente.
Eliminando filas duplicadas de una tabla sin un índice usando la función «ROW_NUMBER ()» y JOINS
Método 2: eliminar filas duplicadas de una tabla mediante la función «ROW_NUMBER ()» y PARTICIÓN POR.
trunca la tabla Employee_with_out_index INSERTAR EN EL EMPLEADO_CON_SIN_INDICE(Dep_ID,Nombre,email,ciudad,direccion) VALORES (001, $0027Aaaronboy Gutiérrez$0027, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Or 97124$0027), (002, $0027Aabdi Maghsoudi$0027, [email protected]$0027,$0027BRENTWOOD$0027,$0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni$0027, [email protected]$0027,$0027HYATTSVILLE$0027,$00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni$0027, [email protected]$0027,$0027HYATTSVILLE$0027,$00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Mughal$0027, [email protected]$0027,$0027OMAHA$0027,$00272975 Crouse Lane Burlington Nc 272150000$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (005, $0027Aabram Howell$0027, [email protected]$0027,$0027DILLSBURG$0027,$0027868 York Ave Atlanta Ga 303102750$0027), (006, $0027Humbaerto Acevedo$0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St St Paul Mn 551063852$0027), (006, $0027Humbaerto Acevedo$0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7th St St Paul Mn 551063852$0027), (007, $0027Pilar Ackaerman$0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027);
Ahora, en este método, estamos usando la función ROW_NUMBER junto con la partición por cláusula para asignar row_no a todas las filas y luego eliminar los duplicados. En primer lugar, necesitamos truncar la misma tabla que hemos creado anteriormente para que todos los datos se eliminen de la tabla. Luego inserte los registros en la tabla, incluidos los registros duplicados. La tercera consulta eliminará las filas duplicadas de la tabla denominada «Employee_with_no_index».
; CON temp_tablr_with_row_ids AS ( SELECCIONE EL NÚMERO DE RUTA() SOBRE (PARTICIPACIÓN POR DEP_ID,NOMBRE,EMAIL,CIUDAD,DIRECCIÓN ORDEN POR Dep_ID,Nombre,email,ciudad,dirección) AS row_no, Dep_ID,Nombre,email,ciudad,dirección FROM Empleado_sin_indexar )
Selección de registros duplicados en la tabla de temperatura.
DELETE a FROM temp_tablr_with_row_ids a WHERE row_no> 1
Eliminación de registros duplicados de la tabla de temperatura.
El resultado será el siguiente.
Truncamiento, inserción, eliminación de filas duplicadas de una tabla sin índice y selección de los registros resultantes.
Además, necesitamos conocer los costos de ejecución de consultas para comprender qué es una solución optimizada. Por lo tanto, debe seleccionar todas las consultas relevantes y hacer clic en el plan de ejecución. La siguiente imagen muestra el plan de ejecución de consultas junto con el costo de ejecución. Las consultas de eliminación están resaltadas en el cuadro rojo. La primera consulta que usa «ROW_NUMBER ()» y la cláusula JOIN tiene un costo de ejecución del 56%, mientras que la segunda consulta usa «ROW_NUMBER ()» y «PARTITION BY» tiene un costo del 31%. Entonces, el segundo método está más optimizado y debemos seguir una solución optimizada.
La primera consulta que usa «ROW_NUMBER ()» y la cláusula JOIN tiene un costo de ejecución del 56%, mientras que la segunda consulta que usa «ROW_NUMBER ()» y «PARTITION BY» tiene un costo del 31%. Entonces el segundo método es uno más optimizado
