This is just a short post that uses a contrived example to demonstrate how to find duplicate records in a table. I needed to identify some duplicate records for a supplier recently and I wanted to make some notes on what I did for future reference.

First I will create a very simple table for this example

CREATE
TABLE [dbo].[Dups]

(

[FirstName] [nvarchar](50)
NULL,

[lastName] [nvarchar](50)
NULL,

[Company] [nvarchar](50)
NULL

)

ON [PRIMARY]

I will then add some example data, including some duplicate rows:

INSERT
INTO [C_AVG].[dbo].[Dups]

(

[FirstName],

[lastName],

[Company]

)

VALUES (

'Gethyn',

'Ellis',

'GRE'

)

INSERT
INTO [C_AVG].[dbo].[Dups]

(

[FirstName],

[lastName],

[Company]

)

VALUES (

'Lisa',

'Ellis',

'GRE'

)

INSERT
INTO [C_AVG].[dbo].[Dups]

(

[FirstName],

[lastName],

[Company]

)

VALUES (

'Ron',

'Ellis',

'GRE'

)

INSERT
INTO [C_AVG].[dbo].[Dups]

(

[FirstName],

[lastName],

[Company]

)

VALUES (

'Lisa',

'Ellis',

'GRE'

)

INSERT
INTO [C_AVG].[dbo].[Dups]

(

[FirstName],

[lastName],

[Company]

)

VALUES (

'Lisa',

'Ellis',

'GRE'

)

When I run a very simple select against this table I get the following output:

Gethyn    Ellis    GRE

Lisa        Ellis    GRE

Ron        Ellis    GRE

Lisa        Ellis    GRE

as we can see, Lisa is included in this table twice this but if you had a table with a couple million rows in this table and you suspected that it had duplicates spotting the duplicates maybe a little more difficult the following script will identify them for you:

This shows that

SELECT FirstName, lastName, Company FROM dups

GROUP
BY FirstName, lastName, Company

HAVING (COUNT
(*)
> 1)

This returns all the duplicate entries:

Lisa    Ellis    GRE

This only identifies the rows that exist more than once, cleaning up duplicates through deletion will be covered in another post.