Làm thế nào để loại bỏ các hàng trùng lặp khỏi bảng SQL Server?

Khi thiết kế các đối tượng trong SQL Server, chúng ta phải tuân theo một số phương pháp hay nhất. Ví dụ: một bảng phải có khóa chính, cột nhận dạng, chỉ mục được phân nhóm và không được gộp, tính toàn vẹn dữ liệu và các ràng buộc về hiệu suất. Bảng SQL Server không được chứa các hàng trùng lặp theo các phương pháp hay nhất trong thiết kế cơ sở dữ liệu. Tuy nhiên, đôi khi chúng ta cần phải xử lý các cơ sở dữ liệu mà các quy tắc này không được tuân thủ hoặc có thể có ngoại lệ khi các quy tắc này bị bỏ qua một cách cố ý. Mặc dù chúng tôi đang tuân theo các phương pháp hay nhất, chúng tôi có thể gặp phải các vấn đề như các hàng trùng lặp.

Ví dụ: chúng tôi cũng có thể lấy loại dữ liệu này trong khi nhập các bảng trung gian và chúng tôi muốn xóa các hàng thừa trước khi thực sự thêm chúng vào bảng sản xuất. Hơn nữa, chúng ta không nên để khách hàng tiềm năng trùng lặp các hàng vì thông tin trùng lặp cho phép xử lý nhiều yêu cầu, kết quả báo cáo không chính xác và hơn thế nữa. Tuy nhiên, nếu chúng ta đã có các hàng trùng lặp trong cột, chúng ta cần làm theo các phương pháp cụ thể để xóa dữ liệu trùng lặp. Hãy xem một số cách trong bài viết này để loại bỏ trùng lặp dữ liệu.

Làm thế nào để loại bỏ các hàng trùng lặp khỏi bảng SQL Server?

Có một số cách trong SQL Server để xử lý các bản ghi trùng lặp trong một bảng dựa trên các trường hợp cụ thể như:

Xóa các hàng trùng lặp khỏi bảng SQL Server chỉ mục duy nhất

Bạn có thể sử dụng chỉ mục để phân loại dữ liệu trùng lặp trong các bảng chỉ mục duy nhất sau đó xóa các bản ghi trùng lặp. Trước tiên, chúng ta cần tạo cơ sở dữ liệu được đặt tên là “test_database”, sau đó tạo một bảng “Nhân viên” với một chỉ mục duy nhất bằng cách sử dụng mã được cung cấp bên dưới.

SỬ DỤNG master GO TẠO DATABASE test_database ĐI SỬ DỤNG [test_database] GO CREATE TABLE Nhân viên ([ID] INT NOT NULL IDENTITY (1,1), [Dep_ID] INT, [Name] varchar (200), [email] varchar (250) NULL , [city] varchar (250) NULL, [address] varchar (500) NULL CONSTRAINT Primary_Key_ID PRIMARY KEY (ID))

Đầu ra sẽ như bên dưới.

Bây giờ hãy chèn dữ liệu vào bảng. Chúng tôi cũng sẽ chèn các hàng trùng lặp. “Dep_ID” 003,005 và 006 là các hàng trùng lặp có dữ liệu tương tự trong tất cả các trường ngoại trừ cột nhận dạng có chỉ mục khóa duy nhất. Thực thi đoạn mã dưới đây.

