Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a CTE Referenced Twice in the Same Query Computing Its Result Set Twice Because SQL Server Doesn’t Materialize a Non-Recursive CTE by Default

- 08.11.26 - ErcanOPAK

πŸ—„οΈ SQL Server: Fix a CTE Referenced Twice in the Same Query Computing Its Result Set Twice Because SQL Server Doesn’t Materialize a Non-Recursive CTE by Default

A Common Table Expression is effectively a named, inline view for the duration of a single query β€” SQL Server doesn’t automatically compute it once and cache the result for reuse within that same query. If the same CTE name is referenced twice, say once to compute a value and once to join against it, the optimizer is free to, and typically does, expand and re-execute the CTE’s underlying logic independently for each reference, doubling the actual work for what reads like a single reusable definition.

πŸ”Ž The Problem

WITH RecentOrders AS (
    SELECT CustomerId, SUM(Total) AS Spend
    FROM Orders
    WHERE OrderDate > DATEADD(month, -3, GETDATE())
    GROUP BY CustomerId
)
SELECT c.Name, r.Spend
FROM Customers c
JOIN RecentOrders r ON r.CustomerId = c.Id
WHERE r.Spend > (SELECT AVG(Spend) FROM RecentOrders); -- recomputed independently here

βœ… Fix: Materialize the Expensive Part Once

  • Materialize the result explicitly into a temp table (SELECT ... INTO #RecentOrders) or a table variable when a CTE referenced more than once has an underlying computation expensive enough to matter, so every reference reads the same already-computed rows instead of re-deriving them.
  • Check the actual execution plan, not just the query text, for the CTE’s underlying subquery appearing more than once β€” that’s the direct evidence the optimizer chose to expand rather than share it, confirming whether this fix is actually worth applying for a given query.
  • Leave a genuinely cheap CTE β€” a small table, a simple filter β€” as-is; materializing adds its own temp table I/O cost, so this fix pays off specifically when the CTE’s own computation (an aggregation, a join, a window function) is the expensive part being duplicated.

🚨 Why This Is Dangerous

Because the query still returns correct results either way, this is purely a performance issue that can go unnoticed until the underlying tables grow large enough that the duplicated computation’s cost becomes the dominant cost in the whole query β€” often first noticed as β€œthis query used to be fine” rather than as an obvious logic bug.

A CTE reads like you defined something once β€” SQL Server reads it as you defining something every time you mention it, and a non-recursive CTE referenced twice is, as far as the optimizer’s concerned, two separate requests to go compute the same thing again.

β€” Database Engineer

Related posts:

SQL: Optimize Complex Joins for Large Datasets

SQL: Optimize Complex Joins for Large Datasets

SQL: Use Window Functions for Advanced Analytical Queries

SQL: Use Window Functions for Advanced Analytical Queries

SQL NULL Handling Breaks Logic

SQL NULL Handling Breaks Logic

Views: 7

Post navigation

The AI Prompt That Reads a Confusing Compiler Error Message and Explains Which Line Actually Caused It, Not Just Where It Surfaced
Windows 11: Fix File Explorer’s Search Finding a File’s Name but Never Its Contents on a Second Drive Because Indexing Options Only Covers That Drive’s Properties by Default

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (608)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (546)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com