[dba-SQLServer] Strange happening

jwcolby jwcolby at colbyconsulting.com
Thu Feb 28 11:10:36 CST 2008


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




More information about the dba-SQLServer mailing list