[dba-SQLServer] Strange happening

Eric Barro ebarro at verizon.net
Thu Feb 28 08:53:28 CST 2008


In fact in SQL Management Studio it forces the saved query for the view to
use TOP 100 PERCENT even if you delete it, it will still insert that when
you save the view. 

-----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 6: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




More information about the dba-SQLServer mailing list