How to find duplicate rows in SQL Server

I ran into this problem the other day at work with duplicate rows while trying to merge data from two separate tables into the same table. I couldn’t remember so I just ran a quick search using Google and came across the solution on a Microsoft knowledge base page.

-------------------------------------------------------

The first step is to identify which rows have duplicate primary key
values:

SELECT col1, col2, count(*)FROM t1GROUP BY col1, col2HAVING count(*) > 1

This will return one row for each set of duplicate PK values
in the table. The last column in this result is the number of
duplicates for the particular PK value.

col1               col2

1                   1                   2

—————————————————————