Re: Strange problem

Re: Strange problem

 

  

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


MS Sql Server LazyDBA home page