Menu

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

  1. Use meaningful names that describe what the data represents
  2. Keep individual CTEs focused on a single logical operation
  3. Comment complex CTEs to explain their purpose
  4. Consider performance - CTEs are not always more efficient than subqueries
  5. 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.

3
49

Related

XML (Extensible Markup Language) is a widely used format for storing and transporting data.

In C#, you can create XML files efficiently using the XmlWriter and XDocument classes. This guide covers both methods with practical examples.

Writing XML Using XmlWriter

XmlWriter provides a fast and memory-efficient way to generate XML files by writing elements sequentially.

Example:

using System;
using System.Xml;

class Program
{
    static void Main()
    {
        using (XmlWriter writer = XmlWriter.Create("person.xml"))
        {
            writer.WriteStartDocument();
            writer.WriteStartElement("Person");

            writer.WriteElementString("FirstName", "John");
            writer.WriteElementString("LastName", "Doe");
            writer.WriteElementString("Age", "30");

            writer.WriteEndElement();
            writer.WriteEndDocument();
        }
        Console.WriteLine("XML file created successfully.");
    }
}

Output (person.xml):

<?xml version="1.0" encoding="utf-8"?>
<Person>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    <Age>30</Age>
</Person>

Writing XML Using XDocument

The XDocument class from LINQ to XML provides a more readable and flexible way to create XML files.

Example:

using System;
using System.Xml.Linq;

class Program
{
    static void Main()
    {
        XDocument doc = new XDocument(
            new XElement("Person",
                new XElement("FirstName", "John"),
                new XElement("LastName", "Doe"),
                new XElement("Age", "30")
            )
        );
        doc.Save("person.xml");
        Console.WriteLine("XML file created successfully.");
    }
}

This approach is ideal for working with complex XML structures and integrating LINQ queries.

When to Use Each Method

  • Use XmlWriter when performance is critical and you need to write XML sequentially.
  • Use XDocument when you need a more readable, maintainable, and flexible way to manipulate XML.

Conclusion

Writing XML files in C# is straightforward with XmlWriter and XDocument. Choose the method that best suits your needs for performance, readability, and maintainability.

1
63

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

  1. Reduced Boilerplate: No need to declare private fields and write constructor assignments
  2. Parameters Available Throughout: Constructor parameters are accessible in all instance methods
  3. 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

  1. Use primary constructors when the class primarily needs dependencies for its methods
  2. Combine with records for immutable data types:
public record Customer(string Name, string Email)
{
    public string FormattedEmail => $"{Name} <{Email}>";
}
  1. 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.

0
66

When working with SQL Server, you may often need to count the number of unique values in a specific column. This is useful for analyzing data, detecting duplicates, and understanding dataset distributions.

Using COUNT(DISTINCT column_name)

To count the number of unique values in a column, SQL Server provides the COUNT(DISTINCT column_name) function. Here’s a simple example:

SELECT COUNT(DISTINCT column_name) AS distinct_count
FROM table_name;

This query will return the number of unique values in column_name.

Counting Distinct Values Across Multiple Columns

If you need to count distinct combinations of multiple columns, you can use a subquery:

SELECT COUNT(*) AS distinct_count
FROM (SELECT DISTINCT column1, column2 FROM table_name) AS subquery;

This approach ensures that only unique pairs of column1 and column2 are counted.

Why Use COUNT DISTINCT?

  • Helps in identifying unique entries in a dataset.
  • Useful for reporting and analytics.
  • Efficient way to check for duplicates.

By leveraging COUNT(DISTINCT column_name), you can efficiently analyze your database and extract meaningful insights. Happy querying!

0
98