[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 REQUIREMENT. > > > 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 TOP 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 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> > > _______________________________________________> 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>