Every table had similar CRUD procedures that had been generated. If any data in the Users table changed, say a user updated their email address, this procedure would handle the update. Is this a good idea?

Aaron Alton recently posted about UPDATE statements. In his post, he explains why it is a good idea to use the WHERE clause to filter out rows that don’t need to be updated. I want to expound on Aaron’s point, and say that you should also avoid needlessly updating columns that don’t need to be updated. The reason? Nonclustered Indexes.

Even though the FirstName and LastName values aren’t changing, SQL Server will still update all of the columns resulting in the nonclustered indexes on the FirstName and LastName columns being locked and updated.

If we were to run the following UPDATE statement instead, the nonclustered indexes on FirstName and LastName would not need to be updated.

UPDATE Users
SET EmailAddress ='jsmith@hotmail.com'WHERE id =1

So, how do we fix the problem with our update procedure listed above? If we know that updating an Email address is a common occurance, we might create a seperate procedure that only updates the EmailAddress column. Alternatively, we can use dynamic SQL to build the correct UPDATE statement for us.