When we design objects in SQL Server, we must follow certain best practices. For example, a table must have primary keys, identity columns, clustered and nonclustered indexes, data integrity and performance limitations. SQL Server table should not contain duplicate rows according to database design best practices. However, sometimes it is necessary to deal with databases where these rules are not followed or where exceptions can be made when these rules are intentionally circumvented. Although we follow best practices, we may face problems like duplicate queues.
For example, We could also get this type of data when importing intermediate tables and we would like to remove redundant rows before adding to production tables. What's more, we must not give up the possibility of duplicating rows because duplicate information allows multiple handling of requests, presenting incorrect results and much more. However, if we already have duplicate rows in the column, we must follow specific methods to clean up duplicate data. Let's look at some ways in this article to eliminate data duplication.
The table that contains the duplicate rows.
How do you remove duplicate rows from a SQL Server table?
There are several ways in SQL Server to handle duplicate records in a table based on particular circumstances like:
Delete duplicate rows from a single SQL Server index table
You can use the index to sort the duplicate data into unique index tables and then remove the duplicate records. First, we need to create a database called “test_base”, and then create a table “Employee” with a unique index using the following code.
Use the teacher GO CREATE DATABASE test_database GO USE [test_database] GO CREATE TABLE Employee ( NOT A NULL IDENTITY (1,1), INT, [Name] varchar(200), [email] varchar (250) NULL, Varchar (250) NULL, [direction] varchar(500) Null PRIMARY KEY CONSTRAINT PRIMARY KEY ID )
The result will be the following.
Creating the table «Employee»
Now insert the data into the table. We will also insert duplicate rows. The “Dep_ID” 003,005 Y 006 are duplicate rows with similar data in all fields except identity column with unique key index. Run the following code.
USE [test_database] GO INSERT INTO THE EMPLOYEE(Dep_ID,Name,email,town,direction) VALUES (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 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 * EMPLOYEE
The result will be the following.
Insert data into table called “Employee” and get data from the same table.
Now find the number of rows in the table by running the following code. Counting function
SELECT DEP_ID,Name,email,town,direction,ACCOUNT(*) AS EMPLOYEE_Duplicated_Rows_count GROUP BY DEP_ID,Name,email,town,direction
will count the number of rows.
The result will be the following. The rows do not (3, 4), (6, 7), (8, 9) highlighted in the red box are duplicates.
This figure highlights duplicate rows that have row_no greater than 1
Our task is to enforce uniqueness by removing duplicates from duplicate columns. It is a bit easier to remove duplicate values from table with unique index than it is to remove rows from table without it. Here are two methods to achieve it. The first method gives you duplicate rows from the table using the function “row_number ()”, while the second method uses the function “NOT IN”. These two methods have their own cost which will be discussed later..
select * from (SELECT ID, Name, email, town, direction, ROW_NUMBER() ON ( PARTICIPATION OF Dep_ID,Name,email,town,direction SORT BY Dep_ID,Name,email,town,direction ) row_no TEST DATABASE.dbo.Employee) x donde row_no>1
Method 1: select duplicate records using function “ROW_NUMBER ()”
SELECT * FROM test_database.dbo.Employee WHERE ID IS NOT (MAX SELECTION(ID) FROM test_database.dbo.Employee GROUP BY DEP_ID, Name, email, town, direction)
Method 2: selecting duplicate records using the function “NOT IN ()”
Run the above code and you will see the following output. Both methods give the same result, but they have different costs.
Select duplicate rows from named table “Employee” using method 1 Y 2 respectively
Now we will remove the duplicate rows selected earlier using “Côte” using the following code. The following code selects duplicate rows to remove using the function “ROW_NUMBER ()”.
WITH cte_delete AS (
SELECT
ID, Name, email, town, direction,
ROW_NUMBER() ON (
PARTICIPATION OF
Dep_ID,Name,email,town,direction
SORT BY
Dep_ID,Name,email,town,direction
) row_no
FROM
test_database.dbo.Employee
)
DELETE FROM cte_borrar WHERE row_no> 1;
Method 1: remove duplicate records using the function “ROW_NUMBER ()”
The result will be the following.
Removing duplicate records from the indexed table using the "ROW_NUMBER" function ()
Method 2: remove duplicate records using the function “NOT IN ()”
USE [test_database] GO truncate the table test_database.dbo.Employee INSERT INTO THE EMPLOYEE(Dep_ID,Name,email,town,direction) VALUES (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 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 * EMPLOYEE
Now, to try another method, we need to truncate the table which will remove all the rows from the table. Later, insert command will add values to table. Run the following code now.
The result will be the one indicated below.
Insert data into table called “Employee” and get data from the same table.
Delete from test database.dbo.Employee WHERE ID IS NOT (MAX SELECTION(ID) FROM test_database.dbo.Employee GROUP BY DEP_ID, Name, email, town, direction)
Run the following code to remove all duplicate rows from the table “Employee”.
The result will be the following.
Remove all duplicate rows from the indexed table named "Employee
Execution plan and query cost to remove duplicate rows from indexed table:
Now we have to check which method will be more profitable and will require less resources. Select the code and click on the execution plan. The following screen will appear showing all the execution plans along with the cost percentage.
We can see that the method 1 “remove duplicate records using function” ROW_NUMBER () “has a cost of 33% and method 2” remove duplicate records using NOT IN function () “has a cost of 67%. Therefore, method one is the most profitable compared to method two.
The method 1 has a cost of 33% and the method 2 has a cost of 67%, which reveals that the method 1 is more profitable.
Remove duplicates from a SQL Server table without a unique index:
It's a bit more difficult to drop duplicate rows or tables without a unique index. In this stage, using a common table expression (Côte) and the ROW NUMBER function () helps us eliminate duplicate records. To remove duplicates from the table without a unique index, we need to generate unique row identifiers.
USE [test_database] GO PUT ANSI_NULLS IN GO PUT QUOTED_IDENTIFIER IN GO CREATE TABLE [dbo]. [Employee without index]( [Dep_ID] [int] NULL, [Name] [varchar](200) NULL, [email] [varchar](250) NULL, NULL, [direction] [varchar](500) NULL, ) GO
Run the following code to create the table without a unique index.
The result will be the following.
Creating the table called “Employee_with_no_index” without a unique index
USE [test_database] GO INSERT IN EMPLOYEE_WITH_SIN_INDICE(Dep_ID,Name,email,town,direction) VALUES (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 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
Now insert the records into the created table called “Employee_with_out_index” executing the following code.
The result will be the following.
Insert data into the table with an output index called “Employee_with_no_index”
Method 1: remove duplicate rows from a table using function “ROW_NUMBER ()” and JOIN.
CON temp_tablr_con_row_ids AS ( SELECT ROW NUMBER() ON (SORT BY DEP_ID,NAME,EMAIL,TOWN,DIRECTION) AS queue number, Dep_ID,Name,email,town,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, Name, email, town, direction)
Run the following code that uses the ROW_NUMBER function () and JOIN to remove duplicate rows from table without index. First create a unique identity to assign row_no to all rows and keep only one row by removing duplicates.
The result will be the following.
Removing duplicate rows from a table without an index using function “ROW_NUMBER ()” y JOINS
Method 2: remove duplicate rows from a table using function “ROW_NUMBER ()” and PARTITION BY.
truncate the Employee_with_out_index table INSERT IN EMPLOYEE_WITH_SIN_INDICE(Dep_ID,Name,email,town,direction) VALUES (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 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);
Now, in this method, we are using ROW_NUMBER function together with partition by clause to assign row_no to all rows and then remove duplicates. First, we need to truncate the same table we have created earlier so that all data is removed from the table. Then insert the records into the table, including duplicate records. The third query will remove the duplicate rows from the table named “Employee_with_no_index”.
; CON temp_tablr_with_row_ids AS ( SELECT ROUTE NUMBER() ON (PARTICIPATION BY DEP_ID,NAME,EMAIL,TOWN,DIRECTION ORDER BY Dep_ID,Name,email,town,direction) AS row_no, Dep_ID,Name,email,town,direction FROM Employee_sin_indexar )
Selecting duplicate records in the temperature table.
DELETE a FROM temp_tablr_with_row_ids a WHERE row_no> 1
Elimination of duplicate records from the temperature table.
The result will be the following.
Truncation, insertion, removing duplicate rows from a table with no index and selecting the resulting records.
What's more, we need to know the query execution costs to understand what an optimized solution is. Therefore, you need to select all relevant queries and click on execution plan. The following image shows the query execution plan along with the execution cost. Delete queries are highlighted in the red box. The first query you use “ROW_NUMBER ()” and the JOIN clause has an execution cost of the 56%, while the second query uses “ROW_NUMBER ()” Y “PARTITION BY” has a cost of 31%. Then, the second method is more optimized and we must follow an optimized solution.
The first query you use “ROW_NUMBER ()” and the JOIN clause has an execution cost of the 56%, while the second query you use “ROW_NUMBER ()” Y “PARTITION BY” has a cost of 31%. So the second method is a more optimized one
