Yes, you do insert with an order by, but then you proceed to update the table, which may change the order of the rows, when there is no clustered index.
But really, who cares how the rows are ordered, until you finally do a select -- with an order by. If order is important for intermediate processing, then you must handle that explicitly. There is no guarantee that the rows will be in the order you desire unless you explicitly enforce that requirement.
P
-----Original Message-----
From: nomi nomi
[mailto:mssqldba-ezmlmshield-x22212693.[Email address protected]
Sent: Wednesday, June 30, 2004 1:27 AM
To: LazyDBA Discussion
Subject: Re: Strange problem
Thanks for all of your replies. But i think some of u have not understood
the question clearly.
Look i am using order by clause when using the 'insert into.....' statement
so it should be ordered but it is not.
just have a look at different sets of data that i am getting as a result of
different executions:
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
Please try this sp. at your site and let me know of any difference in the
output.
Thanks
nomi
>From: "Albert van Biljon " <[Email address protected]
>To: [Email address protected]
>Subject: Re: Strange problem
>Date: Tue, 29 Jun 2004 06:09:47 -0700 (PDT)
>
>**A LazyDBA.com subscriber has responded to your lazydba.com post**
>**LazyDBA.com mail shield has forwarded you this email,
>**and removed any attachments, and kept your email address secret
>**from this person, and any viruses/trojans.
>**If you reply to this email, the person will see your email address as
>normal
>**Anything below this line is the original email text
>
>
>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]
> >
> >
>
>
>
_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
---------------------------------------------------------------------
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