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.