Re: sort order in query changes on one SQL Server but not another

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 12/04/04 

Date: Fri, 3 Dec 2004 22:22:50 -0500

The bottom line is that without the Order By it can never be trusted and
even though it seems to work now it may not tomorrow. Especially if you
take into account service packs etc. If you don't use ORDER BY you are the
only one to blame if things don't work as expected. 

-- 
Andrew J. Kelly  SQL MVP
"Stan Reckard" <StanReckard@discussions.microsoft.com> wrote in message 
news:710824D1-550E-4711-81E3-176C27E99A8B@microsoft.com...
> Thanks for the reply.
>
> What I don't understand is why on one SQL Server it works consistently 
> each
> and every time, while on another SQL Server (same version and service 
> pack)
> the sort order varies practically every time.   If it were chance I would 
> not
> expect to see this type of consistency.
>
> Stan
>
> "Scott Morris" wrote:
>
>> This is a FAQ.  There is no guarantee of order - EVER- for a query that 
>> has
>> no order by clause.   If you want an order, you MUST specify it.  It is 
>> only
>> by chance that the problem has not appeared earlier.
>>
>> "Stan Reckard" <StanReckard@discussions.microsoft.com> wrote in message
>> news:4B0C436E-75CD-4D2A-B311-53D8082946CB@microsoft.com...
>> > I have a SQL query that when run in SQL Query Analyzer gives a 
>> > different
>> > ordering of records on each execution.  This happens on SQL Server 2000
>> Ver
>> > 8.00.760 (SP3).  We have the exact same database on another SQL Server
>> where
>> > the same query executes consistently each time.  Since the two version 
>> > of
>> SQL
>> > Server are the same version and service pack, the difference must be in
>> some
>> > SQL Server setting.
>> >
>> > I checked the collation by running sp_helpsort on each database and get
>> the
>> > same result for both.  Does anyone know of any settings that would 
>> > effect
>> the
>> > sort order on queries that do not specify an ORDER BY clause?
>> >
>> > Our query is a bit complicated.  It is a query on a view that uses 
>> > several
>> > left joins.  The query futher uses an exists clause and filters by a 
>> > bit
>> > field.  Interestingly, when I remove the bit clause the sort order 
>> > becomes
>> > stable.  If I cast the bit to a char it is stable.  If I use a date 
>> > filter
>> > instead of the bit filter it again gives inconsistent sort orderings.
>> >
>> > I would like to focus on finding a SQL Server setting to fix this 
>> > problem
>> > rather than trying to change the SQL query - especially since it works 
>> > on
>> one
>> > SQL Server but not on another.
>> >
>> > Thanks,
>> >
>> > Stan
>> >
>>
>>
>>

Relevant Pages

  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Access?
    ... One thing is that if you have more than one join in the From clause you have to use Parentheses ... You might try building the base of the query using the query grid. ... inner join cmsopen.dbo.apm_vendor v ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ...
    (microsoft.public.access.queries)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Maximum number of elements ina an IN() Clause
    ... if the IN clause is the ONLY criteria... ... If you are using MS SQL Server, can you define an heterogenous server, on it, pointing to an Access-Jet database, and push the values in a table there? ... Vanderghast, Access MVP ... would making the passtrough query WITHOUT the in clause and then, ...
    (microsoft.public.access.queries)
  • Re: ORDER BY in VIEW not working
    ... SQL Server is NOT guaranteed to repect an ORDER BY ... in the view unless you use ORDER BY also when you *query* the view. ... CREATE VIEW TopView AS ... Although the view definition contains an ORDER BY clause, ...
    (comp.databases.ms-sqlserver)
  • Re: Indexing Service, Openquery and sp_executesql
    ... SQL Server version and sp are you running? ... > data from the indexing service catalog when pasted into ... > query analyzer, but failed when put against sp_executesql ... I would choose Microsoft Indexing ...
    (microsoft.public.sqlserver.fulltext)