Quando progettiamo oggetti in SQL Server, dobbiamo seguire alcune buone pratiche. Ad esempio, una tabella deve avere chiavi primarie, colonne di identità, indici cluster e non cluster, integrità dei dati e limitazioni delle prestazioni. La tabella di SQL Server non deve contenere righe duplicate secondo le migliori pratiche di progettazione del database. tuttavia, a volte è necessario occuparsi di banche dati in cui queste regole non vengono seguite o dove si possono fare eccezioni quando queste regole vengono eluse intenzionalmente. Anche se seguiamo le migliori pratiche, potremmo incontrare problemi come code duplicate.
Ad esempio, Potremmo ottenere questo tipo di dati anche durante l'importazione di tabelle intermedie e vorremmo rimuovere le righe ridondanti prima di aggiungerle alle tabelle di produzione. Cosa c'è di più, non dobbiamo rinunciare alla possibilità di duplicare le righe perché le informazioni duplicate consentono una gestione multipla delle richieste, presentare risultati errati e molto altro. tuttavia, se abbiamo già righe duplicate nella colonna, dobbiamo seguire metodi specifici per ripulire i dati duplicati. Diamo un'occhiata ad alcuni modi in questo articolo per eliminare la duplicazione dei dati.
La tabella che contiene le righe duplicate.
Come si rimuovono le righe duplicate da una tabella di SQL Server??
Esistono diversi modi in SQL Server per gestire i record duplicati in una tabella in base a circostanze particolari come:
Elimina le righe duplicate da una singola tabella di indice di SQL Server
È possibile utilizzare l'indice per ordinare i dati duplicati in tabelle di indici univoche e quindi rimuovere i record duplicati. Primo, dobbiamo creare un database chiamato “test_base”, e poi crea una tabella “Dipendente” con un indice univoco utilizzando il seguente codice.
Usa l'insegnante ANDARE CREA DATABASE test_database ANDARE UTILIZZO [database_test] ANDARE CREA TABELLA Dipendente ( NON UN'IDENTITÀ NULLA (1,1), INT, [Nome] varchar(200), [e-mail] varchar (250) NULLO, Varchar (250) NULLO, [indirizzo] varchar(500) Nullo VINCOLO CHIAVE PRIMARIA ID CHIAVE PRIMARIA )
Il risultato sarà il seguente.
Creazione della tabella «Impiegato»
Ora inserisci i dati nella tabella. Inseriremo anche righe duplicate. Il “ID_dip” 003,005 e 006 sono righe duplicate con dati simili in tutti i campi tranne la colonna identità con indice chiave univoco. Esegui il seguente codice.
UTILIZZO [database_test] ANDARE INSERIRE NEL DIPENDENTE(ID_dip,Nome,e-mail,cittadina,indirizzo) VALORI (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, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni$0027, $0027abharana.sahni @ gmail.com $ 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 7° San Paolo Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7° San Paolo Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027); SELEZIONARE * DIPENDENTE
Il risultato sarà il seguente.
Inserisci i dati nella tabella chiamata “Dipendente” e ottieni i dati dalla stessa tabella.
Ora trova il numero di righe nella tabella eseguendo il seguente codice. Funzione di conteggio
SELEZIONA DEP_ID,Nome,e-mail,cittadina,direzione,FATTURA(*) AS EMPLOYEE_Duplicate_Rows_count GRUPPO PER DEP_ID,Nome,e-mail,cittadina,indirizzo
conterà il numero di righe.
Il risultato sarà il seguente. Le righe no (3, 4), (6, 7), (8, 9) evidenziati nel riquadro rosso sono duplicati.
Questa figura evidenzia le righe duplicate che hanno row_no maggiore di 1
Il nostro compito è rafforzare l'unicità rimuovendo i duplicati dalle colonne duplicate. È un po 'più facile rimuovere i valori duplicati dalla tabella con indice univoco piuttosto che rimuovere le righe dalla tabella senza di esso. Ecco due metodi per ottenerlo. Il primo metodo ti dà righe duplicate dalla tabella usando la funzione “numero_riga ()”, mentre il secondo metodo utilizza la funzione “NON IN”. Questi due metodi hanno un loro costo che verrà discusso in seguito..
Selezionare * a partire dal (SELEZIONARE ID, Nome, e-mail, cittadina, indirizzo, RIGA_NUMERO() SU ( PARTECIPAZIONE DI ID_dip,Nome,e-mail,cittadina,indirizzo ORDINA PER ID_dip,Nome,e-mail,cittadina,indirizzo ) riga_no TEST DATABASE.dbo.Dipendente) X donde row_no>1
Metodo 1: seleziona i record duplicati usando la funzione “RIGA_NUMERO ()”
SELEZIONARE * DA database_test.dbo.Impiegato DOVE L'ID NON C'E' (SELEZIONE MASSIMA(ID) DA database_test.dbo.Impiegato GRUPPO PER DEP_ID, Nome, e-mail, cittadina, indirizzo)
Metodo 2: selezionare i record duplicati utilizzando la funzione “NON IN ()”
Esegui il codice sopra e vedrai il seguente output. Entrambi i metodi danno lo stesso risultato, ma hanno costi diversi.
Seleziona le righe duplicate dalla tabella denominata “Dipendente” usando il metodo 1 e 2 rispettivamente
Ora rimuoveremo le righe duplicate selezionate in precedenza utilizzando “costa” utilizzando il seguente codice. Il codice seguente seleziona le righe duplicate da rimuovere utilizzando la funzione “RIGA_NUMERO ()”.
CON cte_delete AS (
SELEZIONARE
ID, Nome, e-mail, cittadina, indirizzo,
RIGA_NUMERO() SU (
PARTECIPAZIONE DI
ID_dip,Nome,e-mail,cittadina,indirizzo
ORDINA PER
ID_dip,Nome,e-mail,cittadina,indirizzo
) riga_no
A PARTIRE DAL
test_database.dbo.Dipendente
)
DELETE FROM cte_borrar WHERE row_no> 1;
Metodo 1: rimuovere i record duplicati utilizzando la funzione “RIGA_NUMERO ()”
Il risultato sarà il seguente.
Rimozione di record duplicati dalla tabella indicizzata utilizzando la funzione "ROW_NUMBER" ()
Metodo 2: rimuovere i record duplicati utilizzando la funzione “NON IN ()”
UTILIZZO [database_test] ANDARE troncare la tabella test_database.dbo.Employee INSERIRE NEL DIPENDENTE(ID_dip,Nome,e-mail,cittadina,indirizzo) VALORI (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, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni$0027, $0027abharana.sahni @ gmail.com $ 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 7° San Paolo Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7° San Paolo Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027); SELEZIONARE * DIPENDENTE
Ora, per provare un altro metodo, dobbiamo troncare la tabella che rimuoverà tutte le righe dalla tabella. Dopo, il comando di inserimento aggiungerà valori alla tabella. Esegui il seguente codice ora.
Il risultato sarà quello indicato di seguito.
Inserisci i dati nella tabella chiamata “Dipendente” e ottieni i dati dalla stessa tabella.
Elimina da test database.dbo.Employee DOVE L'ID NON C'E' (SELEZIONE MASSIMA(ID) DA database_test.dbo.Impiegato GRUPPO PER DEP_ID, Nome, e-mail, cittadina, indirizzo)
Esegui il seguente codice per rimuovere tutte le righe duplicate dalla tabella “Dipendente”.
Il risultato sarà il seguente.
Rimuovi tutte le righe duplicate dalla tabella indicizzata denominata "Impiegato
Piano di esecuzione e costo della query per rimuovere le righe duplicate dalla tabella indicizzata:
Ora dobbiamo verificare quale metodo sarà più redditizio e richiederà meno risorse. Seleziona il codice e clicca sul piano di esecuzione. Apparirà la seguente schermata che mostra tutti i piani di esecuzione insieme alla percentuale di costo.
Possiamo vedere che il metodo 1 “rimuovere i record duplicati utilizzando la funzione” RIGA_NUMERO () “ha un costo di 33% e metodo 2” rimuovere i record duplicati utilizzando la funzione NOT IN () “ha un costo di 67%. Perciò, il metodo uno è il più redditizio rispetto al metodo due.
Il metodo 1 ha un costo di 33% e il metodo 2 ha un costo di 67%, che rivela che il metodo 1 è più redditizio.
Rimuovi i duplicati da una tabella di SQL Server senza un indice univoco:
È un po' più difficile eliminare righe o tabelle duplicate senza un indice univoco. In questa fase, usando un'espressione di tabella comune (costa) e la funzione NUMERO DI RIGA () ci aiuta a eliminare i record duplicati. Per rimuovere i duplicati dalla tabella senza un indice univoco, dobbiamo generare identificatori di riga univoci.
UTILIZZO [database_test] ANDARE METTI ANSI_NULLS IN ANDARE PUT QUOTED_IDENTIFIER IN ANDARE CREA TABELLA [dbo]. [Dipendente senza indice]( [ID_dip] [int] NULLO, [Nome] [varchar](200) NULLO, [e-mail] [varchar](250) NULLO, NULLO, [indirizzo] [varchar](500) NULLO, ) ANDARE
Esegui il seguente codice per creare la tabella senza un indice univoco.
Il risultato sarà il seguente.
Creazione della tabella chiamata “Impiegato_con_no_indice” senza un indice univoco
UTILIZZO [database_test] ANDARE INSERIRE IN EMPLOYEE_CON_SIN_INDICE(ID_dip,Nome,e-mail,cittadina,direzione) VALORI (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, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni$0027, $0027abharana.sahni @ gmail.com $ 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 7° San Paolo Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7° San Paolo Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027); SELEZIONARE * DA Dipendente_con_fuori_indice
Ora inserisci i record nella tabella creata chiamata “Dipendente_con_fuori_indice” eseguendo il seguente codice.
Il risultato sarà il seguente.
Inserisci i dati nella tabella con un indice di output chiamato “Impiegato_con_no_indice”
Metodo 1: rimuovi le righe duplicate da una tabella usando la funzione “RIGA_NUMERO ()” e ISCRIVITI.
CON temp_tablr_con_row_ids AS ( SELEZIONA NUMERO DI RIGA() SU (ORDINA PER DEP_ID,NOME,E-MAIL,CITTADINA,INDIRIZZO) AS numero di coda, ID_dip,Nome,e-mail,cittadina,direzione DA test_database.dbo.Employee_with_out_index ) BORRAR una de temp_tablr_ con_row_ids a WHERE riga_no < (SELEZIONA MASSIMO(riga_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 GRUPPO PER DEP_ID, Nome, e-mail, cittadina, indirizzo)
Esegui il seguente codice che utilizza la funzione ROW_NUMBER () e JOIN per rimuovere le righe duplicate dalla tabella senza indice. Prima crea un'identità univoca per assegnare row_no a tutte le righe e mantieni solo una riga rimuovendo i duplicati.
Il risultato sarà il seguente.
Rimozione di righe duplicate da una tabella senza un indice utilizzando la funzione “RIGA_NUMERO ()” y ISCRIVITI
Metodo 2: rimuovi le righe duplicate da una tabella usando la funzione “RIGA_NUMERO ()” e DIVISIONE PER.
troncare la tabella Employee_with_out_index INSERIRE IN EMPLOYEE_CON_SIN_INDICE(ID_dip,Nome,e-mail,cittadina,direzione) VALORI (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, $0027abharana.sahni @ gmail.com $ 0027, $ 0027HYATTSVILLE $ 0027, $ 00272 Barlo Circle Suite A Dillsburg Pa 170191$0027), (003, $0027Aabharana, Sahni$0027, $0027abharana.sahni @ gmail.com $ 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 7° San Paolo Mn 551063852$0027), (006, $0027Humbaerto Acevedo $ 0027, [email protected]$0027,$0027SAINT PAUL$0027,$0027895 E 7° San Paolo Mn 551063852$0027), (007, $0027Pilar Ackaerman $ 0027, [email protected]$0027,$0027ATLANTA$0027,$00275813 Eastern Ave Hyattsville Md 207822201$0027);
Ora, in questo metodo, stiamo usando la funzione ROW_NUMBER insieme alla clausola partition by per assegnare row_no a tutte le righe e quindi rimuovere i duplicati. Primo, dobbiamo troncare la stessa tabella che abbiamo creato in precedenza in modo che tutti i dati vengano rimossi dalla tabella. Quindi inserisci i record nella tabella, compresi i record duplicati. La terza query rimuoverà le righe duplicate dalla tabella denominata “Impiegato_con_no_indice”.
; CON temp_tablr_with_row_ids AS ( SELEZIONA IL NUMERO DEL PERCORSO() SU (PARTECIPAZIONE DI DEP_ID,NOME,E-MAIL,CITTADINA,INDIRIZZO ORDINA PER ID_Dip,Nome,e-mail,cittadina,indirizzo) AS riga_no, ID_dip,Nome,e-mail,cittadina,indirizzo FROM Employee_sin_indexar )
Selezione di record duplicati nella tabella delle temperature.
DELETE a FROM temp_tablr_with_row_ids a WHERE row_no> 1
Eliminazione dei record duplicati dalla tabella delle temperature.
Il risultato sarà il seguente.
Troncamento, inserimento, rimozione di righe duplicate da una tabella senza indice e selezione dei record risultanti.
Cosa c'è di più, abbiamo bisogno di conoscere i costi di esecuzione della query per capire cos'è una soluzione ottimizzata. Perciò, è necessario selezionare tutte le query pertinenti e fare clic sul piano di esecuzione. L'immagine seguente mostra il piano di esecuzione della query insieme al costo di esecuzione. Le query di eliminazione sono evidenziate nel riquadro rosso. La prima query che usi “RIGA_NUMERO ()” e la clausola JOIN ha un costo di esecuzione del 56%, mentre la seconda query usa “RIGA_NUMERO ()” e “DIVISIONE PER” ha un costo di 31%. Quindi, il secondo metodo è più ottimizzato e dobbiamo seguire una soluzione ottimizzata.
La prima query che usi “RIGA_NUMERO ()” e la clausola JOIN ha un costo di esecuzione del 56%, mentre la seconda query che usi “RIGA_NUMERO ()” e “DIVISIONE PER” ha un costo di 31%. Quindi il secondo metodo è più ottimizzato
