[dba-SQLServer] Strange happening

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





More information about the dba-SQLServer mailing list