[dba-SQLServer] Order of data in views

jwcolby jwcolby at colbyconsulting.com
Wed May 14 10:00:11 CDT 2008


Francisco,

I am not sure exactly what you are trying to discover.

When I discussed the timeout issue long ago (here in this forum IIRC) I 
was told to simply to a:

SELECT * FROM SomeView

to get around the timeout.

That is what I have been doing ever since.  At the query level there is 
no join going on, it is exactly as described above.  In fact these are 
GroupBy Somefield count PKID views back at the top view level.  The 
orderby is on the SomeField (groupby field) down in that top level view.

There are about 18 or so of these count queries which I run, then cut 
and paste the result set into pages of a spreadsheet.  In the vast 
majority of cases the GroupBy is a number code 1 through 7 which 
represents an age (apparently).  In other cases it is a state, zip or 
income code etc.  I need to OrderBy that code, which I do in the top 
level view.  If I were able to run the view (I can't because of the 
timeout issue) it would be sorted because SQL Server apparently allows 
an order by clause in a view even though it is not "approved of" by the 
SQL world.

When I work with a view directly in SSMS, I can sort any view as I wish. 
  It is just a SELECT SQL statement to SSMS, and SELECT syntax allows an 
Order By.  In those cases where my views do not take longer than 30 
seconds to execute, the views sort as directed, each and every time (so 
far, which is in the thousands of times)

As for how I perform the end result, I build the SELECT * FROM SOMEVIEW 
in a query in that db, then simply replace SomeView with SomeOtherView 
with Some3rdView.  I get result sets, cut to the paste buffer, paste 
into a sheet in a spreadsheet (template I built) and when I have run / 
cut / pasted all 18 or so views, I send the spreadsheet off to the customer.


John W. Colby
www.ColbyConsulting.com


Francisco Tapia wrote:
> John,
>    The basics haven't changed in that views are not sorted until you
> yourself sort it, it could be that the results are in line with the
> order of your indexes
> 
> Further, when you base a query off a view are you only changing the
> where clause or are you joining against another table/ view?
> 
> 
> 




More information about the dba-SQLServer mailing list