Duplicate rows SQL Server

SQL Server > Scripts > Duplicate rows SQL Server

Here is a script to find number of duplicate rows in SQL Server

IF OBJECT_ID(N'tbl1', N'U') IS NOT NULL

DROP TABLE tbl1;
GO
 

CREATE TABLE tbl1

(
       id     int ,
       name   varchar(50)
);
GO
 

INSERT INTO tbl1 values  (1,'john'), (2,'dan') , (1,'john')

GO
 

SELECT

  id,
  count(name) 'Number of duplicate rows'
FROM
  tbl1
GROUP BY
  id
HAVING
  count(name) > 1
GO

Result:
id Number of duplicate rows
1 2