By Richie Lee

The subject of my last post was to do with returning DBCC CHECKDB results from a temp table, which was populated by running DBCC DBINFO against each database hosted on the SQL instance. I shared a query that returned any databases that had not had a CHECKDB run against it for over 7 days. The query is below:

 
select dbi.Value from #db dbi
WHERE field LIKE '%lastknow%'
AND (CASE WHEN isDate(dbi.value) = 1 THEN CAST (dbi.Value AS DATETIME2(3))END) < DATEADD (DAY,-7, GETDATE())

That “CASE” statement stands out quite a bit doesn’t it. Let’s explore why this query needs a CASE expression. Begin by creating a temp table that contains a sample of the data stored in the temp table.

CREATE TABLE #DB (
	Db SYSNAME NULL
	,ParentObj NVARCHAR(60)
	,Obj NVARCHAR(60)
	,Field NVARCHAR(128)
	,Value NVARCHAR(128)
	)

	INSERT INTO #DB
	VALUES
 ('master',	'DBINFO STRUCTURE:',	'DBINFO @0x000000002C0ECBD0',	'dbi_dbccLastKnownGood',	'1900-01-01 00:00:00.000')
 	INSERT INTO #DB
	VALUES
('tempdb',	'DBINFO STRUCTURE:',	'DBINFO @0x000000002C0ECBD0',	'dbi_dbccLastKnownGood',	'1900-01-01 00:00:00.000')
	INSERT INTO #DB
	VALUES
('model',	'DBINFO STRUCTURE:',	'DBINFO @0x000000002C0ECBD0',	'dbi_dbccLastKnownGood',	'1900-01-01 00:00:00.000')
	INSERT INTO #DB
	VALUES
('msdb',	'DBINFO STRUCTURE:',	'DBINFO @0x000000002C0ECBD0',	'dbi_dbccLastKnownGood',	'1900-01-01 00:00:00.000')
	INSERT INTO #DB
	VALUES
('John',	'DBINFO STRUCTURE:',	'DBINFO @0x000000002C0ECBD0',	'dbi_dbccLastKnownGood',	'1900-01-01 00:00:00.000')
	INSERT INTO #DB
	VALUES
('Paul',	'DBINFO STRUCTURE:'	,'DBINFO @0x000000002C0ECBD0',	'dbi_dbccLastKnownGood',	'2015-11-22 02:00:02.903')
	INSERT INTO #DB
	VALUES
('George',	'DBINFO STRUCTURE:',	'DBINFO @0x000000002C0ECBD0',	'dbi_dbccLastKnownGood',	'2015-11-22 02:43:54.160')
	INSERT INTO #DB
	VALUES
('Ringo',	'DBINFO STRUCTURE:',	'DBINFO @0x000000002C0ECBD0',	'dbi_dbccLastKnownGood',	'1900-01-01 00:00:00.000')
	INSERT INTO #DB
	VALUES
	('Apu',	'DBINFO STRUCTURE:',	'DBINFO @0x000000002C0ECBD0',	'dbi_dbname',	'Apu')

First thing to note is that not all the values in the “value” column are a date: the last column is not a date so the column has to be a data type that is flexible enough to accommodate a variety of entries. Note that the “field” value is also different from all other values,

So logically the query below should work, but when we run it we get a type conversion error:

SELECT dbi.Value
FROM #db dbi
WHERE field LIKE '%lastknow%'
	AND CAST(dbi.Value AS DATETIME2(3)) < DATEADD(DAY, - 7, GETDATE())

Msg 241, Level 16, State 1, Line 38 Conversion failed when converting date and/or time from character string.

The issue here is that SQL is a declarative language: unlike procedural languages, there is no guarantee on the ordering of the operations, because optimizers. And SQL Server decides to do something other than what we’d expect: it tries to evaluate the value “Apu” as a date. But by using a CASE expression we can force the optimizer to take the input and match it to the expression (in this case, when a value is a date then convert it to a date) before checking if the value is older than 7 days.

select dbi.Value from #db dbi
WHERE field LIKE '%lastknow%'
AND (CASE WHEN isDate(dbi.value) = 1 THEN CAST (dbi.Value AS DATETIME2(3))END) < DATEADD (DAY,-7, GETDATE())

If we check the query plans we can see that the difference in the two queries is the filter before the select statement.

CASE statements can also be used to guarantee short-circuiting in SQL. Information about this and more can be found on this excellent article on SQL Server Central, that also contains links to two more excellent articles.