can I rely on the order of fields in a sql view?

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 * fr开发者_如何学Goom 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.

---

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

---

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 *... :-)

---

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.

---

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."