Functions and Implicit Conversion

Has it ever happened to you that most simple piece of code produces results that make no sense what so ever? Today, I helped a coworker fix one of those little issues that I thought deserves a post. Even though all men are created equal, SQL Server functions are not. Depending on who wrote the function and at what point in the history of SQL Server it can behave differently from what is expected.

Today I want to look at ISNULL vs COALESCE vs OR . To start will I will create a simple table and populate it with two records.

IF OBJECT_ID('dbo.BlogPost') IS NOT NULL
 DROP TABLE dbo.BlogPost

CREATE TABLE dbo.BlogPost
( ID INT
,CODE VARCHAR(10)
)
GO
INSERT INTO dbo.BlogPost
(ID, CODE)
VALUES(1, 'BOB01')
,(2, 'VLADIMIR01')
GO
SELECT *
FROM dbo.BlogPost

Results are as follows.

ID	CODE
1	BOB01
2	VLADIMIR01

Exactly what we would expect. Now unto something more fun. Let us try to limit result by use of a local variable, this would work exactly the same as a parameter in stored procedure or a function.

DECLARE @CODE VARCHAR(6) = NULL

SELECT 'ISNULL- METHOD', *
FROM dbo.BlogPost
WHERE CODE = ISNULL(@CODE, CODE)

SELECT 'COALESCE - METHOD', *
FROM dbo.BlogPost
WHERE CODE = COALESCE(@CODE, CODE)

SELECT 'OR - METHOD', *
FROM dbo.BlogPost
WHERE CODE = @CODE OR @CODE IS NULL

After running the code we get the following results

Method	ID	CODE
ISNULL- METHOD	1	BOB01

Even though we would expect to see both records returned we only get 1 record. Huh? This is exactly what puzzled a coworker, ofcourse query was not as simple as this one but same issue caused him to hit a road block.

In the case of COALESCE and OR methods, results are identical.

Method	ID	CODE
COALESCE - METHOD	1	BOB01
COALESCE - METHOD	2	VLADIMIR01

Method	ID	CODE
OR- METHOD	1	BOB01
OR- METHOD	2	VLADIMIR01

So what caused results to be different for the first function but be the same for next two functions? I'm sure the title of the post already gave it away. Implicit Conversion is what happens behind the scenes and usually when you least expect it. Unless you look at execution plan you would not be able to tell that CONVERT_IMPLICIT has ever occured. I know I can't stop raving about Plan Explorer and how much I love it, if we run same query in PE and look at the base select statement it shows right on the plan CONVERT_IMPLICIT as seen in the screenshot below.

Moral of today's story is: Don't ever assume that all SQL Server functions will behave exactly the same. In some cases, they might throw you in for a spin and you will be scratching your head at results without knowing how to fix it or what is causing it. In the case of what inspired today's post, my coworker will now be more educated and will be more cautious when working with SQL server functions. "It depends!" phrase has truly become a common theme for SQL Server operations. It is always best to test multiple ways of coding to figure out what is the best and which way will produce consistent results.

note: I did not write original code