MS Azure – TSQL a simple example using a Common Table Expression (CTE)

A common table expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and exists only for the duration of the query.

I’m finding them useful as you can use them within a view to contain all the references required for that particular view. This I feel makes things more maintainable.

An example of a simple common table expression (CTE) in T-SQL:

WITH CTE_EmployeeSalaries AS
(
    SELECT EmployeeID, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT *
FROM CTE_EmployeeSalaries;

This CTE defines a virtual table named CTE_EmployeeSalaries that contains all employees with a salary greater than 50,000. The CTE is then used to select all rows from this virtual table.

Common table expressions (CTEs) have several advantages:

1.Improved readability: can make complex queries easier to read and understand by breaking them up into smaller, more manageable pieces.
2.Improved maintainability: Because CTEs are self-contained, they can be easily modified or replaced without affecting the rest of the query.
3.Improved performance: In some cases can improve query performance by reducing the need for subqueries.
4.Recursive queries: CTEs can be used to perform recursive queries, which are useful for querying hierarchical data such as an organizational chart or a bill of materials.
5.Temporary results: can be used to store intermediate results that are used multiple times within a single query. This can be more efficient than repeatedly calculating the same intermediate results.
6.Better alternative to view: CTEs can be used as an alternative to views, which can be difficult to modify and are not always optimized for performance.
7.Reusable: CTEs can be defined once and used multiple times within a single query or in multiple queries within the same session.

MS Azure TSQL writing comments

In TSQL, comments can be added to code in two ways:

1.Single-line comments: These start with two hyphens (–) and continue until the end of the line. For example:

-- This is a single-line comment

2.Multi-line comments: These start with a forward slash and an asterisk (/) and continue until the closing asterisk and forward slash (/). For example:

/*
This is a multi-line comment. It can span
multiple lines and is often used to provide
detailed explanations or to comment out large
blocks of code.
*/

When writing comments, it’s important to keep them clear and concise, and to use them to explain why something is being done rather than how it is being done. This helps to make your code more readable and easier to understand for other developers who may be reading it.