Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Refer to column by position rather than name?

    Is there a way, in the SELECT clause, to refer to a column directly by position rather than name?

    I am sure I could first get a column name, using its positon, via the syscolumns table, but I'm hoping there might be something more direct available that could be entered right into the column list of a SELECT statement.

    I'm envisioning something like:

    Code:
    select  field(5)
    from    dbo.tblTable
    where the statement would return the contents of the fifth column.

    Something out there like this?

    Oh . . . and without resorting to dynamic SQL.
    Last edited by PracticalProgram; 11-04-11 at 16:14.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Well, it can be done, but you would need to...
    Quote Originally Posted by PracticalProgram
    Oh . . . and without resorting to dynamic SQL.
    OK. Nevermind. ;-)

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Sorry, went back and edited that to add that dynamic SQL comment.

    I very much enjoy writing dynamic SQL, but you end up with an enormous amount of code compared to, what I am envisioning, as a single function call.

    Thanks for the response though.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Out of the box, I do not think SQL Server allows you to call a field by position. i checked in BOL for the select clause, and learned you can call identity and rowguid columns by the tokens $identity and $rowguid, but there looks to be nothing about calling fields by position. It probably starts breaking down pretty quickly when you join 5 or 10 tables together. SQL Server needs a way to tell what indexes can be used as a covering index, or what indexes include the columns in the select clause. That's probably why you need to qualify the columns.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Prior to SQL 2008 R2, columns always had a physical order in tables. I'm pretty sure that was always an artifact, I don't believe that the sequence of columns was ever guaranteed. With some of the new features in SQL 2008 R2 and especially the ones comming in SQL 2012, the order assumption will definitely break down.

    I would not head any further down this path than you've already gone, and would beat a hasty retreat as soon as you can.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Got it.

    Already wrote it in dynamic.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan View Post
    Prior to SQL 2008 R2, columns always had a physical order in tables. I'm pretty sure that was always an artifact, I don't believe that the sequence of columns was ever guaranteed. With some of the new features in SQL 2008 R2 and especially the ones comming in SQL 2012, the order assumption will definitely break down.
    Would you care to elaborate which of those upcoming features you mean?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are multiple issues, but the one that jumped right to my mind was Joe Sack's Blog | Row and batch execution modes and columnstore indexes

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •