Odd SQL 2005/2008 Query Behaviour

This is just strange…
If a column appears twice in your query and you attempt to use it within an order by statement you get an error, unless you fully qualify the column. This doesn’t happen in 2000, but does in 2005 & 2008
This doesn’t work:

select name, * from sysobjects
order by name

Msg 209, Level 16, State 1, Line 3
Ambiguous column name ‘name’.


This does:

select name, * from sysobjects s
order by s.name

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s