It's important to consider how to apply all of these tools to common situations. Bear in mind that for the most part, most of the situations we protect against and the errors that will be sent to the client should rarely if ever occur. The UI and application layer should prevent them. Our goal in placing these protections is twofold. First, without a guarantee, mistakes will often be made. There are too many clients and too many situations to guarantee perfection in the object layers that are created. Having this impenetrable layer of protection protects us from any mistakes being made. Second, there are performance benefits as well. Many of the objects that we've looked at will improve performance:
Using proper data types can reduce storage overhead and prevent comparisons between unlike types eliminating index utilization.
Limiting NULLs can help the optimizer not consider NULL as a possibility, and make comparisons easier.
Uniqueness constraints can help the optimizer know that only one value can match for a query.
Using proper indexes (including unique indexes) generally helps performance unless grossly overused, even ones on a view or filtered index (with a WHERE clause).
Using foreign key constraints helps the optimizer to know whether related values exist.
Using check constraints (when trusted) lets the optimizer know what sorts of values can be in a table, so searching for an illegal value is a known fail.
In general, the major problem with all of these objects is that they take work. So unless you have an advocate for the process, it's often quite hard to make the case for database protection that duplicates some of the protection that's in the application code. The goal of this article is to give you a succinct overview of the different types of objects in terms of types of things you're trying to protect against. Independent of performance, there's a tremendous amount of overlap in which tools you use to solve any problem. Triggers could be written to do all data validation, and in the early (dark) ages of SQL Server, triggers, NULL specifications, and unique indexes were all we had for implementation. There were no constraints whatsoever and we made do. Clearly a trigger-only approach isn't the best practice today; rather, the best practice is to use the tools in the order we first discussed, considering whether you can use the technique mentioned to cover your particular need:
Data types to constrain data to a basic physical structure that best matches the need.
NULL specifications to keep NULL data out of columns that shouldn't have NULLs (this is one of the worst offences that people do: letting values be NULL so that the UI can send multiple updates).
Uniqueness constraints to make sure that the set of values for some column or columns only have unique values.
Filtered indexes to implement selective uniqueness.
Foreign key constraints to make certain that related values have matches.
Check constraints to implement checks on the same row, as well as checks on other tables when you want them to happen row by row.
Triggers for everything else you need to validate in the table, as well as introducing side effects to a query. They're seldom used as they're rarely needed, but they're tools that you can use on occasion.
Sadly this topic also becomes a polarizing political topic as well. There are many application developers who'll be annoyed that you want to use constraints to manage anything. They'll become incensed at the errors that are sent to their client by your constraints, and will demand their removal. Even though your goal is to only use constraints to manage "never-occurring" errors, the fact is that they weren't prepared for bugs in the application code that allow errors to occur. This is a defining point in your career when you can, while making them feel good about themselves, explain why this is spurious reasoning. There are two reasons why this reasoning is less than sound. First, if the errors are not supposed to occur, nobody should notice your constraints in the first place. Second, you can't stop all errors from happening. SQL Server could encounter a hardware glitch and an error might occur. More importantly, deadlocks aren't 100 percent preventable, so your application code needs to be aware that a deadlock might occur and be able to handle it. Knowing what changes were running during a transaction (which you need for a deadlock handler to retry the transaction) is the same thing you need to know for an error handler.
Taking this knowledge that you need to protect against errors already, and noting that it's rare if ever that you should see an error from a constraint raised to even the application code (usually the only reason you should ever see an error occur is a bug or some trouble with your database), it should be clear why you need to protect your data in SQL Server. Now the onus is on you to carefully consider how enforceable and permanent each rule you are attempting to enforce is. It doesn't behoove you to build a lot of constraints and triggers that have to be frequently changed, and it's best if you can use some client code or stored values to data-drive as many of the variables to your systems as you can. For example, if you can give a customer a variable amount of discount, based on external settings, don't hard-code the settings: build a table and let the client code look it up. As a final example, consider the salary example from before. You, being the intrepid reader, probably noted that salaries are bound by position, and a new DBA's salary might be bound by the current year's salary bounds. You may even want to create a table that contains the minimum and maximum salary that can be offered. Although this might sound like a rigid rule that could easily be built into a constraint or trigger, this isn't a likely candidate for rigid database constraints, because no matter what the database says, we all know that if you had a shot to get Kalen Delaney to come work for your company as a regular employee, you'd quickly break the salary rules and give her what she wanted (and perhaps a tad more than she wanted to make sure she stayed happy). Having rules be optional like this prevents the user from needing to make a new phony position with its own salary rules to handle fringe cases. A simple piece of data indicating who chose to override the common rules is more than enough.
On the other hand, you definitely want to make sure that you use a proper data type to store the salary amount and a check constraint to make sure only positive values are inserted. In some cases, you might add a column that indicates who overrode the rules and then ignore checking the rules, but again, it can end up like spaghetti code if you start to build a web of enforcement rules that allow too much overriding.
Our website is not responsible for the information contained by this article. Articleinput.com is a free articles resource thus practically any visitor can submit an article. However if you notice any copyrighted material, please contact us and we will remove the article(s) in discussion right away.
Note: This article was sent to us by: Donald Smith at 01072010
1. Python functions handle variable numbers of arguments
All articles are property of their respective authors. Please read our Privacy Policy!
© 2009 ArticleInput.com.