Paul Nielsen
pauln at sqlserverbible.com
Thu Feb 28 10:12:27 CST 2008
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