RE: Strange problem

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]
>



MS Sql Server LazyDBA home page