I would like to know which is more efficient and why.

if not exists (select 1 from table where ID = 101 and TT = 5)
begin
    update table 
    set TT = 5
    where ID = 101;
end;

or

update table 
set TT = 5
where ID = 101 and TT <> 5;

Assume there is a clustered index on ID (nothing more table used default table creation setting)

CodePudding user response：

SQL Server will generally optimize a non-updating UPDATE to not actually issue any updates. Therefore, with a simple table, you are not going to see much difference.

• If you have triggers, they will be fired if the UPDATE statement executes, irrelevant of how many rows are updated.
• rowversion and GENERATED AS columns will be updated regardless.
• Clustered key columns will cause a delete and insert of the whole row.
• If ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT are on, even if not being used, then due to the way row-versioning works, an actual update will always be made.
• If the IF EXISTS is complex, it still may not be worth doing, but in simple cases it usually is.

CodePudding user response：

WHERE, IF EXISTS and IN all have different performance benefits. I would suggest checking out these two articles.

https://www.sqlshack.com/t-sql-commands-performance-comparison-not-vs-not-exists-vs-left-join-vs-except/

https://sqlchitchat.com/sqldev/tsql/semi-joins-in-sql-server/