Aliases

Aliases seems to be something people don’t think much about. They learn that you can call a table or a column something else for reference elsewhere in your query and they go about using it. Sure, sometimes they can’t understand why people would use aliases, to each their own. I like aliases, I think it makes the query easier to read, easier to write and easier to understand. Lets go to the AdventureWorksR2 database for an example.

If you’re referencing the table EmployeeDepartmentHistory, do you really want to type that out every time you reference that table? Worse yet, if you’re using schemas and want to be specific, now you’re typing out HumanResource.EmployeeDepartmentHistory whenever you want to explicitly state a column. If you’re writing the query against a database on another server, you’ll end up with ServerName.AdventureWorks2008R2.HumanResource.EmployeeDepartmentHistory. If you’re linking that to the local table of the same name to check that the data matches, you’ll need to type that out in full for every column you reference

Or you could type RmtEDH for the remote server table and EDH for the local table.

To use a slightly different example that’s all on the same server and in the same database, lets use AdventureWorks2008R2 again.
We can either have the table names typed out

SELECT HumanResources.Employee.BusinessEntityID, HumanResources.Employee.CurrentFlag, HumanResources.Employee.BirthDate,
HumanResources.Employee.Gender, HumanResources.Employee.HireDate, HumanResources.Employee.JobTitle,
HumanResources.Employee.LoginID, HumanResources.Employee.MaritalStatus, HumanResources.Employee.ModifiedDate,
HumanResources.Employee.NationalIDNumber, HumanResources.Employee.OrganizationLevel,
HumanResources.Employee.OrganizationNode, HumanResources.Employee.rowguid, HumanResources.Employee.SalariedFlag,
HumanResources.Employee.SickLeaveHours, HumanResources.Employee.VacationHours,
Person.Person.FirstName, Person.Person.MiddleName, Person.Person.LastName, Person.Person.rowguid,
Person.Person.Title, Person.Person.Suffix, Person.Person.PersonType
FROM HumanResources.Employee
INNER JOIN Person.Person
ON HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID

Or we can use an alias

SELECT E.BusinessEntityID, E.CurrentFlag, E.BirthDate, E.Gender, E.HireDate, E.JobTitle,
E.LoginID, E.MaritalStatus, E.ModifiedDate, E.NationalIDNumber, E.OrganizationLevel,
E.OrganizationNode, E.rowguid, E.SalariedFlag, E.SickLeaveHours, E.VacationHours,
P.FirstName, P.MiddleName, P.LastName, P.rowguid, P.Title, P.Suffix, P.PersonType
FROM HumanResources.Employee E
INNER JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID

Which scans faster? Which is really easier to read? Sure, you could only specify the columns that are in both tables (BusinessEntityID and rowguide), but then anyone else reading this query won’t know which column belongs with which table unless they go look. That’s not the result you want either, you want the quesy to be easily readable by anyone that comes along and looks at your code. Now imagine what the query would look like if the tables were on another server.

That’s the basics of table aliasing, but I’ve come across a strange execution of this in queries I’ve inherited. If we have a query with 4 joined tables, like

SELECT E.BusinessEntityID, E.BirthDate, E.Gender, E.JobTitle, E.MaritalStatus,
P.FirstName, P.MiddleName, P.LastName, P.Title, P.Suffix, A.AddressLine1, A.City
FROM HumanResources.Employee E
INNER JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress BA
ON P.BusinessEntityID = BA.BusinessEntityID
INNER JOIN Person.[Address] A
ON A.AddressID = BA.AddressID

What I find in the code instead is

SELECT A.BusinessEntityID, A.BirthDate, A.Gender, A.JobTitle, A.MaritalStatus,
B.FirstName, B.MiddleName, B.LastName, B.Title, B.Suffix, D.AddressLine1, D.City
FROM HumanResources.Employee A
INNER JOIN Person.Person B
ON A.BusinessEntityID = B.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress C
ON B.BusinessEntityID = C.BusinessEntityID
INNER JOIN Person.[Address] D
ON D.AddressID = C.AddressID

There’s no logical connection between the alias given and the table it references. If the tables are listed in a different order later in another query in the same procedure, they’d have different aliases, making it much more confusing for anyone trying to understand the whole list of queries. I’ve been told it is common practice to alias tables this way in Oracle, but I have no idea if that is true or not. You can make an alias just about anything you want, so make it something that’ll make it easy to understand; it should have some connection to the original table name.

I like one or two letter abbreviations, so I tend to go with unique letters at the beginning of the table name. Of course, that can run into the same problem as the method I mentioned above where the table Person is P in one query and the table Preferences is P in another. It certainly isn’t a bad idea to have a list of standard aliases for all the tables in your database. It is a little more work, but in the long run it can make your job easier. Per and Pref are probably good aliases in that they’re not too long and they’re distinct. If the table is coming from another server or database or schema, just preface the standard abbreviation with an abbreviation of the server, database or schema.

