Clustered index update, how changing the index only at different column value?

Question

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

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)

update query without change Name column(execution CPU time 70ms)

Update

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

Update

Question is closed

Answer 1

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 ; 

Answer 2

0 down vote unaccept

For fix my issue, I created a triger

CREATE TRIGGER [dbo].[trg_BeforeUpdateSubject] 
   ON  [dbo].[Subject] 
   INSTEAD OF UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
        declare @old_val  nvarchar(255),@new_val nvarchar(255) ;
        select
@old_val=[Name]   
from deleted;
select 
@new_val=[Name]
from inserted;
     if (@old_val=@new_val)  
    BEGIN

        UPDATE 
            Subject
        SET
            set new values for other colums,
            Subject.DLC             =   GETDATE()
        FROM 
            inserted
        WHERE
            Subject.Id = inserted.Id            
    END ELSE
    BEGIN
        UPDATE 
            Subject
        SET
            Subject.Name = inserted.Name,
            set new values for other colums,
            Subject.DLC             =   GETDATE()
        FROM 
            inserted
        WHERE
            Subject.Id = inserted.Id                
    END

END

It's work perfect. Average value for update query is 90ms(CPU), old value 200ms