Paul Nielsen
pauln at sqlserverbible.com
Thu Feb 28 12:27:11 CST 2008
A view is nothing more than a saved query. There's no virtual table, no saved data. The order by is supposed to be added by the outer query that selects from the view. The theory is that the view is used to "project" certain columns, but the order by belongs in the outermost query, not in a view or subquery. Data sources (table, views, subqueries, Xqueries, full-text search, etc) don't have a sort order. The fact that you can Open View and see the results of a view in SSMS is less than correct. Behind the scenes, SSMS is wrapping the view in a select * from view. -Paul -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Nancy Lytle Sent: Thursday, February 28, 2008 10:43 AM To: Discussion concerning MS SQL Server Subject: Re: [dba-SQLServer] Strange happening Don't think of a view as a 'view' but as a virtual table, and in a table there is no 'sort' order. You would have to query a view, just like a table to get a sort order. Hope this helps,Nancy Lytle N_Lytle at terpalum.umd.edu > From: jwcolby at colbyconsulting.com> To: dba-sqlserver at databaseadvisors.com> Date: Thu, 28 Feb 2008 12:10:36 -0500> Subject: Re: [dba-SQLServer] Strange happening> > Paul,> > I make no claim to be an SQL expert, so fill me in on why a view should not> be ordered - other than "by SQL Definition". I always just kind of thought> that a view was a stored "view" (look at) some set of data. It makes> perfect sense to order a "look at" some set of data. I might want a view of> sales people ordered by name, or a view of orders in reverse chrono order> etc.> > So what is a VIEW and why would it not be ordered? And if the view can not> be ordered, then how do you get the data from a view ordered? > > > John W. Colby> Colby Consulting> www.ColbyConsulting.com > -----Original Message-----> From: dba-sqlserver-bounces at databaseadvisors.com> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul> Nielsen> Sent: Thursday, February 28, 2008 11:12 AM> To: 'Discussion concerning! MS SQL Server'> Subject: Re: [dba-SQLServer] Strange happening> > By SQL definition, a view should not include any sort order. But top is> useless without a sort order, so order by is allowed with a top. For> example, a view that returns the top 5 salespersons would have to include> the top and the order by. The Top 100 percent is a hack/trick to use an> order by in a view. In SQL Server 2005 RTM the syntax is allowed but the QP> detects the trick and ignores the order by. I think in one of the sp the> order by again works, but it is not correct to order by in a view. > > -Paul > > -----Original Message-----> From: dba-sqlserver-bounces at databaseadvisors.com> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby> Sent: Thursday, February 28, 2008 7:46 AM> To: 'Discussion concerning MS SQL Server'> Subject: Re: [dba-SQLServer] Strange happening> > If order by is ignored in a view without a TOP, why is a TOP considered bad> form? It sounds like a REQUIREMEN! T. > > > John W. Colby> Colby Consulting> www.ColbyConsulting.com> --- --Original Message-----> From: dba-sqlserver-bounces at databaseadvisors.com> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Paul> Nielsen> Sent: Thursday, February 28, 2008 9:36 AM> To: 'Discussion concerning MS SQL Server'> Subject: Re: [dba-SQLServer] Strange happening> > Order by is ignored in a view unless is has a top. The Top 100 percent trick> works in some versions but not all versions (sp versions) of SQL Server.> Using the top 100 percent trick to force an order by in a view is considered> a bad practice. > > -Paul > > -----Original Message-----> From: dba-sqlserver-bounces at databaseadvisors.com> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur> Fuller> Sent: Thursday, February 28, 2008 6:07 AM> To: Discussion concerning MS SQL Server> Subject: Re: [dba-SQLServer] Strange happening> > I think that you could modify the index to include the NewID() column as> Stuart suggested, and get the results you want. Alternatively, SELECT T! OP n> * FROM yourView ORDER BY NewID().> > A.> > On 2/28/08, jwcolby <jwcolby at colbyconsulting.com> wrote:> >> > I do have a cover index that includes all of the fields in my view, > > and PK is the leftmost (top) field in the index. I am thinking that > > perhaps it is pulling all of the data from that index and somehow that > > is causing the issue.> >> >> _______________________________________________> dba-SQLServer mailing list> dba-SQLServer at databaseadvisors.com> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver> http://www.databaseadvisors.com> > > __________ NOD32 2908 (20080228) Information __________> > This message was checked by NOD32 antivirus system.> http://www.eset.com> > > _______________________________________________> dba-SQLServer mailing list> dba-SQLServer at databaseadvisors.com> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver> http://www.databaseadvisors.com> > _______________________________________________> dba-SQLServer mailing li! st> dba-SQLServer at databaseadvisors.com> http://databaseadvisors.com/ma ilman/listinfo/dba-sqlserver> http://www.databaseadvisors.com> > > __________ NOD32 2909 (20080228) Information __________> > This message was checked by NOD32 antivirus system.> http://www.eset.com> > > _______________________________________________> 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> _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com __________ NOD32 2909 (20080228) Information __________ This message was checked by NOD32 antivirus system. http://www.eset.com