Francisco Tapia
fhtapia at gmail.com
Wed May 14 08:39:21 CDT 2008
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? On 5/14/08, jwcolby <jwcolby at colbyconsulting.com> wrote: > 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 > > -- Sent from Gmail for mobile | mobile.google.com -Francisco http://sqlthis.blogspot.com | Tsql and More...