SỬ DỤNG [test_database] ĐI CHÈN VÀO Nhân viên (Dep_ID, Tên, email, thành phố, địa chỉ) GIÁ TRỊ (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro Hoặc 97124 '), (002,' Aabdi Maghsoudi ',' [email protected] ',' BRENTWOOD ',' 987400 Nebraska Medical Center 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 '); CHỌN * TỪ nhân viên

Kết quả sẽ như sau.

Bây giờ, hãy tìm không có hàng nào trong bảng bằng cách thực thi đoạn mã sau. Hàm count (*) sẽ đếm không có hàng nào.

CHỌN Dep_ID, Tên, email, thành phố, địa chỉ, COUNT (*) AS Duplicate_rows_count TỪ NHÓM Nhân viên THEO Dep_ID, Tên, email, thành phố, địa chỉ

Đầu ra sẽ như bên dưới. Hàng số (3, 4), (6, 7), (8, 9) được đánh dấu trong hộp màu đỏ là hàng trùng lặp.

Nhiệm vụ của chúng tôi là thực thi tính duy nhất bằng cách loại bỏ các bản sao cho các cột trùng lặp. Sẽ dễ dàng hơn một chút để xóa các giá trị trùng lặp khỏi bảng với một chỉ mục duy nhất so với xóa các hàng khỏi bảng mà không có chỉ mục đó. Dưới đây là hai phương pháp để đạt được điều này. Phương thức đầu tiên cung cấp cho bạn các hàng trùng lặp từ bảng bằng cách sử dụng hàm “row_number ()”, trong khi phương pháp thứ hai sử dụng hàm “NOT IN”. Hai phương pháp này có chi phí riêng sẽ được thảo luận ở phần sau.

Phương pháp 1: Chọn bản ghi trùng lặp bằng hàm “ROW_NUMBER ()”

select * from (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.Empleteee ) x trong đó row_no> 1

Phương pháp 2: Chọn các bản ghi trùng lặp bằng cách sử dụng chức năng “NOT IN ()”

CHỌN * TỪ test_database.dbo. Nhân viên KHÔNG CÓ ID TRONG ĐÓ (CHỌN TỐI ĐA (ID) TỪ test_database.dbo. NHÓM Nhân viên THEO Dep_ID, Tên, email, thành phố, địa chỉ)

Thực hiện đoạn mã trên và bạn sẽ thấy kết quả sau. Cả hai phương pháp đều cho cùng một kết quả, nhưng chúng có chi phí khác nhau.

Bây giờ chúng tôi sẽ xóa các hàng trùng lặp đã chọn ở trên bằng cách sử dụng “CTE” bằng cách sử dụng mã sau. Đoạn mã sau đang chọn các hàng trùng lặp sẽ bị xóa bằng cách sử dụng hàm “ROW_NUMBER ()”.

Phương pháp 1: Xóa các bản ghi trùng lặp bằng hàm “ROW_NUMBER ()”

VỚI 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. ) XÓA TỪ cte_delete WHERE row_no> 1;

Đầu ra sẽ như bên dưới.

Phương pháp 2: Xóa các bản ghi trùng lặp bằng hàm “NOT IN ()”

Bây giờ để kiểm tra một phương pháp khác, chúng ta cần cắt bớt bảng sẽ loại bỏ tất cả các hàng khỏi bảng. Sau đó lệnh insert sẽ thêm giá trị vào bảng. Thực thi đoạn mã sau ngay bây giờ.

SỬ DỤNG [test_database] ĐI cắt ngắn bảng test_database.dbo. CHÈN NHÂN VIÊN VÀO Nhân viên (Dep_ID, Tên, email, thành phố, địa chỉ) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', ' 5840 Ne Cornell Rd Hillsboro Hoặc 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 '); CHỌN * TỪ nhân viên

Đầu ra sẽ như dưới đây.

Thực thi đoạn mã dưới đây để xóa tất cả các hàng trùng lặp khỏi bảng “Nhân viên”.

Xóa TỪ test_database.dbo.E NHÂN VIÊN NƠI KHÔNG CÓ ID TRONG (CHỌN TỐI ĐA (ID) TỪ test_database.dbo. NHÓM NHÂN VIÊN THEO Dep_ID, Tên, email, thành phố, địa chỉ)

Kết quả sẽ như sau.

Kế hoạch thực thi và Chi phí truy vấn để xóa các hàng trùng lặp khỏi bảng đã lập chỉ mục:

Bây giờ chúng ta phải kiểm tra xem phương pháp nào sẽ hiệu quả về chi phí và sử dụng ít tài nguyên hơn. Chọn mã và nhấp vào kế hoạch thực hiện. Màn hình sau sẽ xuất hiện hiển thị tất cả các kế hoạch đang thực hiện cùng với tỷ lệ phần trăm chi phí.

Chúng ta có thể thấy rằng phương pháp 1 “xóa các bản ghi trùng lặp bằng cách sử dụng hàm“ ROW_NUMBER () ”” có chi phí là 33% và phương pháp 2 “xóa các bản ghi trùng lặp bằng cách sử dụng hàm NOT IN ()” có chi phí là 67%. Vì vậy, phương pháp một là tiết kiệm chi phí nhất so với phương pháp hai.

Xóa các bản sao khỏi bảng SQL Server mà không có chỉ mục duy nhất:

Khó hơn một chút để xóa các hàng hoặc bảng trùng lặp mà không có chỉ mục duy nhất. Trong trường hợp này, việc sử dụng biểu thức bảng chung (CTE) và hàm ROW NUMBER () sẽ giúp chúng tôi xóa các bản ghi trùng lặp. Để xóa các bản sao khỏi bảng mà không có chỉ mục duy nhất, chúng ta cần tạo các số nhận dạng hàng duy nhất.

Thực thi đoạn mã sau để tạo bảng không có chỉ mục duy nhất.

