CODEWITHSUNDEEP
sql
TWood
TWood

Reputation: 2583

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

Upvotes: 1

Views: 405

Answers (4)

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 2

gbn
gbn

Reputation: 432331

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

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

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

Upvotes: 4

RedGrittyBrick
RedGrittyBrick

Reputation: 4002

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

Upvotes: 1

Related Questions