Avoid Transact-SQL’s Column Alias Limitations

Avoid Transact-SQL’s Column Alias Limitations

ou might expect all SQL Server’s clauses to support a column alias. As reasonable as that assumption seems, SQL Server can’t honor a column alias in that way. If you refer to an aliased column in another clause, SQL Server returns an error?surprise! Figure 1 shows the result of running such a query; SQL Server can’t find the aliased column TotalCost. That’s not a bug; SQL Server requires the alias?it has to call the column something!

SQL Server returns the error in Figure 1 because its engine works logically through the calls as follows:

  1. SQL Server evaluates the JOIN (and any UNION operator) in the FROM clause. Imagine SQL Server combining the referenced tables into one table. SQL Server’s not retrieving data, just combining data sources.
  2. SQL Server excludes whole records by applying a WHERE clause.
  3. SQL Server applies the GROUP BY clause, which combines records when necessary, further limiting the results.
  4. SQL Server applies a HAVING clause, in another attempt to limit the records the query retrieves.
  5. At this point, SQL Server has identified the records to retrieve, and now uses the query’s SELECT clause to limit the columns.

Figure 1. SQL Server Engine Can’t Find TotalCost: This error occurs because SQL Server can’t find the aliased column.
?
Figure 2. The WHERE Clause Supports a Column Alias: ORDER BY is the only place SQL Server supports a column alias.

The same thing will happen with a GROUP BY or HAVING clause that refers to a column alias. SQL Server can’t apply an alias that it hasn’t defined yet. Outside of SELECT‘s column list, ORDER BY is the only place SQL Server supports a column alias (see Figure 2).

A Derived Table Solution

The solution to the column alias problem is a derived table. In this context, a derived table is similar to a derived column?it’s held in memory. Technically, a derived table is the result of using a SELECT statement in another SELECT statement’s FROM clause. You might know a derived table as a subquery. SQL Server successfully handles the column alias shown in Figure 3 by virtue of the derived table.

Figure 3. Use a Derived Table to Use a Column Alias: SQL Server successfully handles the column alias by virtue of the derived table.

Yes, you have to rethink your strategy a bit to get the results you want, but it’s a small price to pay for the convenience!

The derived table dtTotalCost retrieves all the columns the query needs, including the aliased (or derived) column. The main SELECT then handles the logistics of manipulating the data?any filtering, grouping, sorting, and so on. In this way, the column alias exists in memory (in the derived table) when the main SELECT‘s WHERE clause gets around to referring to it.

The rule is simple, even if it doesn’t appear so by glancing at the SQL:

  1. Retrieve all the data into a derived table.
  2. Let the main SELECT process the data (filter, group, sort, and so on).

Derived Solution

SQL Server’s engine processes a column alias at the end of a query. That means a column alias for a derived column isn’t available to other clauses. Instead of re-entering a complex expression, alias the column in a derived table. That way, the alias exists when the main SELECT gets around to referencing it.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as