July 17, 2011 at 11:33 am
I think there are places where DRI isn't needed, but not a lot. Most of the time it pays to enforce the FK relationships, or the constraints because they ensure that are enforced.
A good application doesn't necessarily need DRI, but that's if the developer is careful and consistent. There are plenty of those people, but DRI comes into play when someone decides they need to make a change via T-SQL (SSMS/SQLCMD/etc), or another application starts to query your database, or there is another developer loading data with SSIS, that might not be careful.
DRI isn't about a single person. It's the insurance and backstop for concurrent changes, written by multiple people.
Note that it can help performance at times. The query optimizer does take FKs into account in some query plans and can "skip" some reads against tables if it knows the FK is enforced.
July 17, 2011 at 1:33 pm
DRI rocks 🙂 , I am too paranoid to blindly trust that every developer accessing databases will always keep in mind basic DB principals esp cause if anything goes wrong I am the guy who has to fix it.
We have a mix of developers who have little business knowledge and don't understand the data and then we have .Net developers who can write simple stored procedures and then we have ORM and random object being created.
In this mix DRI is the only thing keeping most of us sane.
November 5, 2018 at 10:03 pm
Tony Savoie - Thursday, July 14, 2011 7:00 AMDRI should be the default, with very rare exceptions that need to be proven as exceptional 😉 I've been burned too many times, when the sh!t hits the fan and someone in upper management is screaming because their data is garbage.They arent talking to the developers, they are talking to the DATAbase Admins.If your data is important to you, performance issues due to dri are easily solved. If the data is not important enough to care about it being mangled, well thats what Access is for 😉 The devs can cry all they want, but I've proven to managment the value of a real dba.
Hi, was looking for info on removing DRI from a SQL table as SSMS seems designed to frustrate me by insisting on dropping tables to make any changes, yes there is a flag to allow this but no I'm not the DBA so cant set it. Anyway I digress. Access developer for 15 years, DBA for 3, BA for 6, Data Analyst for 5. Ive written a dozen commercial systems, most of which are still running after 10 years. I built the GUI's and reports in Access, based on a rock solid SQL Server backend with DRI fully enforced. My point is studies have shown Access as superior in terms of cost to develop AND reliability for desktop applications if using a SQL Server backend, I know there are users out there who create havoc with it but there are plenty of developers creating virtually unusable GUI's and charging a fortune for it and making users lives miserable. Its a wonderful option for small to medium businesses who need a custom solution and cant afford the timeframes of C# and web dev tools, time is money after all.
November 5, 2018 at 11:12 pm
cengland0 - Thursday, July 14, 2011 3:12 AMSometimes I use DRI but most of the time I do not. When DRI is used, it makes truncating tables difficult. You need to do them in a very specific order. Also, when adding test data, or importing data from other sources, it also has to be installed in a specific order. I cannot tell you how many times I get data from an SSIS package and the data at the source is not up to par. If I turn on DRI, the entire package will fail and none of the tables will load. We at least need to get the data that is available and we can work with the small quantity of errors in our reports because it only represents a small sample.
I personally am with using DRI, if the SSIS package fails due to constraint issues, it is a good thing, as you are guaranteeing accuracy of data in your tables that are loaded by SSIS.
Just a few months back, i designed a table structure for a OLTP application where when i was enforcing constraints, and many developers wanted to get rid of it. After a lot of discussion i finally gave in and stopped using constraints on a particular table and would let the developer handle the logic in the application. Recently plenty of erroneous records started reaching this table, even though the application developers were dead sure would enforce the uniqueness of data in the table.
On digging further,the cause of the duplicates, was due to the application layer, performing "multi threading" when trying to bulk insert records in one go. Even though the individual constraint checks worked, in the multithreaded scenario there were overlapping records across threads which resulted in duplicates.
November 7, 2018 at 6:53 am
brancompserv - Monday, November 5, 2018 10:03 PMTony Savoie - Thursday, July 14, 2011 7:00 AMDRI should be the default, with very rare exceptions that need to be proven as exceptional 😉 I've been burned too many times, when the sh!t hits the fan and someone in upper management is screaming because their data is garbage.They arent talking to the developers, they are talking to the DATAbase Admins.If your data is important to you, performance issues due to dri are easily solved. If the data is not important enough to care about it being mangled, well thats what Access is for 😉 The devs can cry all they want, but I've proven to managment the value of a real dba.Hi, was looking for info on removing DRI from a SQL table as SSMS seems designed to frustrate me by insisting on dropping tables to make any changes, yes there is a flag to allow this but no I'm not the DBA so cant set it. Anyway I digress. Access developer for 15 years, DBA for 3, BA for 6, Data Analyst for 5. Ive written a dozen commercial systems, most of which are still running after 10 years. I built the GUI's and reports in Access, based on a rock solid SQL Server backend with DRI fully enforced. My point is studies have shown Access as superior in terms of cost to develop AND reliability for desktop applications if using a SQL Server backend, I know there are users out there who create havoc with it but there are plenty of developers creating virtually unusable GUI's and charging a fortune for it and making users lives miserable. Its a wonderful option for small to medium businesses who need a custom solution and cant afford the timeframes of C# and web dev tools, time is money after all.
So, hot tub or DeLorean?
November 7, 2018 at 8:04 am
Steve Jones - SSC Editor - Sunday, July 17, 2011 11:33 AMI think there are places where DRI isn't needed, but not a lot. Most of the time it pays to enforce the FK relationships, or the constraints because they ensure that are enforced.A good application doesn't necessarily need DRI, but that's if the developer is careful and consistent. There are plenty of those people, but DRI comes into play when someone decides they need to make a change via T-SQL (SSMS/SQLCMD/etc), or another application starts to query your database, or there is another developer loading data with SSIS, that might not be careful.DRI isn't about a single person. It's the insurance and backstop for concurrent changes, written by multiple people.Note that it can help performance at times. The query optimizer does take FKs into account in some query plans and can "skip" some reads against tables if it knows the FK is enforced.
... especially when properly indexed. We've got on table, in particular, that has more than a dozen FKs on it. To delete a single row was taking well over a minute. Every one wanted to disable the FKs to delete several rows and then rebuild them, which would have taken even longer. I added the proper indexes and deletes occurred in the very small millisecond range.
And, yeah... I agree with Jayanth... DRI ROCKS!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply