T-SQL 101: 15 Using column and table aliases in SQL Server queries

T-SQL 101: 15 Using column and table aliases in SQL Server queries

Look carefully at the following queries:

I’ve shown several examples here of what are called aliases. There are two basic types of aliases.

Column Alias

In the first query, I’ve used a column alias. I wanted to query the Description column in the dbo.Products table but I wanted the column called ProductName instead. So I follow the column with the word AS and then the name I want to use for the alias.

The word AS is actually optional but I really like using it to make it clear what’s going on. I’ve seen many instances where someone just leaves a comma out in a list of columns, and one column name becomes an alias for the previous column name.

The second query is an example of where you really do need to use aliases. I have selected an expression 2 + 3 and without the alias, there would be no name for the column that was returned. If you’re just reading it on the screen, that might not matter, but generally, I’m writing queries that are sent by programs and I need a column name on the data that comes back. It’s also useful even if all you’re doing is copying the results into a program like Excel.

Now there are two ways that these aliases can be written. Instead of writing:

2 + 3 AS CalculatedValue

I could have written:

CalculatedValue = 2  + 3

I don’t love using aliases that way but I have some pretty clever friends who wouldn’t write queries any other way. They write the column list with column names one under the other, and they like the way that this form would put all the aliases directly under each other.

It’s also possible for you to write this:

‘CalculatedValue’ = 2 + 3

But don’t do that. Same for using double quotes around the name. If you have to quote the name for some reason such as it’s a reserved word, or it contains spaces, write it this way:

2 + 3 AS [Calculated Value]

or

[Calculated Value] = 2 + 3

Table Alias

In the third query, I’ve shown an example of a table alias. Tables can be aliased just like columns can. I tend to do this now, pretty much all the time.

The basic argument for doing this is that if you have more than one table in the query, it can be pretty confusing to work out which column in a SELECT list is from which table. You could always put the table name in front of each column but that’s long and painful. Instead, just alias the table, and use the table’s alias in front of the column names.

OK, so multiple tables makes sense, but why would I do that for a query with just one table? Well, the first bonus is that Intellisense works great when you do that. If I type p then hit a period, I get a list of columns for just that table. And chances are that sometime soon, someone will change my query to add another table anyway. So I’d rather just have aliases right from the start.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-04-29