When joining multiple tables together in a query, use aliases every time on every field.
Say we’ve got two tables, Customers and Salespersons. The Customers table has a PreferredSalespersonID field that identifies who their normal sales rep is, and that lets us quickly grab the right salesperson when a customer calls in. We want to display basic information about the customer and their salesperson:
|
1 2 3 4 |
SELECT CompanyName, Address, City, StateID, ZipCode, SalespersonName FROM dbo.Customers INNER JOIN dbo.Salespersons ON Customers.PreferredSalespersonID = Salespersons.ID WHERE Customers.ID = 12345 |
That works great at first, so we put it in production and off we go. Months later, someone decides to add an Address field to the Salespersons table. Boom goes the dynamite – suddenly this query starts failing because SQL Server isn’t sure which City field we mean. We get errors like this:
|
1 2 |
Msg 209, Level 16, State 1, Line 3 Ambiguous column name 'Address'. |
To prevent this problem, always alias every single field. My personal preference is to use an alias as short as possible, but long enough to explain what the alias points to. Never alias tables with single letters starting with A, B, C, etc – it’s painful to read those queries. Instead, consider aliasing the query like this:
|
1 2 3 4 |
SELECT cust.CompanyName, cust.Address, cust.City, cust.StateID, cust.ZipCode, sls.SalespersonName FROM dbo.Customers cust INNER JOIN dbo.Salespersons sls ON cust.PreferredSalespersonID = sls.ID WHERE cust.ID = 12345 |
If I used one-letter aliases like “c” and “s”, it would still work, but I try to design every query as if I’m going to have to come back to it tomorrow and add three more tables. If I come back and join more tables in, then the “c” and “s” might be confusing if those new tables also start with C or S.
Finally, when picking aliases, check other stored procedures and views to see if there’s already an aliasing standard in the database. If the Customers table is already being aliased with “cmr” in other T-SQL code, then reuse that same alias even if it doesn’t make perfect sense. The more consistent the code, the easier it is to jump from one T-SQL script to another with ease.
11 Comments. Leave new
This is one of my biggest pet peeves! Someone hands over a view or query to tune and/or debug that isn’t aliased or has fully qualified database and table name references throughout the entire query. Trouble is several of the BI tools that build queries either do not allow for you to alias tables when you include them in the schema or the schema developer fails to define them and the BI tool is forced to fully qualify the entire SQL statement to avoid the query failing because two tables share a column name.
We actually have a commonly accepted dictionary of abbreviations that we use. I needs documented, however every query uses the exact same aliases for each table. It makes it really easy to look back at queries I wrote 6 months ago – si is always the Sites table.
I just laugh when my mistakes lead to enlightening blog posts for you. 🙂
Keep up the good work.
Heh – well, I get tired of writing about my own mistakes, hahaha. It’s sustained me for years, but every now and then I have to break from that!
@peschkaj: I like the idea of a standard dictionary for table abbreviations!
I also prefer to quote all identifiers as well. Somewhat of a nervous tick I have (among others 🙂
Brent, is the dbo. really necessary? Is there some reason why that should be included if every table in the database is dbo?
David,
See: http://msdn.microsoft.com/en-us/library/ms189915.aspx
Microsoft recommends you at least owner-qualifying your object names.
Executing User-defined Stored Procedures
When executing a user-defined stored procedure (either in a batch or inside a module such as a user-defined stored procedure or function), we strongly recommend qualifying the stored procedure name with at least the schema name.
The following example demonstrates the recommended method for executing a user-defined stored procedure.
USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;
-Or-
EXEC AdventureWorks.dbo.uspGetEmployeeManagers 50;
GO
If a nonqualified user-defined stored procedure is specified, the Database Engine searches for the procedure in the following order:
* The sys schema of the current database.
* The caller’s default schema if executed in a batch or in dynamic SQL. Or, if the nonqualified procedure name appears inside the body of another procedure definition, the schema containing this other procedure is searched next. For more information about default schemas, see User-Schema Separation.
* The dbo schema in the current database.
Important:
If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed if you use a nonschema-qualified name reference. For more information, see Creating Stored Procedures (Database Engine).
Absolutely, great question. Every table in the database NOW is dbo, but that’s not necessarily true for the future. I’ve seen a lot of projects where a new round of developers or a new project will need to add an additional schema for security needs, and then it’s an absolute mess to go back and fix the code everywhere.
Funny, I hate multiple letter alias or full table name alias. That is painful for me. Keeping it to a single letter is my preference. The important thing is to use an alias; the rest is personal preference. The ‘dbo’ prefix may be recommended but is pretty stupid on the face of it, bloats code and obscures meaning. Having an Oracle background I can’t bring myself to use it in this shop.