Re: Sorting Problem

From: nomi (nomi_at_discussions.microsoft.com)
Date: 06/30/04 

Date: Wed, 30 Jun 2004 03:20:01 -0700

Uri,

I think you are right in saying that sql server has some unpredictable order when inserting the data, but what i fail to understand is that how the temporary table approach is working fine and permanent table approach is giving wrong results. if there is a problem with the order it should be uniform across every scenario.

Any thoughts on that.

Thanks
nomi

"Uri Dimant" wrote:

> nomi
> >The problem occurs in the insert >into statement, it does not insert >the
> data in sorted order inspite of >order by clause.
>
> When you insert data into the table an order (in which rows are inserted) is
> undefined. Only by running SELECT clause and specify ORDER BY clause you can
> define an order output.
>
>
>
> "nomi" <nomi@discussions.microsoft.com> wrote in message
> news:A273D872-7239-461B-8836-39E928A2262C@microsoft.com...
> > 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. 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?
> >
> > Here is the output of the query after different execution of the sp. on
> the same data.
> >
> > counter CCode field1 field2
> > ----------- -------------- ------------- ------------------------
> > 1 ACETRAVE0001 ACETRAVE0001 Ace Travel
> > 2 ADVANCED0001 ADVANCED0001 Advanced Office Systems
> > 3 ALLENSON0001 ALLENSON0001 Allenson Properties
> > 4 AMERICAN0001 AMERICAN0001 AmericaCharge
> > 5 ASSOCIAT0001 ASSOCIAT0001 Associated Insurance Inc.
> > 6 ATTRACTI00001 ATTRACTI00001 Attractive Telephone Co.
> >
> >
> >
> > counter CCode field1 field2
> > ----------- -------------- --------------- -------------------------------
> ----------------------------------
> > 1 PAGEMAST0001 PAGEMAST0001 PageMaster
> > 2 PERMIERS0001 PERMIERS0001 Premier System, Inc.
> > 3 PRINTER1 PRINTER1 Print Vendor
> > 4 PROFESSI0001 PROFESSI0001 Professional Travel Consultant
> > 5 READYREN0001 READYREN0001 Ready Rentals
> >
> >
> >
> > Thanks
> > nomi
> >
>
>
>

Loading