Forcing SQL Server To Order Operations

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.

QueryPlans

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.

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

1 thought on “Forcing SQL Server To Order Operations”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s