Example of searching duplicate records on SQL server

When designing a database, consider whether the user table exists repeatedly, because a user table generally does not allow two records with the same user name, because once there is the same, so which record does a user belong to when logging on with this user name?
To prevent this situation, you should take a three-pronged approach. The most important thing is that when designing a database, UNIQUE must have a UNIQUE index for a field.

The three methods are: add the database UNIQUE index, and check whether the user name exists in the SELECT statement in the script, and whether the AJAX remote access interface has the user name.

Method 1: The unique index of a field ensures that the data inserted for multiple asynchronous requests exists in the database. The only sacrifice is the performance of the database, but for the stability of the system, this performance must be sacrificed.

Method 2: insert a program script and execute a SELECT statement to check whether the database exists. This method also sacrifices some efficiency and is recommended to be ignored.

1. Search for redundant duplicate records in the table, and judge based on a single field (peopleId).

Select * from people
Where peopleId in (select peopleId from people group by peopleId having count
(PeopleId)> 1)
2. Delete redundant record in the table. The record is determined based on a single field (eagleid). Only the records with the smallest rowid are retained.

Delete from people
Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)
And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)
-- By www.jbxue.com
3. Search for redundant duplicate records in the table (multiple fields)

Select * from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid

Delete from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)
5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid

Select * from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)
And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)

Method 3: when registering a user, you can remotely query the existence of the user name using AJAX. If the user name exists, no form is submitted, which greatly improves the user experience.

Select FIELD from TABLE
Group by FIELD
Having (count (*)> 1

We can find records with duplicate FIELD fields in the TABLE and display them in the query window. In this way, we can search for result sets with two or more duplicate records.

In summary, the best way to avoid repeated User name fields is to impose UNIQUE index constraints on the SQL SERVER database.