Intellisense will recognize whatever alias you create, so don’t worry about losing that functionality.

There’s another time when table aliases aren’t just a good idea, they’re required. If you’ve created a subquery and joined it like it is a table, you have to have an alias at the end. For example

SELECT E.BusinessEntityID, E.BirthDate, E.Gender, E.JobTitle, E.MaritalStatus,
P.FirstName, P.MiddleName, P.LastName, P.Title, P.Suffix, A.AddressLine1, A.City
FROM HumanResources.Employee E
INNER JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress BA
ON P.BusinessEntityID = BA.BusinessEntityID
INNER JOIN (SELECT AddressID, AddressLine1, City
FROM Person.[Address]
WHERE StateProvinceID = 47) A
ON A.AddressID = BA.AddressID

This is just like the query above, but we’re using a subquery to limit our search to New Jersey. Without that alias A at the end, we’d be unable to join it to the rest of the query. If you’re going to use aliases in your subquery, try not to reuse the same alias at different levels of your query for readability. SQL Server won’t have a problem with it, but your eyes might.

SELECT E.BusinessEntityID, E.BirthDate, E.Gender, E.JobTitle, E.MaritalStatus,
P.FirstName, P.MiddleName, P.LastName, P.Title, P.Suffix, NJ.AddressLine1, NJ.City
FROM HumanResources.Employee E
INNER JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress BA
ON P.BusinessEntityID = BA.BusinessEntityID
INNER JOIN (SELECT A.AddressID, A.AddressLine1, A.City
FROM Person.[Address] A
WHERE A.StateProvinceID = 47) NJ
ON NJ.AddressID = BA.AddressID

In addition to aliasing tables, you can alias columns. If you have a long column name, you can certainly shorten it, for example in the Person.Address table referenced above, there’s a column called StateProvinceID. To shorten it you could state SELECT StateProvinceID as SPID. I don’t normally use column aliases just to shorten names, there’s much more important times to use it. If you’re doing calculations, you’ll frequently want to alias the calculatd column, for example

SELECT P.FirstName, P.LastName, P.BusinessEntityID, (SP.SalesYTD * SP.CommissionPct) as Commission
FROM Sales.SalesPerson SP
INNER JOIN Person.Person P
ON SP.BusinessEntityID = P.BusinessEntityID

Again, SQL Server won’t need that alias unless you use it as a subquery, but it does make it easier to read both the query and the result window.

The other main reason you’ll want to use aliases is if you have subqueries that pull back the same column, you’ll want to name them different things for readability. You could just use the aliases to tell them apart, but if they’re bubbling up through more than one subquery, that won’t work.

SELECT CurrYear.BusinessEntityID, CurrYear.FirstName, CurrYear.LastName, CurrYear.Commission as CurrComm, LastYear.Commission as LYComm
FROM
(SELECT P.FirstName, P.LastName, P.BusinessEntityID, (SP.SalesYTD * SP.CommissionPct) as Commission
FROM Sales.SalesPerson SP
INNER JOIN Person.Person P
ON SP.BusinessEntityID = P.BusinessEntityID) CurrYear
LEFT JOIN
(SELECT P.FirstName, P.LastName, P.BusinessEntityID, (SP.SalesLastYear * SP.CommissionPct) as Commission
FROM Sales.SalesPerson SP
INNER JOIN Person.Person P
ON SP.BusinessEntityID = P.BusinessEntityID) LastYear
ON CurrYear.BusinessEntityID = LastYear.BusinessEntityID

If that gets used as a subquery, you’ll need those aliases CurrComm and LYComm because you can’t reference anything more than 1 level below.

Think about aliases and use them, you’ll save yourself or someone else a lot of work later. Heck you’ll probably save yourself some time the first time through too.

This entry was posted in 2008, SQL Server and tagged , , , . Bookmark the permalink.

2 Responses to Aliases

  1. jonmcrawford says:

    I don’t know, I use aliases because the other members of my team/organization can’t get along without them, and we’ve at least standardized the ones we use, but I still don’t see the need. Especially with tools like the SSMS Toolspack, or SQL Prompt*. I don’t think it’s really easier to read something with essentially a variable as the tablename, I am used to reading words in everyday life just fine.

    Especially with the auto-formatting options. I can see aliasing to remove the server name maybe, and just leave it with the tablename, I’ll do that sometimes, but I don’t see an advantage to shortening the table name itself.

    Without the shortcut tools though, then I get it. My hands hurt enough from typing ;)

    * – for anyone using SQL Prompt, you can modify the xml files directly to copy in the same aliases to your whole team, so the system will prompt with standard names, without having to manually enter them all.

  2. Stefan says:

    In what way can’t the other members of your team get along without them?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>