Every once in a while I see something in SQL Server that has what I think are “Unexpected Results”. This is the case recently. I received the dreaded ambiguous column error when only using one table. My first thought was how can that be when I am only using one table. Just a bit of background, Ambiguous column errors are usually something we see when joining two tables and there is a column with the same name as both.
If you run the below code against the AdventureWorks2014 database it will run just fine.
SELECT Name
, ProductNumber
, Color
, ReorderPoint
FROM production.product
And when I run this, it also works just fine. No issues.
SELECT Name
, ProductNumber
, Color
, ReorderPoint
FROM production.product
ORDER BY name
However, during the development process I needed to get the list of columns to help me determine if there are any additional columns that I need to add. I know there are several ways to get the columns, but sometimes I find it easier to just at the “*” to return all columns. Just a note: this is in development on a development server. The “*” will not make it’s way into production code.
When I added the “*”, this is where I received unexpected results. All I did was add the “*”. Looking at the code below, you can see SQL Server is having issues with the Name column in the ORDER BY.
And sure enough, when I executed the code I did indeed get an error. Below is that error.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name ‘name’.
I found this to be a bit odd. When I run the code without the ORDER BY, it runs just fine. Although I do get two columns with the same name, Name. Well, I still needed to get the list of columns. So I had a choice, I could use one of the other methods to get the list of columns, Object Explorer or T-SQL using the system objects.
I decided to try something before going to those, something just a bit easier. I decided to try using a table alias. Similar code is below.
SELECT p.Name
, p.ProductNumber
, p.Color
, p.ReorderPoint
, p.*
FROM production.product p
ORDER BY p.name
Once the alias was added, all was good. The query successfully ran and I was able to get the information I was looking for. Of course, the “*” never made it into production code.
Just a small tidbit I ran across today.
Thanks for visiting my blog.