Bagaimana cara menghapus baris duplikat dari tabel SQL Server?

Saat mendesain objek di SQL Server, kita harus mengikuti praktik terbaik tertentu. Misalnya, tabel harus memiliki kunci utama, kolom identitas, indeks berkerumun dan tidak berkerumun, integritas data, dan batasan kinerja. Tabel SQL Server tidak boleh berisi baris duplikat sesuai dengan praktik terbaik dalam desain database. Terkadang, bagaimanapun, kita perlu berurusan dengan database di mana aturan-aturan ini tidak diikuti atau di mana pengecualian dimungkinkan ketika aturan-aturan ini sengaja dilewati. Meskipun kami mengikuti praktik terbaik, kami mungkin menghadapi masalah seperti baris duplikat.

Misalnya, kita juga bisa mendapatkan tipe data ini saat mengimpor tabel perantara, dan kita ingin menghapus baris yang berlebihan sebelum benar-benar menambahkannya ke tabel produksi. Selain itu, kita tidak boleh meninggalkan prospek untuk menduplikasi baris karena informasi duplikat memungkinkan penanganan beberapa permintaan, hasil pelaporan yang salah, dan banyak lagi. Namun, jika kita sudah memiliki baris duplikat di kolom, kita perlu mengikuti metode khusus untuk membersihkan data duplikat. Mari kita lihat beberapa cara dalam artikel ini untuk menghilangkan duplikasi data.

Bagaimana cara menghapus baris duplikat dari tabel SQL Server?

Ada beberapa cara di SQL Server untuk menangani catatan duplikat dalam tabel berdasarkan keadaan tertentu seperti:

Menghapus baris duplikat dari tabel SQL Server indeks unik

Anda dapat menggunakan indeks untuk mengklasifikasikan data duplikat dalam tabel indeks unik kemudian menghapus catatan duplikat. Pertama, kita perlu membuat database bernama “test_database”, lalu buat tabel “Karyawan” dengan indeks unik dengan menggunakan kode yang diberikan di bawah ini.

GUNAKAN master GO CREATE DATABASE test_database GO GUNAKAN [test_database] GO CREATE TABLE Karyawan ( [ID] INT NOT NULL IDENTITY(1,1), [Dep_ID] INT, [Name] varchar(200), [email] varchar (250) NULL , [kota] varchar(250) NULL, [alamat] varchar(500) NULL CONSTRAINT Primary_Key_ID PRIMARY KEY(ID) )

Outputnya akan seperti di bawah ini.

Sekarang masukkan data ke dalam tabel. Kami juga akan menyisipkan baris duplikat. “Dep_ID” 003.005 dan 006 adalah baris duplikat dengan data serupa di semua bidang kecuali kolom identitas dengan indeks kunci unik. Jalankan kode yang diberikan di bawah ini.

GUNAKAN [test_database] GO INSERT INTO Employee(Dep_ID,Name,email,city,address) NILAI (001, 'Aaaronboy Gutierrez', '[email protected]','HILLSBORO','5840 Ne Cornell Rd Hillsboro Atau 97124 '), (002, 'Aabdi Maghsoudi', '[email protected]','BRENTWOOD','987400 Pusat Medis Nebraska Omaha Ne 681987400'), (003, 'Aabharana, Sahni', 'abharana.sahni@gmail. com','HYATTSVILLE','2 Barlo Circle Suite A Dillsburg Pa 170191'), (003, 'Aabharana, Sahni', '[email protected]','HYATTSVILLE','2 Barlo Circle Suite A Dillsburg Pa 170191'), (004, 'Aabish Mughal', '[email protected]','OMAHA','2975 Crouse Lane Burlington Nc 272150000'), (005, 'Aabram Howell', '[email protected] ','DILLSBURG','868 York Ave Atlanta Ga 303102750'), (005, 'Aabram Howell', '[email protected]','DILLSBURG','868 York Ave Atlanta Ga 303102750'), (006 , 'Humbaerto Acevedo', '[email protected]','SAINT PAUL','895 E 7th St Saint Paul Mn 551063852'), (006, 'Humbaerto Acevedo', 'humbaerto.ac [email protected]','SAINT PAUL','895 E 7th St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', '[email protected]','ATLANTA','5813 Eastern Ave Hyattsville Md 207822201'); PILIH * DARI Karyawan

