Martin W Reid
mwp.reid at qub.ac.uk
Wed May 14 04:47:01 CDT 2008
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 ________________________________________ From: dba-sqlserver-bounces at databaseadvisors.com [dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby [jwcolby at colbyconsulting.com] Sent: 14 May 2008 10:20 To: Discussion concerning MS SQL Server Subject: Re: [dba-SQLServer] Order of data in views Stuart, > Enterprise Manager, select the Server, right click and select properties, check the "query timeout" field on the "Connection" tab ? Query timeout is set to 600 (seconds). These views are only taking ~2:40 minutes to run. I have been down this road before. Apparently that setting is for QUERIES, and apparently the SSMS treats views by a different set of rules than queries. > Use "SELECT TOP (99.9999999999999) PERCENT..." Interesting. Even more interesting, once I did that in one query, the rest of the queries sort "properly" even though the underlying view has no TOP() statement at all. Perhaps behind the scenes SSMS is leaving the last used TOP statement hanging around (sounds like another bug). So the takeaway is that it is a "feature" of SSMS that a sorted view works at all and don't depend on it. Just to straighten me out on this though... the theory is that a view is "just another table" and tables are by their nature unsorted? I do understand the "tables are by their nature unsorted" thing. Because a view is based on a SQL Statement, I always considered a view "a query" (a la Access), which appears to be untrue for whatever reason. John W. Colby www.ColbyConsulting.com Stuart McLachlan wrote: > On 13 May 2008 at 16:34, jwcolby wrote: > >> Well, we are talking about a GroupBy Cnt query here. The GroupBy field >> is what is ordered on, and is specifically ordered on that field in the >> underlying view. >> >> Unfortunately I cannot execute the view since the view takes about 40 >> seconds to complete and MS times out in 30, which no one here has >> managed to show me how to prevent. >> > > Enterprise Manager, select the Server, right click and select properties, check the "query > timeout" field on the "Connection" tab ? > > >> Therefore I am forced to create a query and paste the query into that in >> a SELECT * FROM SomeView SQL statement. Given that the view is ordered, >> you (I) would expect a query directly based on that to be ordered unles >> specifically reordered in some manner. Not so for whatever reason. >> > > Use "SELECT TOP (99.9999999999999) PERCENT..." > see > http://executioniseverything.blogspot.com/2007/01/order-by-in-views-sql-server-2005.html > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com