How to Use Common Table Expressions (CTEs) in SQL Server for Readable Queries

Common Table Expressions (CTEs) are a powerful SQL Server feature that can dramatically improve query readability and maintainability.

Introduced in SQL Server 2005, CTEs let you define a temporary result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or MERGE statement.

Basic CTE Syntax

A CTE follows this pattern:

WITH CTE_Name AS (
    -- Your query here
)
SELECT * FROM CTE_Name;

The main components are:

The WITH keyword to start the CTE
A name for your CTE
The AS keyword
Parentheses containing your query
A statement that references the CTE

Why Use CTEs?

CTEs offer several advantages:

Improved readability: Breaking complex queries into named, logical segments
Self-referencing capability: Useful for hierarchical or recursive data
Query simplification: Reducing nested subqueries
Code reusability: Using the same temporary result multiple times in a query

Simple CTE Example

Here's a basic example that calculates average order values by customer category:

-- Without CTE
SELECT 
    c.CustomerCategory,
    SUM(o.TotalAmount) / COUNT(DISTINCT o.OrderID) AS AvgOrderValue
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerCategory;

-- With CTE
WITH OrderSummary AS (
    SELECT 
        c.CustomerCategory,
        o.OrderID,
        o.TotalAmount
    FROM Customers c
    JOIN Orders o ON c.CustomerID = o.CustomerID
)
SELECT 
    CustomerCategory,
    SUM(TotalAmount) / COUNT(DISTINCT OrderID) AS AvgOrderValue
FROM OrderSummary
GROUP BY CustomerCategory;

The CTE version clearly separates the data gathering from the aggregation logic.

Multiple CTEs in a Single Query

You can chain CTEs for even more complex scenarios:

WITH 
CustomerOrders AS (
    SELECT 
        c.CustomerID,
        c.CustomerName,
        COUNT(o.OrderID) AS OrderCount
    FROM Customers c
    LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID, c.CustomerName
),
OrderCategories AS (
    SELECT
        CustomerID,
        CASE 
            WHEN OrderCount = 0 THEN 'Inactive'
            WHEN OrderCount BETWEEN 1 AND 5 THEN 'Regular'
            ELSE 'VIP'
        END AS CustomerCategory
    FROM CustomerOrders
)
SELECT 
    c.CustomerName,
    o.CustomerCategory
FROM CustomerOrders c
JOIN OrderCategories o ON c.CustomerID = o.CustomerID
ORDER BY o.CustomerCategory, c.CustomerName;

Recursive CTEs

One of the most powerful CTE features is recursion, which is perfect for hierarchical data like organizational charts or category trees:

