Problem One process that should be run on a set basis to ensure integrity in your database is to run either DBCC CHECKDB or DBCC CHECKTABLE. These processes check allocation, structural, and logical integrity of the object or objects and report back any inconsistencies that are found. One thing that these processes have not check for in the past is the data itself to see if there are column values that are not valid or out-of-range.
Solution SQL Server 2005 offers a new option to the DBCC CHECKDB and DBCC CHECKTABLE commands. This new option is the "DATA_PURITY" check which will look for issues where column values are not valid or out-of-range. To run the command you issue it just as you would a regular DBCC command along with the added option, such as:
DBCC CHECKDB with DATA_PURITY
For databases that are created in SQL Server 2005, these checks are on by default, so using or not using the DATA_PURITY option does not affect the outcome of the checks when issuing a DBCC CHECKDB or DBCC CHECKTABLE.
For databases that are not created in SQL Server 2005 and then later brought into SQL Server 2005 these checks are useful and will allow you to see if there are any data issues within the database. Once this is run on a databases one of two results will occur; the first it will come back clean without problems and the second there will be data issues that need to be resolved. If the purity check comes back without any problems a entry is made in the database header and whenever a DBCC CHECKDB or DBCC CHECKTABLE is issued the DATA_PURITY checks will also be performed regardless of whether you specify the DATA_PURITY check or not.
DBCC results for "account_history". Msg 2570, Level 16, State 2, Line 1 Page (1:1073), slot 33 in object ID 1977058079, index ID 0, partition ID 129568478265344, alloc unit ID 129568478265344 (type "In-row data"). Column "account_name_japan" value is out of range for data type "nvarchar". Update column to a legal value. Msg 2570, Level 16, State 2, Line 1 Page (1:1156), slot 120 in object ID 1977058079, index ID 0, partition ID 129568478265344, alloc unit ID 129568478265344 (type "In-row data"). Column "account_name_japan" value is out of range for data type "nvarchar". Update column to a legal value. There are 153137 rows in 1080 pages for object "account_history". CHECKDB found 0 allocation errors and 338 consistency errors in table "account_history" (object ID 1977058079). CHECKDB found 0 allocation errors and 338 consistency errors in database 'BadUnicodeData'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So as you can see for any database that is migrated to SQL Server 2005 from a previous version this option should be run at least once until all of the data issues have been resolved. One this process runs clean all subsequent runs will automatically check for data purity issues.