Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2005
    Posts
    4

    Unanswered: SELECT columns by column-index NOT by columnname!

    Hello all,

    how can I select one or more columns from a table by column-index and NOT by columnname?

    e.g.:

    Code:
    SELECT tbl1.[1], tbl1.[2], tbl1.[3] FROM Orders AS tbl1
    and NOT like this:

    Code:
    SELECT tbl1.OrderNo, tbl1.ProductNo, tbl1.Price FROM Orders AS tbl1
    Is that possible in MS-SQL 2000?

    Thanks a lot in advance

    kind regards

    Otto

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which column index? The columns in a database don't have any implicit order. You can order them by data type, by name, index coverage, etc. There's no guarantee that the order will be the same unless you specify it somehow, even though the columns are usually returned in the order that they were added to the table.

    The short answer is that columns and rows have no order within a table. Some database engines impose an order for you, but relying on any particular order that you didn't specify is a recipe for disaster in the long term.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    Which column index? The columns in a database don't have any implicit order. You can order them by data type, by name, index coverage, etc. There's no guarantee that the order will be the same unless you specify it somehow, even though the columns are usually returned in the order that they were added to the table.

    The short answer is that columns and rows have no order within a table. Some database engines impose an order for you, but relying on any particular order that you didn't specify is a recipe for disaster in the long term.

    -PatP
    What??? Records have no order, true, but columns definitely do have an order. Its a value in the syscolumns table called "colid". And how you can reorder columns without enumeration or dynamic sql, well, that's a new one on me.
    otto, to accomplish this you are going to need your procedure to create a custom sql string based upon the colid values in syscolumns and then execute it as dynamic sql.
    Why do you want to do this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    If Pat is right what is the meanng of ordinal position in INFORMATION_SCHEMA.COLUMNS?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think this is one of the (extremely rare) circumstances where Pat is demonstrably wrong about something. If so, I shall mark it in my calendar, request that this thread be made Sticky, and go to weather.com to check the temperature in Hades.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you follow that line of reasoning (that side effects of the storage medium are acceptable logical constraints), then both rows and columns do have order. Columns are ordered by the physical order in which they are added to the table, and rows are ordered by either the clustered index or the hash slot.

    In a logical database, both rows and columns can have any order in a database that the engine chooses to put on them if the code doesn't specify an order. A physical database orders the rows and columns according to whatever rules its implementors find convenient, and that order can change at their whim.

    Different tools will also use different ways to manage and retrieve data. This means that the third column in one tool could be the first column using a different tool. Some of the more interesting tools that are presentation based instead of set based will even make changes in the result set based on the data in that set, which implies that the same query can produce different orders as the data in the underlying tables changes.

    Relying on the physical order often works, for a while... But it is a receipe for disaster in the long term.

    -PatP

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    According to Kalen's book (p259), if you have varchar columns, then any fixed-length fields are stored physically before the variable length columns. I would guess the colid order is there to help folks who have 200 field tables, and depend on Enterprise Manager. Besides, it makes a decent artificial key, doesn't it?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    C'mon Pat. You're digging now.

    Select * from YourTable

    SQL Server makes no guarantee that the rows will come back in the same order every time, especially if there are joins or indexes involved. But it does guarantee that the columns will be returned in the same order every time. Select * may be bad practice, but it is consistent until the schema changes.

    So there is a fundamental difference between the stored order of rows and the stored order of columns. And colid is not a side-effect, it is a strongly defined logical order.

    Somewhere below us, the Devil is putting on thermal underwear...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    But it does guarantee that the columns will be returned in the same order every time.
    Show me where that error is. I'll get the documentation corrected.

    -PatP

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    LOL. I believe you would!

    Show me where it doesn't, and correct my error!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    From Books Online:
    Code:
    SELECT Clause
    .
    .
    .
    Arguments
    .
    .
    .
    
    * 
    Specifies that all columns from all tables and views in the FROM clause
    should be returned. The columns are returned by table or view, as specified
    in the FROM clause, and in the order in which they exist in the table or view.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You are correct. Even though it is logically just as wrong to think of columns as having an implicit order as it is to think of rows that way, BOL does in fact document it. I'll start the process to get that fixed, but that fix will probably take a while.

    -PatP

Posting Permissions

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