CTE Column Name Definition - by The SQL Apostle

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 785780 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 4/29/2013 4:50:24 AM
Access Restriction Public


If you have specified column names in the CTE definition, the Columns are returned in the exact order they are in the definition, even if the matching names are availible in the Query. 
Example :
;WITH mysystables (Name, Id) AS
		object_id Id,
		object_name(object_id) AS Name
	WHERE Type = 'u'
SELECT * FROM mysystables
Sign in to post a comment.
Posted by The SQL Apostle on 5/8/2013 at 1:19 PM
Adhering to ANSI SQL Specification would be great, but it would help the developers if this is documented as such in the Books online that the column alias after the object name would be the one that would be used and in the same order irrespective of the column names specified in the query.

As I mentioned, This is an easily made mistake and would prevent developers spending hours of time debugging complex queries for an issue caused by this. Documentation giving example of this kind of problem in the Books online would help a lot many developers and improve the documentation.

The ANSI SQL Specification for this is not easy to find.
Posted by Microsoft on 5/6/2013 at 12:41 PM
The behavior you are seeing is by design & follows the ANSI SQL specification. This is how column aliases are resolved in the CREATE VIEW definition too. If you specify the column aliases after the object name then that is how the columns are renamed from the query itself.

Umachandar, SQL Programmability Team