I'm getting lost about one thing. According to MS docs full checkdb with no option does column-value checks, so what's the gain of running data_purity? By 'gain' I mean how detailed checks are being done, I'm not considering performance now.
Do I understand it correctly that if the database was created before SQL2005 it will have dbi_dbccFlags set to 0 and full checkdb will skip column-value checks, but once such database will have at least one successfull data_purity checks the flag will be set to 2 and column-value checks will be done during every full checkdb? And if the database is created in SQL2005+ it will have dbi_dbccFlags set to 2 by default and then full checkdb with no option and with data_purity will be the same checks?
Also estimates seem to suggest that for SQL2005+ there's no distinction between these two checks, at least if it comes to space needed in TempDB:
- DBCC CHECKDB('AdventureWorks2019') WITH PHYSICAL_ONLY, ESTIMATEONLY
DBCC results for 'AdventureWorks2019'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 39189.
- DBCC CHECKDB('AdventureWorks2019') WITH ESTIMATEONLY
DBCC results for 'AdventureWorks2019'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 225996.
- DBCC CHECKDB('AdventureWorks2019') WITH DATA_PURITY, ESTIMATEONLY
DBCC results for 'AdventureWorks2019'.
Estimated TEMPDB space (in KB) needed for CHECKDB on database AdventureWorks2019 = 225996.
Best Answer
Yes, that's right. You only need to enable the DATA_PURITY option running CHECKDB on a pre-2005 created database once, if the result is clean then subsequent CHECKDBs without the option will automatically include the check. And it's part of the standard CHECKDB process in 2005 and above-created databases.
That's because it's the same check process - the option wouldn't have any effect in a SQL2005+ created database because CHECKDB does that check as standard.