WITH EmployeeHierarchy AS (
    -- Anchor member (starting point)
    SELECT 
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL -- Start with top-level employees
    
    UNION ALL
    
    -- Recursive member (references itself)
    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    EmployeeName,
    Level,
    REPLICATE('--', Level) + EmployeeName AS HierarchyDisplay
FROM EmployeeHierarchy
ORDER BY Level, EmployeeName;

This query produces an indented organization chart starting from top-level managers.

CTEs vs. Temporary Tables or Table Variables

Unlike temporary tables or table variables, CTEs:

Exist only during query execution
Don't require explicit cleanup
Can't have indexes added to them
Are primarily for improving query structure and readability

Best Practices

Use meaningful names that describe what the data represents
Keep individual CTEs focused on a single logical operation
Comment complex CTEs to explain their purpose
Consider performance - CTEs are not always more efficient than subqueries
Avoid excessive nesting - if your query becomes too complex, consider stored procedures or multiple queries

When Not to Use CTEs

CTEs might not be the best choice when:

You need to reference the same large dataset multiple times (temp tables may be more efficient)
You need to add indexes for performance optimization
Your recursive CTE might exceed the default recursion limit (100)

By mastering CTEs, you can write SQL that's not only more maintainable but also easier to understand and debug.

4/2/2025

sql

How to Use Primary Constructors for Compact Classes in C#

Primary constructors, introduced in C# 12, offer a more concise way to define class parameters and initialize fields.

This feature reduces boilerplate code and makes classes more readable.

Traditional Approach vs Primary Constructor

Before primary constructors, you would likely write something like the following:

public class UserService
{
    private readonly ILogger _logger;
    private readonly IUserRepository _repository;

    public UserService(ILogger logger, IUserRepository repository)
    {
        _logger = logger;
        _repository = repository;
    }

    public async Task<User> GetUserById(int id)
    {
        _logger.LogInformation("Fetching user {Id}", id);
        return await _repository.GetByIdAsync(id);
    }
}

With primary constructors, this becomes:

public class UserService(ILogger logger, IUserRepository repository)
{
    public async Task<User> GetUserById(int id)
    {
        logger.LogInformation("Fetching user {Id}", id);
        return await repository.GetByIdAsync(id);
    }
}

Key Benefits

Reduced Boilerplate: No need to declare private fields and write constructor assignments
Parameters Available Throughout: Constructor parameters are accessible in all instance methods
Immutability by Default: Parameters are effectively readonly without explicit declaration

Real-World Example

Here's a practical example using primary constructors with dependency injection:

public class OrderProcessor(
    IOrderRepository orderRepo,
    IPaymentService paymentService,
    ILogger<OrderProcessor> logger)
{
    public async Task<OrderResult> ProcessOrder(Order order)
    {
        try
        {
            logger.LogInformation("Processing order {OrderId}", order.Id);
            
            var paymentResult = await paymentService.ProcessPayment(order.Payment);
            if (!paymentResult.Success)
            {
                return new OrderResult(false, "Payment failed");
            }

            await orderRepo.SaveOrder(order);
            return new OrderResult(true, "Order processed successfully");
        }
        catch (Exception ex)
        {
            logger.LogError(ex, "Failed to process order {OrderId}", order.Id);
            throw;
        }
    }
}

Tips and Best Practices

Use primary constructors when the class primarily needs dependencies for its methods
Combine with records for immutable data types:

public record Customer(string Name, string Email)
{
    public string FormattedEmail => $"{Name} <{Email}>";
}

Consider traditional constructors for complex initialization logic

Primary constructors provide a cleaner, more maintainable way to write C# classes, especially when working with dependency injection and simple data objects.

4/2/2025

How to Encode a String for a URL in C#

When working with URLs in C#, encoding is essential to ensure that special characters (like spaces, ?, &, and =) don’t break the URL structure. The recommended way to encode a string for a URL is by using Uri.EscapeDataString(), which converts unsafe characters into their percent-encoded equivalents.

string rawText = "hello world!";
string encodedText = Uri.EscapeDataString(rawText);

Console.WriteLine(encodedText); // Output: hello%20world%21

This method encodes spaces as %20, making it ideal for query parameters.

For ASP.NET applications, you can also use HttpUtility.UrlEncode() (from System.Web), which encodes spaces as +:

using System.Web;

string encodedText = HttpUtility.UrlEncode("hello world!");
Console.WriteLine(encodedText); // Output: hello+world%21

For .NET Core and later, Uri.EscapeDataString() is the preferred choice.

204

4/2/2025

String Interpolation in C#: A Cleaner Way to Format Strings

String interpolation, introduced in C# 6.0, provides a more readable and concise way to format strings compared to traditional concatenation (+) or string.Format(). Instead of manually inserting variables or placeholders, you can use the $ symbol before a string to directly embed expressions inside brackets.

string name = "Walt";
string job = 'Software Engineer';

string message = $"Hello, my name is {name} and I am a {job}";
Console.WriteLine(message);

This would produce the final output of:

Hello, my name is Walt and I am a Software Engineer

String interpolation can also be chained together into a multiline string (@) for even cleaner more concise results:

string name = "Walt";
string html = $@"
    <div>
        <h1>Welcome, {name}!</h1>
    </div>";

4/2/2025

Menu

Resources

Contact

Popular tags

How to Use Common Table Expressions (CTEs) in SQL Server for Readable Queries

Basic CTE Syntax

Why Use CTEs?

Simple CTE Example

Multiple CTEs in a Single Query

Recursive CTEs

CTEs vs. Temporary Tables or Table Variables

Best Practices

When Not to Use CTEs

Related

Traditional Approach vs Primary Constructor

Key Benefits

Real-World Example

Tips and Best Practices

Menu

Resources

Contact

Popular tags

How to Use Common Table Expressions (CTEs) in SQL Server for Readable Queries

Basic CTE Syntax

Why Use CTEs?

Simple CTE Example

Multiple CTEs in a Single Query

Recursive CTEs

CTEs vs. Temporary Tables or Table Variables

Best Practices

When Not to Use CTEs

Related

Traditional Approach vs Primary Constructor

Key Benefits

Real-World Example

Tips and Best Practices

Popular Questions