SQL Server Magazine

Data Integrity
People store a table's data integrity rules in the database for many reasons, particularly centralization and consistency. However, I've often heard the argument that data integrity rules add overhead and that it's not worth the performance cost to store rules in the database. My response to this argument is that there's no free lunch—you have to pay for data integrity somewhere. The main problem I have with storing data integrity rules outside the database is that often, application integrity costs aren't measured the same way that database inserts are measured; as a result, when business logic is moved to the client or middle tier, the server appears faster. So, although you might see better server-side throughput, you should realize that the work is still being performed, you're just distributing it.

To some people, this distribution is really the point. In the end, you might be able to handle more requests by taking some of your business logic off of the server. However, you might also end up with data-integrity problems when you upgrade the application or access your data through applications that don't include this business logic, such as SQL Server Management Studio.You might end up paying for these gains by spending extra time repairing bad data.

So, although some arguments for storing the business logic elsewhere are good, I strongly suggest storing data integrity rules as close to the data as possible. If data integrity is important in your environment, you must consider constraints. The most costly constraints are foreign key constraints because they must do data integrity checks when rows are inserted or updated in the referencing table as well as when a referenced table is updated or deleted. Verifying a foreign key value on the insert or update of a row in the referencing table is relatively easy because a foreign key can reference only primary or unique keys. (Primary and Unique keys enforce uniqueness through the creation of an index, which makes the verification of the referenced value fast.) However, if a record must be updated or deleted from the referenced table, SQL Server must perform a reverse lookup. SQL Server must verify whether any rows reference the value being updated or removed. In this case, you must check the column that contains the foreign key to see whether any rows contain that value. If the column doesn't have an index, the data values must be scanned. Maintaining this relationship (without indexes) can be very costly. As a tip, consider manually indexing your foreign key columns. This technique will not only minimize the cost of maintaining the primary or foreign key relationship, but it might also help improve your join performance in some cases.

Working from the List
I've given you a first look at a database-design laundry list for taking advantage of features that can offer many performance gains and help you avoid problems you might not have even realized you'd have. Designing your database for performance and performance engineering can provide huge rewards for your business, and using the resources you have—efficiently—is the best way to reap those rewards.You just need to be willing to invest the time to apply these basic principles to your current applications and database operations processes, hone your skills through experience and research, and document and pass on your skills to others in your organization. In an upcoming article, I'll cover the technical items you need to have in your indexing, maintenance, and statistics lists.

End of Article

Prev. page     1 2 3 [4]     next page -->