Is it faster to UPDATE a row, or to DELETE it and INSERT a new one?

Question

Given two scenarios on SQL Server 2008/2005 - 1 Table has 5 rows 2 Table has 1 million rows

If we need to update a few rows, what is is efficient and why? 1) UPDATE the required columns 2) DELETE the row and INSERT new row with the updated information

Answer 1

You should not be asking this question. You are asking "Is it better to do it the right way, or the wrong way, in the name of some nebulous idea of 'faster'?"

Do you have an application that is somehow too slow? Do you for some reason think that the problem is because your UPDATEs are taking too long? Have you done any measurement and benchmarking of the performance of your database interactions?

What you are doing is premature optimization of the worst kind, and you are doing your application a disservice by doing so. You are making wild guesses about how to speed up your code, with absolutely nothing to base it on.

Write your code right. Then try to find where you have a performance problem. Do you even HAVE a performance problem, or are you asking this question simply because you think it's something you should be asking about? You shouldn't.

Even if you specifically DID have a problem with your UPDATEs being too slow, we can't answer the question of "Is X faster than Y" because you have not given us nearly enough information, such as:

• What database you are using
• The table layouts
• What indexes are on the database
• How you're interfacing with the database

Please, write your code correctly, and then come back with specifics about what is too slow, rather than guessing at micro-optimizations.

Answer 2

Usually updating a single row will be faster. Reason being deleting existing row and inserting a new row, both of these operations will impact clustered index. Updating a single row will also have impact on various indices but not on clustered index. No data point to support my claim but logically DB engines should behave this way.