SQL Server Blogs

Voices from the UK SQL Server Community
Welcome to SQL Server Blogs Sign in | Help
in Search

My two cents

Tips, opinions, tests (and misspellings) from a Brazilian DBA in UK

Data purity

Reviewing DBCC CHECKDB syntax for SQL Server 2005, I found a [DATA_PURITY] option added to its syntax.

This option enable the following checks on each column value of those datatypes:

Unicode character - The data length should be a multiple of 2.

Datetime - The days field should be between Jan 1 1753 and Dec 31 9999. The time field must be earlier than '11:59:59:999PM' .

Real and Float - Check for existence of invalid floating point values like SNAN, QNAN, NINF, ND, PD, PINF.

For a database created on SQL Server 2005, these checks are always performed, but upgraded databases won't do it until you explicitly call using that option. If it reports no errors, then all subsequent CHECKDB in that database will perform those checks automatically.

Add this in your migration checklist.

KB923247 has all the details about it. The guys at mssqltips.com also have a good article about it.

Published Nov 26 2007, 10:39 PM by leo.pasta
Filed under: ,


No Comments
Powered by Community Server (Commercial Edition), by Telligent Systems