July 11, 2011 at 7:03 pm
Ok, I know we're supposed to be all normalized, but in the real world you need to archive and/or purge some old records once in a while. The query here looks promising ( although I'll have to see if we have any composite foreign keys ).
When I run the query here: http://www.mssqltips.com/tip.asp?tip=1151 I get 360 foreign key relationships and I only see one related table for the table I want to clean up. It also seems to show the ON CASCADE DELETE is in place for all these keys.
Also, if I "view dependencies" in managment studio it shows me only one table that depends on my table. Now for what seems like inconsistencies: If I do a database diagram, add my table, then add all related tables I get quite a few.
We all know disk space is cheaper, but we don't have an infinite supply of it and keeping too many old records is a performance hit for the application and for maintenance windows. So how can one definitely identify the delete statements that won't leave orphans anywhere?
July 12, 2011 at 10:14 am
Another approach to disk space savings would be changing clustered indexes ( based on an increasing data type such as integer ) to have a fill factor of 100 instead of 90 like several of ours. Testing showed that doing this on one table saves almost 10GB of disk space
July 12, 2011 at 10:23 am
I don't use FKs to cascade deletes. I map out the actual data dependencies, and build scripts/procs that explicitly handle the data correctly.
This avoids a couple of issues. First, you can end up with On Delete Cascade failing if SQL Server thinks it might end up in a loop when you're adding the constraint. For example, you can't add it to both ends of a many-to-many join table. Second, I really hate hiding code from future developers/DBAs, including myself. A constraint that updates/deletes data can be just as bad as triggers, et al. Third, it allows for easier refactoring if, for example, it is decided that another table needs to be added to the archive data instead of just being deleted. Fourth, I add FKs to lookup tables, and I don't want actual data being deleted if a lookup table value is mistakenly identified as "we no longer use that and can delete it".
On the fill factor issue, it really depends on how you update/insert/delete data. 100% can certainly save you some space. It can also result in a lot of page splits and kill performance if you routinely update data after it's been inserted. I don't know your database, so you'll have to judge that factor, of course.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply