Sql-server – Clustered index update, how changing the index only at different column value

execution-plansql serversql-server-2016

I've table,
example as

Id int primary key(clustered index)
Name varchar(255)not null (non-clustered index)
..
..
Other columns

I execute stored procedur as

update table1 set table1.Name=isnull(@Name,table1.Name) ,( updating other columns) where Id=@Id;

many times Name equals @Name;
In execution plan i saw enter image description here

if i change stored procedure as 

update table1 set ( updating other columns) where Id=@Id;

i saw Cost:15%

Question:

I need to change this procuder for two situation when

  1. table1.Name= @Name
  2. table1.Name <> Name.

In first case table1.Name doesn't change value or index doesn't update and clustered index update cost 15%;

in second case table1.Name changes value and clustered index update cost 50%.

Why index is updating when update a equal value?

i can't select "Name" and compare befor updatings

I know that create varchar column index isn't good idea

Update

update query with change Name column(execution CPU time 200ms)
enter image description here

update query without change Name column(execution CPU time 70ms)
enter image description here

Update

maybe anybody can help with trigger if it can help with this issue?

Update

Question is closed

Best Answer

how many record will updated at one go and how frequently ?

Why index is updating when update a equal value?

Because optmizer is not smart enough to detect if you are updating same value.

It is good that index is rebuild of its own,though it is time consuming.

If you disable index then you have to rebuild it later.

your situation is very serious ,1.one row, 30 000 per minute Hope you have handle the requirement correctly.

As by definition of index or disadvantage of index is that it slow down update statement if that table is so frequently updated.

Now you have to answer how frequently Name is use in predicate and how.is index helping you there.Depending upon all this it is better to drop index on Name.

Don't keep any non clustered index in these column which are use in this proc.

since @name 10% is null you need dynamic sql whether you drop index or not. it will improve your query on 90% occasion.

sp_executeSql example (just an example)

declare @Name varchar(250)
declare @othercol varchar(250)
declare @Id int=1

DECLARE @ParmDefinition nvarchar(500); 
set @Sql='update table1 set'

if (@Name is not null)
set @Sql=@Sql+' Name=@Name1 ,'


set @Sql=@Sql+' othercol=@othercol1 '


set @Sql=@Sql+' where Id=@LocalID '

SET @ParmDefinition = N'@LocalID int , @Name1 varchar(250) , @othercol1 varchar(250)';  

print @Sql

EXECUTE sp_executesql @Sql, @ParmDefinition,  
                      @LocalID = @Id,@Name1=@Name,@othercol1=@othercol ;
Related Question