Find duplicate rows in Sql table

Here we learn how to write query in “sql server management studio query analyzer” to find duplicate data in any sql table or view.

Knowing how to find duplicate data from sql table is one of the frequently required task for sql developer.

Following query can fetch the records with duplicate count, just change the table name and the column name, on which you want to find the duplicate records.

select colName1, Count(colName1) as DuplicateCount
from [dbo].[tableName]
group by colName1
having count(*) >1

If you want only unique records for any particular column then the following query is enough

select colName1
from [dbo].[tbTableName1] 
group by colName1

Group by clause will get only the unique values from any table, learn more about how to use having clause with group by in sql query.

In query result of above query, you will not find any duplicate data for that specified column.

If you want to find only unique rows based on same column value, you can use distinct in sql query.

select distinct colName1  from [dbo].[tbTableOne]

// like 
Select distinct Firstname from [dbo].[tbStudent]

Above query will get all rows where first name is unique in student table.