Please correct me if I'm wrong, but doesn't the engine switch COALESCE out for a CASE behind the scenes, meaning that it actually IS sargable?
Also, regardless of the sargability (is that a word? it should be!) would this only be applicable if the column has an index on it? If it's a varchar with NULL and repeated values, I think the chances of an index are pretty slim :)
Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net
Articles.
You are correct that a COALESCE is just a CASE expression with an alternate syntax.
No... Neither a COALESCE nor a CASE expression in the WHERE clause are sargable. In fact, off the top of my head, the only two functions that are sargable are CAST & CONVERT. And even then it only works with certain datatypes.
Is sargability as word? I get the red squiggly in all browsers and MS Word... But if you ask Google to
find sargibility it'll ask if you mean sargability...
So I suppose it depends on who's dictionary you're using...
Yes... You are correct in saying that, if no index exists on the column, then it doesn't matter... But that's a big IF. There's nothing that would or should keep a nullable (another
iffy word) column from being an index key.
In either case, using functions in the WHERE clause is just a bad habit. Even if there isn't an index on that column today, doesn't mean that one won't be created down the
road.
Below is a quick little demo...
IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
FirstNumber INT NOT NULL PRIMARY KEY,
SecondNumber INT NULL
);
-- run with & without the nonclustered index --
CREATE NONCLUSTERED INDEX ix_temp ON #temp (SecondNumber) INCLUDE (FirstNumber);
GO
WITH n (n) AS (
SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
), Tally (n) AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM n n1, n n2, n n3, n n4, n n5, n n6
)
INSERT #temp (FirstNumber,SecondNumber)
SELECT
t.n,
CASE WHEN t.n % 10 > 0 THEN t.n END
FROM
Tally t
;
--=======================================================================
DECLARE
@n INT,
@b1 DATETIME2(7),
@b2 DATETIME2(7)
;
SET @b1 = SYSDATETIME();
SELECT
@n = t.FirstNumber
FROM
#temp t
WHERE
t.SecondNumber < 10000 OR t.SecondNumber IS NULL
;
SET @b2 = SYSDATETIME();
SELECT
@n = t.FirstNumber
FROM
#temp t
WHERE
COALESCE(t.SecondNumber, 0) < 10000
;
SELECT
DATEDIFF(ms, @b1, @b2) AS Query_1,
DATEDIFF(ms, @b2, SYSDATETIME()) AS Query_2
;
In any case... Hope I didn't come off as a ball buster. The only reason I harp on it is because it's one of my own personal bad habits that I have to keep reminding myself to break. The COALESCE syntax makes for cleaner looking code... Which make too damn
tempting...
Jason Long
