Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

IX.B.3. My Data Is Messed Up!

Unless you believe in gremlins or other supernatural entities that descend from the ether and wreak havoc on your data, chances are that any information problem is because of an error or omission. Here's what to watch out for:

• Referential integrity issues: To help keep all your data synchronized, SQL Server offers referential integrity features. These prevent you or your applications from inadvertently altering rows from one table without making corresponding changes in another table. To get a better idea of how to use referential integrity to your advantage, take a look at Book II, Chapter 6.

• Failure to use transactions: Transactions help certify that your database interactions happen in logically consistent groups. Without proper transactions, an operation may update one table but fail to do the same for other tables. The result is damaged data integrity. Book III, Chapter 9 is designed to help you make the most of transactions.

• Incorrectly defined columns: Believe it or not, sometimes database designers choose the wrong data type when setting up their tables. For example, a particular field might need to contain currency amounts, which include decimals. Yet when designers write the SQL to create the table, they choose the INTEGER data type — SQL Server discards any fractional amounts from the column.

• Not enough space for character-based fields: Another common problem is database designers not providing enough space for character-based fields. Again, ...