Home Dashboard Directory Help
Search

CTE Column Name Definition by The SQL Apostle


Status: 

Closed
 as By Design Help for as By Design


1
3
Sign in
to vote
Type: Suggestion
ID: 785780
Opened: 4/29/2013 4:50:24 AM
Access Restriction: Public
0
Workaround(s)
view

Description

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
(
    SELECT
        object_id Id,
        object_name(object_id) AS Name
    FROM
        sys.objects
    WHERE Type = 'u'
)
SELECT * FROM mysystables
-------------------------------------------------------------------------------
Details
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
Hello,
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
Sign in to post a workaround.