"case when" vs multiple update

Which one is faster and cost less for tables with a lot of rows:
1. update atable
set acol = case when cond1 then 1 when cond2 then 2 ..... end
2. update atable set acol = 1 where cond1
update atable set acol = 2 where cond2
update atable set acol = 3 where cond3
....>> Which one is faster and cost less for tables with a lot of rows:
Test it. One UPDATE statement with a CASE tends to be faster than multiple
ones.
Anith|||1 is probably cheaper. But it depends on whether you have a WHERE clause and
the selectivity, how
efficient indexes you have to drive that where clause and also how many rows
you modify.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:A755000B-0491-4ECA-8BF3-0F6FE1A2B456@.microsoft.com...
> Which one is faster and cost less for tables with a lot of rows:
> 1. update atable
> set acol = case when cond1 then 1 when cond2 then 2 ..... end
> 2. update atable set acol = 1 where cond1
> update atable set acol = 2 where cond2
> update atable set acol = 3 where cond3
> .....
>|||it depends, as usual.
look at the execution plans. If at least one of those updates scans the
whole tables, combine them into one update.
On the other hand, if every simple update accesses the table via a
highly selective index and touches just a handful of rows, it could be
better leave them as is, because the combined statement might be
executed as an expensive table scan|||The table may have up to 10 million rows. And the conditions may be only up
to 30s. So it sounds a low selective.
"Tibor Karaszi" wrote:

> 1 is probably cheaper. But it depends on whether you have a WHERE clause a
nd the selectivity, how
> efficient indexes you have to drive that where clause and also how many ro
ws you modify.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:A755000B-0491-4ECA-8BF3-0F6FE1A2B456@.microsoft.com...
>|||I'm not sure what you mean by "And the conditions may be only up to 30s". Ar
e you saying that the
SELECT statement will typically return about 30 rows from a table with 10,00
0,000 rows? If so, the
query has very high selectivity (it returns relatively few rows). In that ca
se, I'd go with my
original recommendation. If you cannot create that index as a clustered inde
x, a non-clustered index
should be efficient as well (thanks to the high selectivity).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:5D4A2719-76F6-46BD-9249-94CFA8BE8656@.microsoft.com...
> The table may have up to 10 million rows. And the conditions may be only u
p
> to 30s. So it sounds a low selective.
> "Tibor Karaszi" wrote:
>