[dba-SQLServer] Strange happening

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.


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 Francisco
Tapia
Sent: Wednesday, February 27, 2008 7:35 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Strange happening

Maybe it's a plan that's been pre-buffered, try the following before you
re-run the query again or sp_recompile viewname.

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

--
Francisco

On Wed, Feb 27, 2008 at 3:46 PM, jwcolby <jwcolby at colbyconsulting.com>
wrote:

> I have a pretty strange happening.
>
> I have a table with a PK which is an autoincrement int.  When I create 
> a view and include that field, then all order by clauses are ignored.  
> IOW I create a query
>
> SELECT PKID, Fld1, fld2, fld3 ORDER By Fld3 DESC
>
> And when executed the data is ordered by PKID.
>
> If I save the view and then create a QUERY SELECT * from MyView ORDER 
> BY
> Fld3 DESC then it does in fact order by fld3 desc order.  If I save 
> THAT SQL MSTATEMENT into another view and then execute that view, I am 
> right back to ordered by PKID.
>
> I am at a loss as to why this happens and how to avoid it.  I am 
> trying to pull the first 1 million rows, ordered in a random order 
> (for testing of another app) so I tried creating a view where I order 
> by zip 4 DESC.  I store the view and then export the view into a CSV 
> file.  The data comes out ordered on the PKID.
>
> Truly weird!
>
> I can't go further on my testing until I get this resolved.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


--
-Francisco
http://sqlthis.blogspot.com | Tsql and More...
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com