Several ways to find duplicate records in SQL Server database

Http://www.hanyu123.cn/html/c61/6790.html

One, check a column (or more than one column) of the duplicate values. (You can only find the value of the duplicate record, cannot find the whole record information)

For example: Find Stuid,stuname duplicate records:

1. Select stuid,stuname from stuinfo
2. Group by Stuid,stuname
3. Having(Count(*)) >1

Second, check a column has a record of duplicate values. (This method is to detect all duplicate records, if there are two records repeat, the detection of two)

For example: Find Stuid duplicate records:

1. Select * from stuinfo
2. where stuid in (
3. Select stuid from stuinfo
4. GROUP BY stuid
5. Having(Count(*)) >1
6. )

Third, check a column has a record of duplicate values. (Show only redundant records, which means two if there are three duplicates)

Prerequisite: There is a column that does not duplicate, this example is recno. For example: Find Stuid duplicate records:

1. Select * from stuinfo s1
2. Where recno not in (
3. Select Max(recno) from stuinfo s2
4. where s1. Stuid=S2. Stuid

Several ways to find duplicate records in SQL Server database