RE: Strange problem

RE: Strange problem

 

  

I agree - it used to be true under v6.5 but from v7 onwards it is not.
Typical examples are queries with multiple 'threads' (e.g. parallel plans or
UNIONs) where the data from the quickest 'thread' will come back first. MS
'advised' about this before v7 came out, if in doubt use an ORDER BY.

Keith

-----Original Message-----
From: RRazzano
[mailto:mssqldba-ezmlmshield-x18756261.[Email address protected]
Sent: 29 June 2004 18:09
To: LazyDBA Discussion
Subject: RE: Strange problem


That's not really true. Quite often the data will end up in the order
of the clustered index, but there is nothing in the SQL specification to
say how data in the result set should be returned absent an ORDER BY,
and SQL Server doesn't guarantee any particular order. It is not a good
idea to just assume that the data will always come back sorted one way
or another.

-----Original Message-----
From: Parthasarathy Mandayam
[mailto:mssqldba-ezmlmshield-x31357540.[Email address protected]
Sent: Tuesday, June 29, 2004 11:57 AM
To: LazyDBA Discussion
Subject: RE: Strange problem


If you have a primary key on your table, data will always be sorted by
that
column otherwise it is random unless you specify an order by clause



Regards

Partha Mandayam
Software Consultant
Home page: http://partha.tripod.com






>From: "Chris Hagemaier "
><mssqldba-ezmlmshield-x67318964.[Email address protected]
>To: "LazyDBA Discussion" <[Email address protected]
>Subject: RE: Strange problem
>Date: Tue, 29 Jun 2004 09:51:41 -0700
>Received: from www.websitesondatabases.com ([216.234.186.193]) by
>mc5-f5.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Tue, 29 Jun
2004
>09:52:55 -0700
>Received: (qmail 10640 invoked by uid 121); 29 Jun 2004 17:27:49 -0000
>Received: (qmail 28355 invoked from network); 29 Jun 2004 17:27:49
-0000
>X-Message-Info: JGTYoYF78jHf3EGn+RrISmgOaaVS6hUA
>Mailing-List: contact mssqldba-[Email address protected] run by ezmlm
>Precedence: bulk
>X-No-Archive: yes
>List-Post: <mailto:[Email address protected]
>List-Help: <mailto:mssqldba-[Email address protected]
>List-Unsubscribe: <mailto:mssqldba-[Email address protected]
>List-Subscribe: <mailto:mssqldba-[Email address protected]
>Delivered-To: mailing list [Email address protected]
>Message-ID: <20040629172749.10638.[Email address protected]
>Return-Path: mssqldba-return-6809-MCP111=hotmail.[Email address
protected]
>X-OriginalArrivalTime: 29 Jun 2004 16:52:55.0499 (UTC)
>FILETIME=[865231B0:01C45DF9]
>
>That's normal for SQL Server and Oracle.
>How does Sybase do it? Is the data stored sorted?
>
>-----Original Message-----
>From: Horton Joe (LNI) [mailto:mssqldba-ezmlmshield-x17471286.[Email
>address protected]
>Sent: Tuesday, June 29, 2004 9:40 AM
>To: LazyDBA Discussion
>Subject: RE: Strange problem
>
>
>We have discovered when we migrated from Sybase to M$SQL that Sybase
>returns
>row incrementally from top of table to bottom - whereas M$SQL seemed to

>jump
>around - nothing that a ORDER BY didn't fix - but it was strange
>nonetheless.
>
>___________________________
>Joe Horton
>Database Developer / Software Engineer
>WISHA/Legal Services Software Development
>Department of Labor and Industries
>Voice (360) 902-5928 fax (360) 902-6200
>
>
>-----Original Message-----
>From: Albert van Biljon [mailto:mssqldba-ezmlmshield-x22296642.[Email
>address protected]
>Sent: Tuesday, June 29, 2004 6:10 AM
>To: LazyDBA Discussion
>Subject: Re: Strange problem
>
>So, are you saying that when you do the SELECT on STable and you
>specify the ORDER BY clause on Counter, that the numbers in the Counter

>column go for instance: 4, 5, 3, 6, 1, 2, etc. in other words, not 1,
>2, 3, 4, 5, 6 sorted?
>
>That would be difficult to believe... I cannot really see how the
>collations can influence an integer column either.
>
>--- nomi nomi <mssqldba-ezmlmshield-x63496923.[Email address
>protected]
>wrote:
> > Hi Everybody,
> >
> > I am having a strange problem, i wonder if anybody knows the
> > solution:
> >
> > I have written the following SP:
> >
> > CREATE PROCEDURE sp_search_results
> > @SID varchar(50),
> > @STypeID int,
> >
> > AS
> >
> > SELECT field1,field2,field3 INTO #tempsearch FROM tbl_cfields
> > ORDER BY field1
> >
> > insert into STable (SID, STypeID, counter, cCode, cdesc) select
> > @SID, @STypeID, 0, field1, field2 from #tempsearch order by field1
> >
> > update STable
> > set counter = @counter,
> > @counter = @counter + 1
> > where sid=@sid
> > and STypeID=@STypeID
> >
> >
> > But when i execute this query:
> >
> > select counter,
> > CCode,
> > field1,
> > field2
> > FROM STable
> > WHERE sid=@sid
> > and stypeid=6
> > order by counter
> >
> > The result set aways has a different order, It is not sorted always.

> > The problem occurs in the insert into statement, it does not insert
> > the data in
> > sorted order inspite of order by clause. i Have created STable with
> > the
> > following script.
> >
> >
> > CREATE TABLE STable (
> > SID varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > STypeID int NOT NULL ,
> > Counter int NOT NULL ,
> > IntID int NULL ,
> > CCode char (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > Cdesc [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON PRIMARY
> >
> >
> > I have even created STable without specifying the collations like
> > this:
> >
> > CREATE TABLE STable (
> > SID varchar (50) NOT NULL ,
> > STypeID int NOT NULL ,
> > Counter int NOT NULL ,
> > IntID int NULL ,
> > CCode char (256) NULL ,
> > Cdesc [varchar] (256) NULL
> > ) ON PRIMARY
> >
> > but it doesn't work.
> >
> >
> > However when i use a temporary table instead of Permanent table it
> > works fine and the data is always sorted, the code is like this:
> >
> >
> > CREATE PROCEDURE sp_search_results
> > @SID varchar(50),
> > @STypeID int,
> >
> > AS
> >
> > SELECT field1,field2,field3 INTO #tempsearch FROM tbl_cfields
> > ORDER BY field1
> >
> > insert into #tempSTable (SID, STypeID, counter, cCode, cdesc) select

> > @SID, @STypeID, 0, field1, field2 from #tempsearch order by field1
> >
> > update #tempSTable
> > set counter = @counter,
> > @counter = @counter + 1
> > where sid=@sid
> > and STypeID=@STypeID
> >
> > insert into STable (SID, STypeID, counter, cCode, cdesc) select SID,

> > STypeID, counter, cCode, cdesc from #tempSTable
> >
> > I presume that it has something to do with collations, but tempdb
> > and the database that i have has same collation settings. Can
> > anybody tell me the
> > reason?
> >
> > Thanks
> > nomi
> >
> > _________________________________________________________________
> > MSN Toolbar provides one-click access to Hotmail from any Web page -

> > FREE download!
> > http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/
> >
> >
> >
> > --------------------------------------------------------------------
> > -
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > Website : http://www.LazyDBA.com
> > To unsubscribe: http://www.lazydba.com/unsubscribe.html
> > For additional commands, e-mail: mssqldba-[Email address protected]
> >
> >
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :

>http://www.LazyDBA.com To unsubscribe:
>http://www.lazydba.com/unsubscribe.html
>For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :

>http://www.LazyDBA.com To unsubscribe:
>http://www.lazydba.com/unsubscribe.html
>For additional commands, e-mail: mssqldba-[Email address protected]
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :

>http://www.LazyDBA.com To unsubscribe:
>http://www.lazydba.com/unsubscribe.html
>For additional commands, e-mail: mssqldba-[Email address protected]
>




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



______________________________________________________________

CONFIDENTIALITY NOTICE

This communication and the information it contains is intended for the person or organisation to
whom it is addressed. Its contents are confidential and may be protected in law. Unauthorised use,
copying or disclosure of any of it may be unlawful. If you are not the intended recipient, please
contact us immediately.

The contents of any attachments in this e-mail may contain software viruses, which could damage your
own computer system. While Marlborough Stirling has taken every reasonable precaution to minimise
this risk, we cannot accept liability for any damage, which you sustain as a result of software
viruses. You should carry out your own virus checking procedure before opening any attachment.


Marlborough Stirling plc, Registered in England and Wales
Registered No. 3008820,
Allen Jones House, Jessop Avenue, Cheltenham, Gloucestershire, GL50 3SH
Tel: 01242 547000 Fax: 01242 547100
http://www.marlborough-stirling.com


MS Sql Server LazyDBA home page