Re: Sorting Problem
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/30/04
- Next message: Tibor Karaszi: "Re: natural order of seleted records"
- Previous message: nomi: "Re: Sorting Problem"
- In reply to: nomi: "Re: Sorting Problem"
- Next in thread: Vishal Parkar: "Re: Sorting Problem"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 30 Jun 2004 12:23:36 +0200
Short answer is that a table is not sorted, and it seems that you are using a technique which is
depending on the execution plan. The optimizer decides to handle one case differently from the other
case. But as you are expecting something from SQL Server which isn't documented, you cannot rely on
this behavior.
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "nomi" <nomi@discussions.microsoft.com> wrote in message news:71459C53-7484-4773-8F90-A420822B2A8C@microsoft.com... > 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 > > > > > > > > >
- Next message: Tibor Karaszi: "Re: natural order of seleted records"
- Previous message: nomi: "Re: Sorting Problem"
- In reply to: nomi: "Re: Sorting Problem"
- Next in thread: Vishal Parkar: "Re: Sorting Problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