SỬ DỤNG [test_database] THIẾT LẬP ANSI_NULLS KHI ĐI ĐẶT QUOTED_IDENTIFIER TRÊN ĐI TẠO BẢNG [dbo]. [Employee_with_out_index] ([Dep_ID] [int] NULL, [Name] [varchar] (200) NULL, [email] [varchar] (250 ) NULL, [city] [varchar] (250) NULL, [address] [varchar] (500) NULL,) ĐI

Kết quả sẽ như sau.

Bây giờ, hãy chèn các bản ghi vào bảng đã tạo có tên “Employee_with_out_index” bằng cách thực thi đoạn mã sau.

SỬ DỤNG [test_database] ĐI CHÈN VÀO Employee_with_out_index (Dep_ID, Tên, email, thành phố, địa chỉ) GIÁ TRỊ (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd Hillsboro Hoặc 97124 '), (002,' Aabdi Maghsoudi ',' [email protected] ',' BRENTWOOD ',' 987400 Nebraska Medical Center 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'); CHỌN * TỪ Employee_with_out_index

Kết quả đầu ra sẽ như sau.

Phương pháp 1: Xóa các hàng trùng lặp khỏi bảng bằng hàm “ROW_NUMBER ()” và JOINS.

Thực thi mã sau bằng cách sử dụng hàm ROW_NUMBER () và JOIN để xóa các hàng trùng lặp khỏi bảng mà không có chỉ mục. Đầu tiên, CNTT tạo ra một danh tính duy nhất để gán row_no cho tất cả các hàng và chỉ giữ một hàng loại bỏ các hàng trùng lặp.

VỚI 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.Eaffee_with_out_index) DELETE a FROM temp_tablr_ a (WHERE_row_ids CHỌN TỐI ĐA (row_no) TỪ temp_tablr_with_row_ids i WHERE a.Dep_ID = i.Dep_ID và a.Name = i.Name và a.email = i.email và a.city = i.city và a.address = i.address GROUP BY Dep_ID, Tên, email, thành phố, địa chỉ)

Kết quả sẽ như sau.

Phương pháp 2: Xóa các hàng trùng lặp khỏi bảng bằng hàm “ROW_NUMBER ()” và PARTITION BY.

Bây giờ, trong phương pháp này, chúng tôi đang sử dụng hàm ROW_NUMBER cùng với phân vùng theo mệnh đề để gán row_no cho tất cả các hàng và sau đó xóa các hàng trùng lặp. Trước hết, chúng ta cần cắt bớt cùng một bảng mà chúng ta đã tạo trước đó để tất cả dữ liệu bị xóa khỏi bảng. Sau đó, chèn các bản ghi vào bảng bao gồm các bản ghi trùng lặp. Truy vấn thứ ba sẽ xóa các hàng trùng lặp khỏi bảng có tên “Employee_with_out_index”.

cắt ngắn bảng Employee_with_out_index CHÈN VÀO Employee_with_out_index (Dep_ID, Tên, email, thành phố, địa chỉ) VALUES (001, 'Aaaronboy Gutierrez', '[email protected]', 'HILLSBORO', '5840 Ne Cornell Rd' Hillsboro) Hoặc 97124 Hillsboro , (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,' 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 ');

Chọn các bản ghi trùng lặp vào bảng tạm thời

; VỚI temp_tablr_with_row_ids NHƯ (CHỌN ROW_NUMBER () HẾT (PHẦN BỞI Dep_ID, Tên, email, thành phố, địa chỉ ĐẶT HÀNG THEO Dep_ID, Tên, email, thành phố, địa chỉ) AS row_no, Dep_ID, Tên, email, thành phố, địa chỉ FROM Employee_with_out_index)

Xóa các bản ghi trùng lặp khỏi bảng tạm thời

XÓA TỪ temp_tablr_with_row_ids a WHERE row_no> 1

Kết quả sẽ như sau.

Hơn nữa, chúng ta cần biết về chi phí thực hiện truy vấn để hiểu cái nào là giải pháp tối ưu hóa. Vì vậy, bạn cần chọn tất cả các truy vấn có liên quan và nhấp vào kế hoạch thực hiện. Hình ảnh bên dưới cho thấy kế hoạch thực thi cho các truy vấn cùng với chi phí thực thi. Xóa truy vấn được đánh dấu trong hộp màu đỏ. Truy vấn đầu tiên sử dụng mệnh đề “ROW_NUMBER ()” và JOIN có chi phí thực thi là 56%, trong khi truy vấn thứ hai sử dụng “ROW_NUMBER ()” và “PARTITION BY” có chi phí là 31%. Vì vậy, phương pháp thứ hai là một phương pháp tối ưu hơn và chúng ta nên tuân theo một giải pháp tối ưu hóa.