Lorsque nous concevons des objets dans SQL Server, nous devons suivre certaines bonnes pratiques. Par exemple, une table doit avoir des clés primaires, colonnes d'identité, index clusterisés et non clusterisés, intégrité des données et limitations de performances. La table SQL Server ne doit pas contenir de lignes en double conformément aux meilleures pratiques de conception de base de données. Cependant, il est parfois nécessaire de traiter avec des bases de données où ces règles ne sont pas respectées ou où des exceptions peuvent être faites lorsque ces règles sont intentionnellement contournées. Bien que nous suivions les meilleures pratiques, nous pouvons être confrontés à des problèmes tels que des files d'attente en double.
Par exemple, Nous pourrions également obtenir ce type de données lors de l'importation de tables intermédiaires et nous aimerions supprimer les lignes redondantes avant d'ajouter aux tables de production. En outre, il ne faut pas renoncer à la possibilité de dupliquer des lignes car les informations dupliquées permettent un traitement multiple des requêtes, présenter des résultats incorrects et bien plus encore. Cependant, si nous avons déjà des lignes en double dans la colonne, nous devons suivre des méthodes spécifiques pour nettoyer les données en double. Regardons quelques moyens dans cet article pour éliminer la duplication de données.
La table qui contient les lignes en double.
Comment supprimer les lignes en double d'une table SQL Server?
Il existe plusieurs façons dans SQL Server de gérer les enregistrements en double dans une table en fonction de circonstances particulières telles que:
Supprimer les lignes en double d'une seule table d'index SQL Server
Vous pouvez utiliser l'index pour trier les données en double dans des tables d'index uniques, puis supprimer les enregistrements en double. Premier, nous devons créer une base de données appelée “base_test”, puis créer un tableau “Employé” avec un index unique utilisant le code suivant.
Utiliser le professeur VA CREATE DATABASE test_database VA UTILISER [test_base_de_données] VA CRÉER TABLE Employé ( PAS UNE IDENTITÉ NULLE (1,1), INT, [nom] varchar(200), [e-mail] varchar (250) NUL, Varchar (250) NUL, [adresse] varchar(500) Nul ID DE CLÉ PRIMAIRE DE CONTRAINTE DE CLÉ PRIMAIRE )
Le résultat sera le suivant.
Création du tableau « Employé »
Insérez maintenant les données dans le tableau. Nous allons également insérer des lignes en double. le “Dép_ID” 003,005 Oui 006 sont des lignes en double avec des données similaires dans tous les champs, à l'exception de la colonne d'identité avec un index de clé unique. Exécutez le code suivant.
UTILISER [test_base_de_données] VA INSÉRER DANS L'EMPLOYÉ(Dép_ID,nom,e-mail,ville,adresse) VALEURS (001, $0027Aaaronboy Gutiérrez 0027 $, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Ou 97124$0027), (002, $0027Aabdi Maghsoudi 0027 $, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni0027 $, $0027abharana.sahni @ gmail.com 0027 $, 0027 $HYATTSVILLE 0027 $, 00272 $ Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni0027 $, $0027abharana.sahni @ gmail.com 0027 $, 0027 $HYATTSVILLE 0027 $, 00272 $ Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Moghol$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); SÉLECTIONNER * EMPLOYÉ
Le résultat sera le suivant.
Insérer des données dans la table nommée “Employé” et récupérer les données de la même table.
Trouvez maintenant le nombre de lignes dans le tableau en exécutant le code suivant. Fonction de comptage
SÉLECTIONNER DEP_ID,nom,e-mail,ville,adresse,COMPTE(*) AS EMPLOYEE_Duplicated_Rows_count GROUPER PAR DEP_ID,nom,e-mail,ville,adresse
comptera le nombre de lignes.
Le résultat sera le suivant. Les rangées ne (3, 4), (6, 7), (8, 9) mis en évidence dans la case rouge sont des doublons.
Cette figure met en évidence les lignes en double qui ont row_no supérieur à 1
Notre tâche consiste à renforcer l'unicité en supprimant les doublons des colonnes en double. Il est un peu plus facile de supprimer les valeurs en double d'une table avec un index unique que de supprimer des lignes d'une table sans. Voici deux méthodes pour y parvenir. La première méthode vous donne des lignes en double de la table en utilisant la fonction “numéro_ligne ()”, tandis que la deuxième méthode utilise la fonction “PAS DEDANS”. Ces deux méthodes ont leur propre coût qui sera discuté plus loin..
sélectionner * de (SÉLECTIONNER identifiant, nom, courrier électronique, ville, adresse, ROW_NUMBER() SUR ( PARTICIPATION DE Dép_ID,nom,e-mail,ville,adresse TRIER PAR Dép_ID,nom,e-mail,ville,adresse ) no_ligne TEST DATABASE.dbo.Employé) X donde row_no>1
Méthode 1: sélectionner les enregistrements en double à l'aide de la fonction “ROW_NUMBER ()”
SÉLECTIONNER * FROM test_database.dbo.Employee O L'ID N'EST PAS (SÉLECTION MAXIMALE(identifiant) FROM test_database.dbo.Employee GROUPER PAR DEP_ID, nom, e-mail, ville, adresse)
Méthode 2: sélection d'enregistrements en double à l'aide de la fonction “PAS DEDANS ()”
Exécutez le code ci-dessus et vous verrez la sortie suivante. Les deux méthodes donnent le même résultat, mais ils ont des coûts différents.
Sélectionnez les lignes en double dans la table nommée “Employé” en utilisant la méthode 1 Oui 2 respectivement
Maintenant, nous allons supprimer les lignes en double sélectionnées précédemment en utilisant “Côte” en utilisant le code suivant. Le code suivant sélectionne les lignes en double à supprimer à l'aide de la fonction “ROW_NUMBER ()”.
AVEC cte_delete AS (
SÉLECTIONNER
identifiant, nom, courrier électronique, ville, adresse,
ROW_NUMBER() SUR (
PARTICIPATION DE
Dép_ID,nom,e-mail,ville,adresse
TRIER PAR
Dép_ID,nom,e-mail,ville,adresse
) no_ligne
DE
test_database.dbo.Employé
)
SUPPRIMER DE cte_borrar O row_no> 1;
Méthode 1: supprimer les enregistrements en double à l'aide de la fonction “ROW_NUMBER ()”
Le résultat sera le suivant.
Suppression des enregistrements en double de la table indexée à l'aide de la fonction "ROW_NUMBER" ()
Méthode 2: supprimer les enregistrements en double à l'aide de la fonction “PAS DEDANS ()”
UTILISER [test_base_de_données] VA tronquer la table test_database.dbo.Employee INSÉRER DANS L'EMPLOYÉ(Dép_ID,nom,e-mail,ville,adresse) VALEURS (001, $0027Aaaronboy Gutiérrez 0027 $, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Ou 97124$0027), (002, $0027Aabdi Maghsoudi 0027 $, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni0027 $, $0027abharana.sahni @ gmail.com 0027 $, 0027 $HYATTSVILLE 0027 $, 00272 $ Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni0027 $, $0027abharana.sahni @ gmail.com 0027 $, 0027 $HYATTSVILLE 0027 $, 00272 $ Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Moghol$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); SÉLECTIONNER * EMPLOYÉ
À présent, essayer une autre méthode, nous devons tronquer la table qui supprimera toutes les lignes de la table. Alors, La commande d'insertion ajoutera des valeurs au tableau. Exécutez le code suivant maintenant.
Le résultat sera celui indiqué ci-dessous.
Insérer des données dans la table nommée “Employé” et récupérer les données de la même table.
Supprimer de la base de données de test.dbo.Employee O L'ID N'EST PAS (SÉLECTION MAXIMALE(identifiant) FROM test_database.dbo.Employee GROUPER PAR DEP_ID, nom, e-mail, ville, adresse)
Exécutez le code suivant pour supprimer toutes les lignes en double de la table “Employé”.
Le résultat sera le suivant.
Supprimez toutes les lignes en double de la table indexée nommée « Employee
Plan d'exécution et coût de la requête pour supprimer les lignes en double de la table indexée:
Maintenant, nous devons vérifier quelle méthode sera la plus rentable et nécessitera moins de ressources. Sélectionnez le code et cliquez sur le plan d'exécution. L'écran suivant apparaîtra montrant tous les plans d'exécution ainsi que le pourcentage de coût.
On voit que la méthode 1 “supprimer les enregistrements en double à l'aide de la fonction” ROW_NUMBER () “a un coût de 33% et méthode 2” supprimer les enregistrements en double à l'aide de la fonction NOT IN () “a un coût de 67%. Donc, la méthode un est la plus rentable par rapport à la méthode deux.
La méthode 1 a un coût de 33% et la méthode 2 a un coût de 67%, qui révèle que la méthode 1 est plus rentable.
Supprimer les doublons d'une table SQL Server sans index unique:
Il est un peu plus difficile de supprimer des lignes ou des tables en double sans index unique. À ce stade, en utilisant une expression de table commune (Côte) et la fonction ROW NUMBER () nous aide à éliminer les enregistrements en double. Pour supprimer les doublons de la table sans index unique, nous devons générer des identifiants de ligne uniques.
UTILISER [test_base_de_données] VA METTRE ANSI_NULLS DANS VA METTRE QUOTED_IDENTIFIER DANS VA CRÉER UN TABLEAU [dbo]. [Employé sans index]( [Dép_ID] [entier] NUL, [nom] [varchar](200) NUL, [e-mail] [varchar](250) NUL, NUL, [adresse] [varchar](500) NUL, ) VA
Exécutez le code suivant pour créer la table sans index unique.
Le résultat sera le suivant.
Création de la table appelée “Employé_avec_no_index” sans index unique
UTILISER [test_base_de_données] VA INSÉRER DANS EMPLOYEE_CON_SIN_INDICE(Dép_ID,nom,e-mail,ville,adresse) VALEURS (001, $0027Aaaronboy Gutiérrez 0027 $, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Ou 97124$0027), (002, $0027Aabdi Maghsoudi 0027 $, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni0027 $, $0027abharana.sahni @ gmail.com 0027 $, 0027 $HYATTSVILLE 0027 $, 00272 $ Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni0027 $, $0027abharana.sahni @ gmail.com 0027 $, 0027 $HYATTSVILLE 0027 $, 00272 $ Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Moghol$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); SÉLECTIONNER * FROM Employee_with_out_index
Insérez maintenant les enregistrements dans la table créée appelée “Employee_with_out_index” exécuter le code suivant.
Le résultat sera le suivant.
Insérez des données dans la table avec un index de sortie appelé “Employé_avec_no_index”
Méthode 1: supprimer les lignes en double d'une table à l'aide de la fonction “ROW_NUMBER ()” et REJOIGNEZ.
CON temp_tablr_con_row_ids AS ( SÉLECTIONNER LE NUMÉRO DE LIGNE() SUR (TRIER PAR DEP_ID,NOM,E-MAIL,VILLE,ADRESSE) Numéro de file d'attente AS, Dép_ID,nom,e-mail,ville,direction FROM test_database.dbo.Employee_with_out_index ) BORRAR una de temp_tablr_ con_row_ids a O row_no < (SELECTIONNER MAX(no_ligne) FROM temp_tablr_with_row_ids i O 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 GROUPE POR DEP_ID, nom, e-mail, ville, adresse)
Exécutez le code suivant qui utilise la fonction ROW_NUMBER () et JOIN pour supprimer les lignes en double de la table sans index. Créez d'abord une identité unique pour attribuer row_no à toutes les lignes et ne conserver qu'une seule ligne en supprimant les doublons.
Le résultat sera le suivant.
Supprimer les lignes en double d'une table sans index à l'aide de la fonction “ROW_NUMBER ()” y REJOINT
Méthode 2: supprimer les lignes en double d'une table à l'aide de la fonction “ROW_NUMBER ()” et PARTITION PAR.
tronquer la table Employee_with_out_index INSÉRER DANS EMPLOYEE_CON_SIN_INDICE(Dép_ID,nom,e-mail,ville,adresse) VALEURS (001, $0027Aaaronboy Gutiérrez 0027 $, [email protected]$0027,$0027HILLSBORO$0027,$00275840 Ne Cornell Rd Hillsboro Ou 97124$0027), (002, $0027Aabdi Maghsoudi 0027 $, [email protected] $ 0027, $ 0027BRENTWOOD $ 0027, $ 0027987400 Nebraska Medical Center Omaha Ne 681987400$0027), (003, $0027Aabharana, Sahni0027 $, $0027abharana.sahni @ gmail.com 0027 $, 0027 $HYATTSVILLE 0027 $, 00272 $ Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni0027 $, $0027abharana.sahni @ gmail.com 0027 $, 0027 $HYATTSVILLE 0027 $, 00272 $ Barlo Circle Suite A Dillsburg Pa 170191$0027), (004, $0027Aabish Moghol$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);
À présent, dans cette méthode, nous utilisons la fonction ROW_NUMBER avec la partition par clause pour attribuer row_no à toutes les lignes, puis supprimer les doublons. En premier lieu, nous devons tronquer la même table que nous avons créée précédemment afin que toutes les données soient supprimées de la table. Insérez ensuite les enregistrements dans la table, y compris les enregistrements en double. La troisième requête supprimera les lignes en double de la table nommée “Employé_avec_no_index”.
; CON temp_tablr_with_row_ids AS ( SÉLECTIONNER LE NUMÉRO DE ROUTE() SUR (PARTICIPATION PAR DEP_ID,NOM,E-MAIL,VILLE,ADRESSE ORDER BY Dep_ID,nom,e-mail,ville,adresse) AS row_no, Dép_ID,nom,e-mail,ville,adresse DE Employé_sin_indexar )
Sélection des enregistrements en double dans le tableau des températures.
SUPPRIMER un FROM temp_tablr_with_row_ids un WHERE row_no> 1
Élimination des enregistrements en double de la table de température.
Le résultat sera le suivant.
Troncation, insertion, suppression des lignes en double d'une table sans index et sélection des enregistrements résultants.
En outre, nous devons connaître les coûts d'exécution des requêtes pour comprendre ce qu'est une solution optimisée. Donc, vous devez sélectionner toutes les requêtes pertinentes et cliquer sur plan d'exécution. L'image suivante montre le plan d'exécution de la requête ainsi que le coût d'exécution. Les requêtes de suppression sont mises en évidence dans la zone rouge. La première requête que vous utilisez “ROW_NUMBER ()” et la clause JOIN a un coût d'exécution du 56%, tandis que la deuxième requête utilise “ROW_NUMBER ()” Oui “PARTITION PAR” a un coût de 31%. Ensuite, la deuxième méthode est plus optimisée et nous devons suivre une solution optimisée.
La première requête que vous utilisez “ROW_NUMBER ()” et la clause JOIN a un coût d'exécution du 56%, tandis que la deuxième requête que vous utilisez “ROW_NUMBER ()” Oui “PARTITION PAR” a un coût de 31%. La deuxième méthode est donc plus optimisée
