Defensive Db Programming Chapter 06

Comments: No Comments
Published on: August 30, 2010

We are here again after another week and ready for another episode in this series.  Today we get to talk about chapter 6 in the book by Alex Kuznetsov (Blog).  You can find more on this series by looking here.

Common Problems with Data Integrity

Alex explains to us that SQL server has built-in tools to help maintain the integrity of the data.  He quickly lists out three areas that can be useful in maintaining Data Integrity, as well.

  1. Applications
  2. Constraints
  3. Triggers

Application

Data integrity can be enforced from the application.  It should be noted that this could be an inefficient method of ensuring the integrity of the data.  If you try to do it from the application, be prepared for a little pain.

Constraints

It is well known that using constraints is the most reliable way to enforce data integrity rules.

Using constraints in the database is helpful and quick.  If data does not meet the constraint criteria, we can prevent it from being added.  There are different kinds of constraints and one should become familiar with the various constraints.  In this section, Alex also demonstrates the differences for the three states of constraints.  It is important to understand the state of the constraint to ensure the integrity of the data is at the level your expect.

There is a section devoted to UDFs in this chapter as well.  The section is within the Constraints section and should be read.

Triggers

The chapter is concluded with the discussion on using Triggers for enforcing data integrity.  Triggers are suitable in some areas where a constraint would not be suitable for enforcing data integrity.  Triggers do have their own problems that should be considered too though.  Some of the problems are with how the trigger was coded and some are inherent with the use of triggers.  It is well worth learning about these limitations by reading this section as well.  Test the samples provided and implement some of the suggestions in your triggers.

Conclusion

Alex concludes this chapter with the following statement:

Data integrity logic in the application layer is too easily bypassed. The only completely
robust way to ensure data integrity is to use a trusted constraint. Even then, we need to
test our constraints, and make sure that they handle nullable columns properly.

We also have a nice set of reminders of what to include in our assumptions to be tested.  The short of it, check your constraints and test as many scenarios as necessary based on your assumptions.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *








Calendar
August 2010
M T W T F S S
« Jul   Sep »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
Content
SQLHelp

SQLHelp

  • @SQLSoldier: @vNetNinja No, they can't. Just failover. #sqlhelp
  • @vNetNinja: Can SQL stretch clusters be used active/active, or just failover? #sql #sqlhelp
  • @NedOtter: @SQLSoldier Thanks -- just wanted to hear from the "voice of experience...." #sqlhelp
  • @SQLSoldier: @NedOtter Yes, can be done in place. Edition upgrade. Should be no issues, but you know, things happen. #sqlhelp
  • @mikedavem: #sqlhelp Do I have to enable traceflag or others to use hadr_backup_msg XE event? When performing a backup nothing is recorded.
  • @Harsha547: RT @iChoo_remake: SELECT * FROM table WHERE (id % 2) = 0; # even SELECT * FROM table WHERE (id % 2) > 0; # odd #PHP #sqlhelp
  • @NedOtter: If u run SQL Std and want to change edition to Enterprise, possible to do that "in-place"? Any issues encountered? #sqlhelp
  • @SQLFlipFlopsDBA: upgrade 14 to 16 sql server, error stating no features selected for upgrade, but all boxes checked.Both enterprise edition,licensed #sqlhelp
  • @tjaybelt: #sqlhelp it safely? Will versioning be an issue? This package was created in VS 2010 or 2012 we think. And runs in 2014. We have no source
  • @tjaybelt: #sqlhelp I have an SSIS package i need to edit. Its part of a catalog of many packages. Can I suck it out of SQL Server, edit it, & deploy

Welcome , today is Friday, July 1, 2016