Outputnya akan seperti berikut.

Sekarang temukan jumlah baris dalam tabel dengan mengeksekusi kode berikut. Fungsi count(*) tidak akan menghitung jumlah baris.

PILIH Dep_ID,Nama,email,kota,alamat, COUNT(*) SEBAGAI duplikat_rows_count FROM Employee GROUP BY Dep_ID,Nama,email,kota,alamat

Outputnya akan seperti di bawah ini. Baris no (3, 4), (6, 7), (8, 9) yang disorot dalam kotak merah adalah duplikat.

Tugas kita adalah menegakkan keunikan dengan menghapus duplikat untuk kolom duplikat. Sedikit lebih mudah untuk menghapus nilai duplikat dari tabel dengan indeks unik daripada menghapus baris dari tabel tanpa indeks. Diberikan di bawah ini adalah dua metode untuk mencapai ini. Metode pertama memberi Anda duplikat baris dari tabel menggunakan fungsi "row_number()", sedangkan metode kedua menggunakan fungsi "NOT IN". Kedua metode ini memiliki biaya masing-masing yang akan dibahas kemudian.

Metode 1: Memilih catatan duplikat menggunakan fungsi “ROW_NUMBER ()”

pilih * dari (SELECT Dep_ID,Name,email,city,address, ROW_NUMBER() OVER ( PARTITION BY Dep_ID,Name,email,city,address ORDER BY Dep_ID,Name,email,city,address ) row_no FROM test_database.dbo.Employee ) x di mana baris_no>1

Metode 2: Memilih rekaman duplikat menggunakan fungsi "NOT IN ()"

SELECT * FROM test_database.dbo.Employee WHERE ID NOT IN (SELECT MAX(ID) FROM test_database.dbo.Employee GROUP BY Dep_ID,Name,email,city,address)

Jalankan kode di atas dan Anda akan melihat output berikut. Kedua metode memberikan hasil yang sama, tetapi memiliki biaya yang berbeda.

Sekarang kita akan menghapus baris duplikat yang dipilih di atas menggunakan "CTE" dengan menggunakan kode berikut. Kode berikut memilih baris duplikat untuk dihapus menggunakan fungsi “ROW_NUMBER ()”.

Metode 1: Menghapus catatan duplikat menggunakan fungsi “ROW_NUMBER ()”

DENGAN cte_delete AS ( SELECT Dep_ID,Name,email,city,address, ROW_NUMBER() OVER ( PARTITION BY Dep_ID,Name,email,city,address ORDER BY Dep_ID,Name,email,city,address ) row_no FROM test_database.dbo.Employee ) HAPUS DARI cte_delete WHERE row_no > 1;

Outputnya akan seperti di bawah ini.

Metode 2: Menghapus catatan duplikat menggunakan fungsi "NOT IN ()"

Sekarang untuk menguji metode lain, kita perlu memotong tabel yang akan menghapus semua baris dari tabel. Kemudian perintah sisipkan akan menambahkan nilai ke tabel. Jalankan kode berikut sekarang.

GUNAKAN [test_database] GO truncate table test_database.dbo.Employee INSERT INTO Employee(Dep_ID,Name,email,city,address) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]','HILLSBORO',' 5840 Ne Cornell Rd Hillsboro Or 97124'), (002, 'Aabdi Maghsoudi', '[email protected]','BRENTWOOD','987400 Nebraska Medical Center Omaha Ne 681987400'), (003, 'Aabharana, Sahni', '[email protected]','HYATTSVILLE','2 Barlo Circle Suite A Dillsburg Pa 170191'), (003, 'Aabharana, Sahni', '[email protected]','HYATTSVILLE',' 2 Barlo Circle Suite A Dillsburg Pa 170191'), (004, 'Aabish Mughal', '[email protected]','OMAHA','2975 Crouse Lane Burlington Nc 272150000'), (005, 'Aabram Howell', ' [email protected]','DILLSBURG','868 York Ave Atlanta Ga 303102750'), (005, 'Aabram Howell', '[email protected]','DILLSBURG','868 York Ave Atlanta Ga 303102750 '), (006,' Humbaerto Acevedo ',' [email protected] ',' SAINT PAUL ',' 895 E 7th St Saint Paul Mn 551063852 ' ), (006, 'Humbaerto Acevedo', '[email protected]', 'SAINT PAUL', '895 E 7th St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', 'pilar.ackaerman @ gmail.com','ATLANTA','5813 Eastern Ave Hyattsville Md 207822201'); PILIH * DARI Karyawan

Outputnya akan seperti yang diberikan di bawah ini.

Jalankan kode yang diberikan di bawah ini untuk menghapus semua baris duplikat dari tabel "Karyawan".

Hapus FROM test_database.dbo.Employee WHERE ID NOT IN (SELECT MAX(ID) FROM test_database.dbo.Employee GROUP BY Dep_ID,Name,email,city,address)

Outputnya akan seperti berikut.

Paket Eksekusi dan Biaya Kueri untuk menghapus baris duplikat dari tabel yang diindeks:

Sekarang kita harus memeriksa metode mana yang hemat biaya dan menggunakan lebih sedikit sumber daya. Pilih kode dan klik pada rencana eksekusi. Layar berikut akan muncul menunjukkan semua rencana pelaksanaan bersama dengan persentase biaya.

Kita dapat melihat bahwa metode 1 "menghapus catatan duplikat menggunakan fungsi "ROW_NUMBER ()" memiliki biaya 33% dan metode 2 "menghapus catatan duplikat menggunakan fungsi NOT IN ()" memiliki biaya 67%. Jadi metode satu adalah yang paling hemat biaya dibandingkan dengan metode dua.

Menghapus duplikat dari tabel SQL Server tanpa indeks unik:

Sedikit lebih sulit untuk menghapus baris atau tabel duplikat tanpa indeks unik. Dalam skenario ini, menggunakan ekspresi tabel umum (CTE) dan fungsi ROW NUMBER() membantu kami menghapus rekaman duplikat. Untuk menghapus duplikat dari tabel tanpa indeks unik, kita perlu membuat pengidentifikasi baris unik.

Jalankan kode berikut untuk membuat tabel tanpa indeks unik.

GUNAKAN [test_database] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employee_with_out_index]( [Dep_ID] [int] NULL, [Name] [varchar](200) NULL, [email] [varchar](250 ) NULL, [kota] [varchar](250) NULL, [alamat] [varchar](500) NULL, ) GO

Outputnya akan seperti berikut.

Sekarang masukkan record ke dalam tabel yang dibuat bernama “Employee_with_out_index” dengan mengeksekusi kode berikut.

GUNAKAN [test_database] GO INSERT INTO Employee_with_out_index(Dep_ID,Name,email,city,address) NILAI (001, 'Aaaronboy Gutierrez', '[email protected]','HILLSBORO','5840 Ne Cornell Rd Hillsboro Atau 97124 '), (002, 'Aabdi Maghsoudi', '[email protected]','BRENTWOOD','987400 Pusat Medis Nebraska Omaha Ne 681987400'), (003, 'Aabharana, Sahni', 'abharana.sahni@gmail. com','HYATTSVILLE','2 Barlo Circle Suite A Dillsburg Pa 170191'), (003, 'Aabharana, Sahni', '[email protected]','HYATTSVILLE','2 Barlo Circle Suite A Dillsburg Pa 170191'), (004, 'Aabish Mughal', '[email protected]','OMAHA','2975 Crouse Lane Burlington Nc 272150000'), (005, 'Aabram Howell', '[email protected] ','DILLSBURG','868 York Ave Atlanta Ga 303102750'), (005, 'Aabram Howell', '[email protected]','DILLSBURG','868 York Ave Atlanta Ga 303102750'), (006 , 'Humbaerto Acevedo', '[email protected]','SAINT PAUL','895 E 7th St Saint Paul Mn 551063852'), (006, 'Humbaerto Acevedo' , '[email protected]','SAINT PAUL','895 E 7th St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', '[email protected]','ATLANTA', '5813 Eastern Ave Hyattsville Md 207822201'); PILIH * FROM Employee_with_out_index

Outputnya akan seperti berikut.

Metode 1: Menghapus baris duplikat dari tabel menggunakan fungsi “ROW_NUMBER ()” dan BERGABUNG.

Jalankan kode berikut yang menggunakan fungsi ROW_NUMBER () dan JOIN untuk menghapus baris duplikat dari tabel tanpa indeks. IT pertama-tama membuat identitas unik untuk menetapkan row_no ke semua baris dan hanya menyimpan satu baris yang menghapus duplikat.

DENGAN temp_tablr_with_row_ids AS ( SELECT ROW_NUMBER() OVER (ORDER BY Dep_ID,Name,email,city,address) AS row_no, Dep_ID,Name,email,city,address FROM test_database.dbo.Employee_with_out_index ) DELETE a WRE_row_ FROM PILIH MAX(row_no) DARI temp_tablr_with_row_ids i WHERE a.Dep_ID=i.Dep_ID dan a.Name=i.Name dan a.email=i.email dan a.city=i.city dan a.address=i.address GROUP BY Dep_ID,Nama,email,kota,alamat)

Outputnya akan seperti berikut.

Metode 2: Menghapus baris duplikat dari tabel menggunakan fungsi “ROW_NUMBER ()” dan PARTITION BY.

Sekarang, dalam metode ini, kami menggunakan fungsi ROW_NUMBER bersama dengan partisi demi klausa untuk menetapkan row_no ke semua baris dan kemudian menghapus yang duplikat. Pertama-tama, kita perlu memotong tabel yang sama yang telah kita buat sebelumnya sehingga semua data terhapus dari tabel. Kemudian, masukkan catatan ke dalam tabel termasuk catatan duplikat. Kueri ketiga akan menghapus baris duplikat dari tabel bernama "Employee_with_out_index".

truncate table Employee_with_out_index INSERT INTO Employee_with_out_index(Dep_ID,Name,email,city,address) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]','HILLSBORO','5840 Ne Cornell Rd Hillsboro Atau 97124') , (002, 'Aabdi Maghsoudi', '[email protected]','BRENTWOOD','987400 Pusat Medis Nebraska Omaha Ne 681987400'), (003, 'Aabharana, Sahni', '[email protected]' ,'HYATTSVILLE','2 Barlo Circle Suite A Dillsburg Pa 170191'), (003, 'Aabharana, Sahni', '[email protected]','HYATTSVILLE','2 Barlo Circle Suite A Dillsburg Pa 170191' ), (004, 'Aabish Mughal', '[email protected]','OMAHA','2975 Crouse Lane Burlington Nc 272150000'), (005, 'Aabram Howell', '[email protected]', 'DILLSBURG','868 York Ave Atlanta Ga 303102750'), (005, 'Aabram Howell', '[email protected]','DILLSBURG','868 York Ave Atlanta Ga 303102750'), (006, ' Humbaerto Acevedo', '[email protected]','SAINT PAUL','895 E 7th St Saint Paul Mn 551063852'), (006, 'Hu mbaerto Acevedo', '[email protected]','SAINT PAUL','895 E 7th St Saint Paul Mn 551063852'), (007, 'Pilar Ackaerman', '[email protected]',' ATLANTA','5813 Eastern Ave Hyattsville Md 207822201');

Memilih catatan duplikat ke dalam tabel temp

; DENGAN temp_tablr_with_row_ids AS ( SELECT ROW_NUMBER() OVER (PARTITION BY Dep_ID,Name,email,city,address ORDER BY Dep_ID,Name,email,city,address) AS row_no, Dep_ID,Name,email,city,address FROM Employee_with_out_index )

Menghapus rekaman duplikat dari tabel temp

HAPUS a FROM temp_tablr_with_row_ids a WHERE row_no > 1

Outputnya akan seperti berikut.

Selanjutnya, kita perlu mengetahui tentang biaya eksekusi kueri untuk memahami mana yang merupakan solusi yang dioptimalkan. Jadi, Anda perlu memilih semua kueri yang relevan dan mengklik rencana eksekusi. Gambar di bawah ini menunjukkan rencana eksekusi untuk kueri bersama dengan biaya eksekusi. Hapus kueri disorot dalam kotak merah. Query pertama yang menggunakan klausa “ROW_NUMBER ()” dan JOIN memiliki biaya eksekusi 56%, sedangkan query kedua menggunakan “ROW_NUMBER ()” dan “PARTITION BY” berbiaya 31%. Jadi metode kedua adalah yang lebih optimal dan kita harus mengikuti solusi yang dioptimalkan.