Data integrity in SQL Server is one of those things that doesn't seem like an obvious topic for an administrator to address directly. After all, doesn't SQL Server automatically ensure the integrity of your data? Well, yes, but only up to a point. SQL Server itself won't protect you from inept handling of transactions, for instance, or breaking internal referential integrity -- two of many things that can ruin the quality of your data.
Data integrity isn't just about ensuring that you have a nightly backup in case a table goes bad (although having backups is always a good idea!). It's also about developing good habits so data doesn't become poisoned in the course of normal work.
This guide tells you how to enforce and ensure data integrity on many fronts at once -- not just in your database maintenance habits, but also programming, bounds checking and other realms that can be overlooked.
ENSURING DATA INTEGRITY IN SQL SERVER
Step 1: Back up, optimize and enable safety features
Step 2: Segregate data aggressively into files and filegroups
Step 3: Consider using implicit transactions
Step 4: Be careful how you enforce internal referential integrity through triggers
Step 5: Use constraints and relationships to keep out bad data
Step 6: Don't expose interfaces that create dynamic SQL to the end user
Step 7: Use a "check-in/check-out" mechanism for contested data
|ABOUT THE AUTHOR:|
Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
Copyright 2005 TechTarget