Hi
This is surely a newbie question but I'm not sure of the answer.

If you do an update with a where clause (or delete/insert for that
matter), does it work by first gathering all the rows and then doing
the update?

This matters if the update would alter the table that the where
clause is working on. I think it does but just want someone to confirm
it for me

ta

Hi
UPDATE tbl SET col =... WHERE col1=....

SQL Server starts to run throu a table to examine col1 to find (IX lock) a
row/s that will be updated. ( where found that X lock applies)
Then it deletes the old value and then insert a new one.

As for INSERT ,SQL Server will allocate a new page/s for new data at the end
or some of the pages will be splitted and new pages will be allocated



<codefragment@googlemail.com> wrote in message
news:b55ddf12-61c7-470f-99e0-504ede2572d4@l42g2000hsc.googlegroups.com...
> Hi
> This is surely a newbie question but I'm not sure of the answer.
>
> If you do an update with a where clause (or delete/insert for that
> matter), does it work by first gathering all the rows and then doing
> the update?
>
> This matters if the update would alter the table that the where
> clause is working on. I think it does but just want someone to confirm
> it for me
>
> ta

You should think of SQL operations like it all happens simultaneous. At one moment in time we have
one state of the data and at the next moment in time (after your update) we have some other state of
the data. Physically, in real time, the update might not take 0 seconds, of course but logically all
happens at once. That is why we can do things like:

UPDATE tbl
SET c1 = c2, c2 = c1

Above sometimes surprises programmers (who expects that we need some 3:rd "storage" to swap values).
But the cool thing with SQL is that we are working at a somewhat higher level of abstraction and we
don't have to worry about *how* these operations are performed in the end.

What I think you allude to is something that is sometimes called the "Halloween problem" (Google for
more info). Consider below:

CREATE TABLE t(c1 int PRIMARY KEY)
INSERT INTO t (c1) VALUES(1)
INSERT INTO t (c1) VALUES(2)
INSERT INTO t (c1) VALUES(3)

UPDATE t SET c1 = c1 + 1

Now, that UPDATE looks fishy, right? If SQL Server starts by changing 1 to 2, we then would have two
rows with value 2 which would cause a duplicate that violates our primary key constraint (at that
point in time). And what about when changing our 2 to 3, we now have two 2's so which one is which?
Again, we don't have to worry about this, since SQL server has techniques for coping with these
situations. In this particular case, it will perform the update as delete followed by insert. It
will generate all before and all after values. It will do the delete operations first and only after
that perform all the insert operations. You can see this by looking at the (surprisingly complex)
execution plan for that update statement.

This was only one example, but my point is what we don't have to worry about these things. We expect
a behavior from the SQL language and it is SQL server's job to make sure that the correct thing is
what happens. By studying the execution plan we can see how some particular case is handled.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


<codefragment@googlemail.com> wrote in message
news:b55ddf12-61c7-470f-99e0-504ede2572d4@l42g2000hsc.googlegroups.com...
> Hi
> This is surely a newbie question but I'm not sure of the answer.
>
> If you do an update with a where clause (or delete/insert for that
> matter), does it work by first gathering all the rows and then doing
> the update?
>
> This matters if the update would alter the table that the where
> clause is working on. I think it does but just want someone to confirm
> it for me
>
> ta

Thanks for your detailed response

Your first statement reassures me, and I did look at the execution
plan, this is an example of the case I was thinking of:

create table TempA(
columna int);

delete from tempa
insert into tempa values(1);
insert into tempa values(1);
insert into tempa values(1);

update tempa set columna=columna+1
where columna not in (select columna from tempa where columna=2)

The update will change all the rows from 1 to 2, the where clause
assures us that there are no rows of value 2. From what your saying
and what I see this is correct.

A colleague mentioned that you could have a select large enough to use
temporary storage, during the select the temporary storage could be
wiped for whatever reason (large number of simultanious queries?)
which would result in it being created again and hence going wrong.
This doesn't sound right to me, is it possible?

> A colleague mentioned that you could have a select large enough to use
> temporary storage, during the select the temporary storage could be
> wiped for whatever reason (large number of simultanious queries?)
> which would result in it being created again and hence going wrong.
> This doesn't sound right to me, is it possible?

You are correct.
SQL Server returning incorrect result would be a bug in the product. I.e., SQL Server will not wipe
out stuff it stored in tempdb which is needed by further processing of the query if that can result
in the query returning incorrect result. If it runs out of space (tempdb) for the modification in
question, then the modification in question goes wrong and a rollback is performed.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


<codefragment@googlemail.com> wrote in message
news:0c2434ec-2f57-4ebe-a645-59b34eae0450@e53g2000hsa.googlegroups.com...
> Thanks for your detailed response
>
> Your first statement reassures me, and I did look at the execution
> plan, this is an example of the case I was thinking of:
>
> create table TempA(
> columna int);
>
> delete from tempa
> insert into tempa values(1);
> insert into tempa values(1);
> insert into tempa values(1);
>
> update tempa set columna=columna+1
> where columna not in (select columna from tempa where columna=2)
>
> The update will change all the rows from 1 to 2, the where clause
> assures us that there are no rows of value 2. From what your saying
> and what I see this is correct.
>
> A colleague mentioned that you could have a select large enough to use
> temporary storage, during the select the temporary storage could be
> wiped for whatever reason (large number of simultanious queries?)
> which would result in it being created again and hence going wrong.
> This doesn't sound right to me, is it possible?

Thanks again. My mind is at ease :-)