1

If I create a view and select my fields in the order I want to "receive" them in can I be fully assured that I can call "Select * from myView" from my apps instead of specifying ALL of the fieldnames yet again in my select query?

I ask this because I pass whole datarows to my DataModels and construct the objects by assigning properties to the different indexes in the itemarray attached to this datarow. If these fields get out of order there's no telling what could happen to my object.

I know that I can't rely on an order-by that lives inside of a view (been burned before on this one). But the order of the fields I was not sure about.

Sorry if this is sql noob level. We all start somewhere with it. Right now all the extraneous field names in my app code is making readability somewhat difficult so if I can safely go back and replace a lot of syntax with a * then that would be great.

These tables are small so i'm not worried about implications of using a * over individual fields. I'm just looking to not code unnecessary syntax.

TWood
  • 2,563
  • 8
  • 36
  • 58
  • 2
    I usually manually specify the column names unless I am just running a quick one-off query at an SQL prompt. It lets me know (and the SQL engine know) what I expect -- that is, it increases the explicitness of the "contract" and allows more meaningful errors to get generated sooner. The additional information can also be used to trim the record size and thus save on transmission costs, etc. If "readability" is an issue, perhaps you have not settled into a comfortable readable way to write SQL yet or are otherwise hampered by how your environment wants you to express the queries. –  Nov 09 '10 at 21:09
  • I guess maybe i've not settled into a comfortable way to write my sql queries then. It just gets hard to read on a non-widescreen monitor when your query calls 40-something fields and spans more than 2 lines. In my dept. we keep "lean" views, or at least try to. Only what's necessary is included in the view. If you need more in the view chances are there's another one out there that gives a broader blob of data (ie: daily stats vs hourly stats). This usually keeps row #'s acceptable but sometimes there's a ridiculous # of fields. – TWood Nov 10 '10 at 15:31

4 Answers4

4

Column order is guaranteed, row order (as you noted) is not.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • So ... **NO** for row order :-) The only way (in general) to *guaranteed* an row ordering is to use an ORDER BY clause at the *top level* of a query. (ORDER BY is not honored in a view, and some versions will reject it IIRC.) –  Nov 09 '10 at 21:07
  • @pst: SQL Server, for one, will reject an `ORDER BY` in a view unless you also include a `TOP`. – Joe Stefanelli Nov 09 '10 at 21:12
  • Worth noting this is subject to table changes and sp_refreshview etc? – gbn Nov 09 '10 at 21:20
  • @gbn: See my comment on your answer. – Joe Stefanelli Nov 09 '10 at 21:23
2

Column order may not be guaranteed or reliable if both of these are true

  • the view definition has SELECT * or SELECT tableA.* internally
  • any changes are made to the table(s) concerned

You'd need to run sp_refreshview: see this question/answer for potential issues.

Of course, if you have simple SELECT * FROM table in a view, why not just use the table and save some maintenance pain?

Finally, and I have to say it, it isn't recommeded to use SELECT *... :-)

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • My interpretation was not that the view was defined as `select * from table` but rather that the OP wanted to use `select * from view` in his code. – Joe Stefanelli Nov 09 '10 at 21:22
  • @Joe Stefanelli: of course, but it's one of my [pet dislikes](http://programmers.stackexchange.com/questions/14856/best-practices-that-you-disagree-with/14880#14880) when folk do such things... Ah. Get it. Pimped my answer to nmake it clearer I hope. – gbn Nov 09 '10 at 21:24
  • the view's are actually quite complex (not written by me) and use a few calculated fields and functions, which I can't ever seem to get the fieldnames correct for. We're talking about <100 rows x <17 fields or I wouldn't use the * selector. Thank you all for your great comments and explanations though! – TWood Nov 10 '10 at 15:22
2

Yes, left-to-right ordering of columns is guaranteed in SQL. In fact, it's one of the top three flaws used to prove that SQL is not truly relational (e.g. see The Importance of Column Names by Hugh Darwen), duplicate rows and the NULL value being the other two.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • +1 for SQL not being truly relational, but see gbn's answer for the perils of `select *`. –  Nov 10 '10 at 12:09
1

Yes, I've always relied on select * returning fields in the order specified in the view or table.

For example Microsoft SQL - "* 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."

RedGrittyBrick
  • 3,827
  • 1
  • 30
  • 51
  • However, as gbn notes, if the structure of the table changes then the fields returned by `select *` will be different. –  Nov 10 '10 at 12:07
  • Yes, the use of asterisk in SQL selects is simultaneously a great boon and a great curse. But the question author explicitly stated he wasn't concerned about that. – RedGrittyBrick Nov 10 '10 at 14:51
  • Not exactly - the OP said "These tables are small so i'm not worried about implications of using a * over individual fields", implying that the additional processing/bandwidth cost wasn't an issue. –  Nov 10 '10 at 15:32