How to Find Duplicate Rows in SQL Server

The grouped columns define what counts as a duplicate. NULLs group together here, and SQL Server's UNIQUE constraint agrees, permitting only one NULL because it treats NULLs as equal. That is stricter than Postgres or MySQL, so for once the GROUP BY definition and the constraint definition line up.

SELECT email, COUNT(*) AS dupes
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

See the full rows

The join brings back the full rows so you can compare them before deciding what to keep. The idiomatic SQL Server alternative is ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) inside a CTE, which finds the duplicates and numbers them at once, letting you delete everything with rn > 1 directly from the CTE.

SELECT c.*
FROM customers c
JOIN (
  SELECT email
  FROM customers
  GROUP BY email
  HAVING COUNT(*) > 1
) d ON d.email = c.email
ORDER BY c.email, c.id;