Custer index update

Last Post 26 May 2008 08:30 AM by AlexB_SQL. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
AlexB_SQL
New Member
New Member

--
26 May 2008 06:46 AM
Hi,

Why when I update a column on a table that does not in clustered index, profiler shows that a cluster index update occurred?

Thanks

Alex
AlexB_SQL
New Member
New Member

--
26 May 2008 08:30 AM
I observed this behaviour on a production BD and test it with the following script:

create table xx(
n1 int
,c1 varchar(3)
, n2 float,
CONSTRAINT PK_X PRIMARY KEY CLUSTERED (n1)
)
go

DECLARE @cont int
SET @cont=0
WHILE
@cont<500

BEGIN
begin tran
INSERT INTO
xx (n1,c1,n2)
VALUES (@cont,'1',100000000*rand(@cont))
commit
set @cont=@cont+1
END
go

SELECT *,cast(floor(n2) as integer) FROM XX
go

UPDATE XX
SET C1='par' WHERE 0=cast(floor(n2) as integer)%2
go

UPDATE XX
SET C1='imp' WHERE 0<>cast(floor(n2) as integer) %2
go


You are not authorized to post a reply.

Acceptable Use Policy