Ambiguous column name error when only using one Table

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.

One thought on “Ambiguous column name error when only using one Table”

Comments are closed.