2

I have to run an UPDATE statement on live online database with 500k records. I want to know which statement will run faster:

Update Table set REC_ID = isnull(REC_ID,'')

Update Table set REC_ID = '' where REC_ID is null

i am using Microsoft SQL Server 2012

2
  • 1
    If you've got SSMS: enter one update after the other and click the "Display Estimated Execution Plan" (Ctrl + L). This will compare the updates and you can get an indication of which one it thinks will be quicker.
    – dwjv
    Feb 18, 2016 at 15:27
  • Yes I checked the executive plan but after that I was Confafed then I was asking for the question. thanks for suggestion Aug 30, 2018 at 6:46

5 Answers 5

7

Those queries won't do the same thing.

Update Table set REC_ID = isnull(REC_ID,'')

This one will update each record and if REC_ID is NULL it will set it to ''.

Update Table set REC_ID = '' where REC_ID is NULL

This one will only update records containing a null value in REC_ID, and set it to ''.
While both of them will end up giving the same result, the second one will be executed on less records (except if every REC_ID is NULL), it should be faster.

0
1

The only difference in above two cases is the first statement don't have a where clause so all the rows in the table will be updated irrespective of the fact that REC_ID is NULL or not. While in second case only those rows will be updated where REC_ID is null.

Performance would have been impacted when you had condition like:

Update Table set REC_ID = '' where isnull(REC_ID,'') ='' 

because in the above case the predicate is no longer a search argument. This means the SQL server cannot efficiently use an index on REC_ID column.

1
  • Your way is right, but I do not need this condition right now. Thanks for your answer; Aug 30, 2018 at 6:48
-1

I think you have a misunderstanding here. Those two statements do not do the same thing.

Run those commands in a sample dataset and you will see that they do not do the same thing.

0
-1

In theory, the "winner" should (assuming there are no indexes):

Update Table set REC_ID = '' where REC_ID is null

There's a "IS_NULL" bitmap stored value that should quickly fetch the rows where REC_ID is NULL. SQL Server automatically saves a TRUE (1) value for each null column, and a FALSE (0) for each one that is not null. The other query presented has to evaluate after applying an ISNULL() function.

-2

The WHERE clause in your UPDATE statement could be used to speed the search for matching rows when the field in question is part of an index. In this example, if REC_ID is indexed and the selectivity of the field is good, the WHERE clause will help, but if it's poor, a table scan will likely be required anyway and both statements will last almost the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.