This tutorial covers how to find duplicate records within SQL Server and how to delete them! I've also covered a few other points such as a query on how to find duplicates and how not to delete them!
HOW TO DELETE DUPLICATES USING A CTE
Personally I think the best, easiest and quickest way to delete duplicate rows is to use a CTE with the ROW NUMBER function. Its easier to analyse the data beforehand to ensure your query is correct but also ensures that anything with more than one record is removed! Obviously like anything in SQL Server there are a load of ways of doing the same thing, some quicker than others but I like simplicity and cte's give us that!
HOW NOT TO DELETE DUPLCATE RECORDS IN SQL
Whats just as important is to understand how NOT TO delete duplicate records as this can lead to data loss. So I've covered two ways of doing this and explained what dangers and pitfalls to look out for by comparing a SQL CTE with a GROUP BY and HAVING clause. This will show you how one method removes a duplicate while the other will remove both rows from the table
HOW TO PREVENT DUPLICATE RECORDS
The lesson also shows how to use PRIMARY KEYS to prevent duplicates being inserted in the first place. This can save us all a great deals of headaches just by setting up some good database design practices so we as DBAs we don't encounter the problem in the first place!
I think you will find the lesson helpful, but take a look at my other tutorials and as always give me a shout or comment if you have any questions.
Like and Subscribe for more videos!