SQL Query Performance of union & case

Last night I came across a question posted on Experts Exchange website. The user was asking which performance would be better, case when or union. It is a very interesting question, and a lot professionals give their opinions. (well without elaborating or explain it) and the verified so called “best” answer, that developer very firmly announced it is “case”  much better that “union”. But is it true? or will it be true for all kinds of cases?

Here I paste my two queries below, and explain–if it’s true, case always better than union.

QUERY 1:

SELECT DISTINCT caseID, ‘CPT’ as caseC, CPT, CPTMod
FROM [AQINACORVendorLoad]..[P606_Load_V_XUAN]
WHERE ( CASE
WHEN ISNUMERIC(CPT)=0 THEN 0
WHEN CAST(CPT AS INT) > 1999 THEN 1
ELSE 0
END ) = 1

union
SELECT DISTINCT caseID, ‘ASACPT’ as caseC, CPT, CPTMod
FROM [AQINACORVendorLoad]..[P606_Load_V_XUAN]
WHERE ( CASE
WHEN ISNUMERIC(CPT)=0 THEN 0
WHEN CAST(CPT AS INT) < 2000 THEN 1
ELSE 0
END ) = 1
union
SELECT DISTINCT caseID, ‘CPT’ as caseC, CPT, CPTMod — INCLUDED every CPT with character symbol
FROM [AQINACORVendorLoad]..[P606_Load_V_XUAN]
WHERE ISNUMERIC(CPT)=0
AND CPT IS NOT NULL
and CPT <>”

QUERY 2:

SELECT DISTINCT caseID,

CASE WHEN ISNUMERIC(CPT)=0 OR (ISNUMERIC(CPT)=1 AND CAST(CPT AS INT)>1999) THEN ‘CPT’
ELSE ‘ASACPT’  END AS caseC, CPT, CPTMod
FROM [AQINACORVendorLoad]..[P606_Load_V_XUAN]
WHERE CPT IS NOT NULL and CPT <>”

I’m not using other articles or  explain what happened in each statement in a tedious way.

Straightforward. I run this two statement and comparing the execution plan. And please, too many people just imaging what is happening when you run your query, if you ever have question of the performance, the actual execution plan is always better than asking other “expert”.

union:

case:

And let’s see the property details:

Noticing that even the second  query(case when) is simpler and shorter, however more memory need to be granted before it can run. even though the memory was really used less.

so if you have larger data-set, and scan table need more memory for each of them, apparently the “case” statement need more memory, if there is couple of other queries is running the same time, and grant memory can not be satisfied, SQL server will wait until there is enough space for this query.

I just check the statements performance in my case, will it other situation case statement better than union/union all? To be honestly, I don’t know. But always remember comparing the performance in actual plan, see the memory, computer I/O, that would be developers’ best approach.