Hi
I am running an update against a table with over 100,000,000 rows which takes forever. Is there a better way to write a query like this?

UPDATE [dbo].[Test_Table]
SET [Column_34] =(
CASE
WHEN [Column_2] > 25 AND [Column_2] <= 34 THEN 1
ELSE 0 END)

Hello,

Do you have an index on [Column_2]? If not, it's going to do a table scan... which may take a while...

-Sean

Instead of updating everything in one shot, you can update in chunks. You can use TOP to select x number of rows to update, however, you would need some criteria to check and update only records that have not been updated. What does column_34 column values are like right now? If the column_34 values are not either 0 or 1 to start off, you can use that as your condition. Updating in chunks will also help curb the transaction log growth.

What indexes you've on the table?

Did you check execution plan? What does it suggest?

Yes I have an index on [Coulumn_2]

Good suggestion, thanks

How much rows does it update? You can also use a TOP clause , especially if SQL Server decides to scan the table due to lack of the useful index.

UPDATE TOP (1000) [dbo].[Test_Table]
SET [Column_34] =(
CASE
WHEN [Column_2] > 25 AND [Column_2] <= 34 THEN 1
ELSE 0 END)