SQL Server Composite Key

Question

I have the following situation: I have a table ItensVendas where I need to apply a composite key to the fields VendedorId and ProdutoId, however I have some duplicated data, ie I can not apply the composite key unless delete duplicate data.

|-----------|----------|
 VendedorId   ProdutoId
|-----------|----------|
      1         600
|-----------|----------|
      2         500
|-----------|----------|
      1         600     
|-----------|----------|
      4         600     
|-----------|----------|

The query I made to bring the duplicate data was this:

SELECT t.* 
FROM ITENSVENDA s
JOIN
(
    SELECT VENDEDORID, PRODUTOID
    FROM ITENSVENDA
    GROUP BY VENDEDORID, PRODUTOID
    HAVING COUNT(*) > 1
) t 
ON s.VENDEDORID = t.VENDEDORID AND s.PRODUTOID = t.PRODUTOID

I’m not getting to delete duplicate data, also not being sure that my query is really effective.

Answer 1

Your query to identify duplicate records is correct.

There is however another alternative that usually has better performance: The use of ROW_NUMBER().

To remove duplicate records, ensuring that the table will contain only one record with the composite key, you can do the following:

WITH Dups AS 
(
    SELECT ROW_NUMBER() OVER (PARTITION BY VENDEDORID, PRODUTOID ORDER BY ( SELECT 0)) RN
      FROM ITENSVENDA
)
DELETE FROM Dups
WHERE  RN > 1;

Note the use of ORDER BY (SELECT 0). This will allow, in the event of a tie, the random selection of a record to be kept in the table. If your table has another column, for example, modification date, you can use that column as tiebreaker.

If you do not want to leave any record, you can do so (assuming there are no null values in the composite key):

DELETE IV
FROM ITENSVENDA IV
INNER JOIN 
(
   SELECT VENDEDORID, PRODUTOID
     FROM ITENSVENDA
    GROUP BY VENDEDORID, PRODUTOID
   HAVING COUNT(*) > 1
) AS Duplicados 
   ON Duplicados.VENDEDORID = IV.VENDEDORID
  AND Duplicades.PRODUTOID = IV.PRODUTOID