Re: Strange problem

Re: Strange problem

 

  


Albert,
u r right it should be:

select ccode, cdesc, counter
FROM STable
WHERE sid=@sid
and stypeid=6
order by counter

field 1 and field2 are basically ccode and cdesc

but that doesn't matter much.

Thanks.
nomi

>From: "Albert van Biljon " <[Email address protected]
>To: [Email address protected]
>Subject: Re: Strange problem
>Date: Wed, 30 Jun 2004 03:26:15 -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
>
>
>From my experience, it seems to me that you can insert say 5 rows into
>a table, and they will stay in the order in which they were inserted.
>Now I'm assuming a table with no clustered or other index.
>
>If you make a select on the rows without an ORDER BY, they are returned
>in that order.
>
>However, when you then make an update on say the second row, and then
>do the same select, the last updated row will be returned last.
>
>I do not have knowledge of how SQL server stores tables internally, but
>it may have something to do with that. The temp tables may be stored
>differently than the permanent tables. I would say that you should try
>to make some indexes so that you do not depend on chance for returning
>rows in the correct order.
>
>Also, some people have mentioned parallel processing of the query,
>which also seems quite likely to me to give you rows in an
>unpredictable order if you do not specify the order somehow.
>
>In your insert statements, specify the table names as well, not only
>the column names. However, I would have expected that you will get a
>ambigous column name-error if that was a problem.
>
>In your select, I'm also not sure where you get field1 and field2 - I
>don't see them in your table's create statement.
>
>select counter,
>CCode,
>field1,
>field2
>FROM STable
>WHERE sid=@sid
>and stypeid=6
>order by counter
>
>Regards,
>Albert
>
>--- nomi nomi <mssqldba-ezmlmshield-x93206692.[Email address protected]
>wrote:
> > Albert,
> >
> > I think you are right that sql server inserts the data in some
> > unpredictable
> > order. You can confirm this by running a select query before updating
> > the
> > counters. You will see that update has no effect on the order of the
> > data,
> > but it's only 'insert into' which is inserting the data at it's own
> > will.
> > However what i fail to understand is that if this is the case then
> > why the
> > sp performs correctly when i use the temporary table approach instead
> > of
> > permanent table which i have also described.
> > Any thoughts on that.
> >
> > Thanks
> > nomi
> >
> >
> > >From: "Albert van Biljon " <[Email address protected]
> > >To: [Email address protected]
> > >Subject: Re: Strange problem
> > >Date: Wed, 30 Jun 2004 02:20:26 -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
> > >
> > >
> > >As I understand it, with one run, the counters get the values 1 to 5
> > >for values A to E for instance, and with the next run, the counters
> > 1
> > >to 5 are assigned to F to J.
> > >
> > >When you are updating the Counter-column, you don't do the updates
> > in
> > >any particular order, which means you depend on the way SQL Server
> > >returns it to you, which is not very predictable if you have no
> > >WHERE-clause.
> > >
> > >update STable
> > >set counter = @counter,
> > >@counter = @counter + 1
> > >where sid=@sid
> > >and STypeID=@STypeID
> > >ORDER BY STable. ...
> > >
> > >Can you try it and let us know?
> > >
> > >--- nomi nomi <mssqldba-ezmlmshield-x17408429.[Email address
> > protected]
> > >wrote:
> > > >
> > > > Albert,
> > > > Thanks for your response.
> > > > But i have checked it numerous times the problem is with 'insert
> > > > into...'
> > > > and not with update.
> > > >
> > > > u can check it by inserting a select stmt. before insert
> > statement.
> > > >
> > > > Thanks
> > > > nomi
> > > >
> > > > >From: "Albert van Biljon " <[Email address protected]
> > > > >To: [Email address protected]
> > > > >Subject: Re: Strange problem
> > > > >Date: Wed, 30 Jun 2004 00:44:44 -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
> > > > >
> > > > >
> > > > >I think I understand the problem now.
> > > > >
> > > > >The problem is probably not with the insert, but with the update
> > -
> > > > try
> > > > >an ORDER BY in your update clause (update #tempSTable...) and
> > see if
> > > > >you get better results.
> > > > >
> > > > >--- nomi nomi <mssqldba-ezmlmshield-x22212693.[Email address
> > > > protected]
> > > > >wrote:
> > > > > > 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
>
>
>

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail


MS Sql Server LazyDBA home page