Reused Nested Decision Logic - CTE vs Copying Code

Question

I have a query which outputs one column, created through a series of CASE statements. That same column is used as part of a CASE logic for a 2nd column in the same SELECT statement.

If I were to construct a CTE with the inner logic applied so I can reference the inner logic when I have to use it as a decision later down the road. What is the overall additional overhead?

As I understand it there is not any real overhead added. (Some research and a simple test case I used are below). Are there articles that talk about this or instances where this is not the case?

Research and Simple Test Case

I found a couple of articles which don't indicate this specific question but send me in the direction of there isn't operational overhead added.

• https://www.scarydba.com/2016/07/18/common-table-expression-just-a-name/

• https://www.sqlshack.com/why-is-my-cte-so-slow/

I wrote a small query against one of our existing databases to test this theory out. The results, and execution times were the same, as well as The statistics and the Query Execution Plan.

Code, Execution Plan and Statistics for Non-CTE version:

```
SELECT PC.CompanyID,
PC.ClientID,
PC.ProgramID,
PC.PatientID,
PC.CaseID,
CASE
WHEN PFH.FulFilHdrCreateDateTime IS NULL
THEN PC.CaseCreateDateTime
ELSE
PFH.FulFilHdrCreateDateTime
END AS [ImportantDate],
DATEDIFF(Day, CASE WHEN PFH.FulFilHdrCreateDateTime IS NULL THEN PC.CaseCreateDateTime ELSE PFH.FulFilHdrCreateDateTime END, GETDATE())
FROM PATIENTCASES PC
LEFT OUTER JOIN PATFULFILLMENTHEADER PFH
ON PFH.CompanyID = PC.CompanyID
AND PFH.ClientID = PC.ClientID
AND PFH.ProgramID = PC.ProgramID
AND PFH.PatientID = PC.PatientID
AND PFH.CaseID = PC.CaseID
AND PFH.FulFilHdrID = (SELECT TOP(1) temp.FulFilHdrID
FROM PATFULFILLMENTHEADER temp
WHERE temp.CompanyID = PC.CompanyID

It looks like you've done some solid analysis here, so I may not be able to add much.

One thing that often comes to my mind when folks start throwing around CTEs is this post from Erik Darling:

CTEs, Inline Views, and What They Do


To sum things up, CTEs are a great base from which you can reference and filter on items in the select list that you otherwise wouldn’t be able to (think windowing functions), but every time you reference a CTE, they get executed. The fewer times you have to hit a larger base set, and the fewer reads you do, the better. If you find yourself referencing CTEs more than once or twice, you should consider a temp or persisted table instead, with the proper indexes.

So in your specific case, since you are not joining to the CTE, or otherwise correlating it with other datasets, it's unlikely that the CTE will cause you any issues.

Just be aware that if someone comes along and starts mucking with the query, joining the CTE to other tables, or back to itself, or adding another level of CTE "nesting" (to crunch more numbers for the final select) - then you will start to get into "operational overhead" territory.

StackExchange Database Administrators Q#241038, answer score: 5