[dba-SQLServer] Strange happening

The issue with order by newID() is performance, add a tablesample clause to
your tables in the from clause to speed up the random sampling.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, February 27, 2008 4:47 PM
To: 'Discussion concerning MS SQL Server'
Subject: [dba-SQLServer] Strange happening

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


__________ NOD32 2908 (20080228) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com