Deleting DB entries

For ex: Lets say there are 100000 entries which i got after a date filter on a DB. now i want to delete those entries . How do i do that? The problem here would be that there will be linked tables through FKs, i want to delete those entries also from DB. Whats the best way to implement this? How can performance be improved.

i want to implement this by normal scripting, without using partitioning and other concepts.

more ▼

asked Mar 26, 2013 at 06:11 AM in Default

avatar image

470 14 14 19

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You'll need to do a few things to make this work well. First, you will have to know the structure so that you can delete all the related data prior to deleting the data in the principal table. That means also deleting data related to the related data. Second, you're not going to want to do all the deletes in a single transaction. This will lead to a huge transaction log and lots and lots of blocking. Instead, you should plan on breaking down the deletes into smaller chunks, say 1,000 rows at a time. To support this you'll need to load the data from your SELECT criteria as described into a temporary storage (temp table or table variable, since you'll be searching on it, probably a temp table is better). Then you can use this data to step through the delete process.

It's a lot of work, but it's the safest and most efficient mechanism I know.

more ▼

answered Mar 26, 2013 at 09:54 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

(comments are locked)
10|1200 characters needed characters left

If you want to delete a parent row and all other child rows referenced by foreign keys you could use cascading deletes. Alternatively you would have to work you way up from the bottom of the tree deleting child, parent, parents parent etc.

more ▼

answered Mar 26, 2013 at 09:17 AM

avatar image

1.8k 4 6 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 26, 2013 at 06:11 AM

Seen: 816 times

Last Updated: Mar 26, 2013 at 03:58 PM

Copyright 2018 Redgate Software. Privacy Policy