The importance of SQL Check Constraints

Not long ago I developed a database that was missing the proper check constraints on the columns. During the development of the application this was not a problem because I was doing all of the validation in the application. For example, I was checking that phone numbers and zip codes all used the same formats. The application was base on Volunteers and Events so searches against the database were based on fields such as phone numbers and zip codes to find volunteers. After the application was implemented into production all was going well until they noticed that many volunteers with matching phone numbers and zip codes were not coming back in the searches. I found out later that backend imports were being performed against the database. The import program had phone numbers and zip codes that were formatted differently. When the SQL Query was executed it was looking for a phone number in the format of ‘(888)222-2222’. Although the volunteer existed in the database, the phone number was formatted such as this: 888-222-2222 and the records were not returned. If I would have put the proper check constraints on my phone number and zip code columns this would not have been a problem.

http://msdn.microsoft.com/en-us/library/ms179491.aspx