![]() ![]() ![]() delete students from students where students. any restriction on which rows to compare. To do this, we’ll use the ROWNUMBER () function to help find duplicates with name and ages. The goal is to delete repeated records, based on the EmployeeName field. In practice I have found that some delete iterations are slower than others (secondary index cleanup etc., checking for foreign key rows and so on) WITH T_CTE Delete duplicate records in SQL Server Ask Question Asked 12 years, 11 months ago Modified 3 years, 8 months ago Viewed 119k times 103 Consider a column named EmployeeName table Employee. (If the single-iteration time causes an abort you might adjust the code so that the Batch Size is adjusted, downwards, after a slow iteration and back upwards, again, after a fast iteration, it will then auto-adjust to varying load on the server. It includes a SLEEP (default 3 seconds) each iteration, which will stop it hogging resources and allow other process to run. This code sample has configurable options for limits on number of deletes per iteration, max iterations, max time of any single iteration, and max overall runtime (e.g. If the rows have a unique identifier (Primary Key would be a good candidate, if you have one, most especially if it is also the Clustered Index) then use the above code to just collect the key(s) for the records to be deleted into a #TEMP table, and then use that to physically remove the rows, probably something like the code below. ![]() If the number of rows deleted is very large (more than 10,000 say) then you may need to do this in batches, particularly if the delete operation will be performed whilst the database is in use. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |