[dba-SQLServer] Order of data in views

jwcolby jwcolby at colbyconsulting.com
Wed May 14 05:12:43 CDT 2008


Martin,

In the end it really doesn't matter, reality is, and reality says that 
SQL treats a view as a "table".

I guess we get into semantics here.  What is a query (and to be frank, I 
certainly don't know, behind the scenes)?  Is it not a "virtual table"? 
  A set of data pulled out of the database.  WHATEVER ELSE IT IS, it is 
a SQL statement  with a SELECT clause.  Everything else that can be used 
with a SELECT clause works correct?  Why not the Order By clause?  It is 
just a steenkin SQL Statement, the ORDER BY clause should function too.

The answer to that question is probably lost in the mists of time and 
the members of some standards body who defined SQL syntax and operation 
decades ago.  Some Purist likely had some reason not to want a view 
sorted and had a LOT of pull on the committee and so here we are, 
decades later, saying "of course a view is ALWAYS unsorted".

LOGICALLY a view is a SELECT SQL statement, and the last time I looked 
SELECT SQL statements have an ORDER BY clause defined in the SQL 
syntax... but NOT if saved as a view.

Silly if you ask me.  But I don't get a vote.

John W. Colby
www.ColbyConsulting.com


Martin W Reid wrote:
> John
> 
> I always treated a view as a virtual table created at the point the statement is executed. The use orderby on the statement hitting the view.
> 
> Martin
> 
> 
> Martin WP Reid
> Information Services
> Queen's University
> Riddel Hall
> 185 Stranmillis Road
> Belfast
> BT9 5EE
> Tel : 02890974465
> Email : mwp.reid at qub.ac.uk



More information about the dba-SQLServer mailing list