How to Optimize CASE Statement Updates in SQL Server 2005 to Avoid Full Table Scans?

CASE Statement Update Optimization in SQL Server 2005

Original Question:

How to selectively update records using a CASE statement in SQL Server 2005 without scanning all unaffected rows?

Context:

The following CASE statement is used to update specific values in the LASTNAME column of the dbo.TestStudents table:

UPDATE dbo.TestStudents SET LASTNAME = 
( CASE 
WHEN (LASTNAME = 'AAA') THEN 'BBB' 
WHEN (LASTNAME = 'CCC') THEN 'DDD' 
WHEN (LASTNAME = 'EEE') THEN 'FFF' 
ELSE (LASTNAME)
END )

While this statement achieves the desired purpose, the ELSE condition requires scanning all rows in the table.

Solution:

To avoid scanning unaffected rows, a WHERE clause can be added to the statement to specify which rows should be updated.

UPDATE dbo.TestStudents SET LASTNAME = CASE 
WHEN LASTNAME = 'AAA' THEN 'BBB' 
WHEN LASTNAME = 'CCC' THEN 'DDD' 
WHEN LASTNAME = 'EEE' THEN 'FFF' 
ELSE LASTNAME
END 
WHERE LASTNAME IN ('AAA', 'CCC', 'EEE')

By specifying the LASTNAME values to be updated in the WHERE clause, the statement only updates the affected rows and leaves other rows unchanged.

The above is the detailed content of How to Optimize CASE Statement Updates in SQL Server 2005 to Avoid Full Table Scans?. For more information, please follow other related articles on the PHP Chinese website!