Why, I don't want it.

Question

Why, I don't want it.

Oleg D

SSC Veteran

Points: 279
More actions
April 26, 2002 at 6:39 am

#78306

Hello!
Can somebody explain this behavior of SQL, please?
---------------------------------------------------------------------
Example:
CREATE TABLE A
(
ONE INT,
TWO CHAR(15),
CONSTRAINT PK PRIMARY KEY CLUSTERED (ONE )
)
GO
CREATE NONCLUSTERED INDEX IDX001 ON A( TWO );
GO
INSERT INTO A VALUES(1, 'HELLO')
GO
SET SHOWPLAN_TEXT ON
GO
UPDATE A
SET TWO = 'good-bye'
WHERE ONE =1
Result:
StmtText
|--Clustered Index Update(OBJECT:([od_Test].[dbo].[A].[PK]), SET:([A].[TWO]=[Expr1004]), DEFINE:([Expr1004]=Convert([@1])), WHERE:([A].[ONE]=[@2]))
---------------------------------------------------------------------
So the question is: why update of clustered index?
There are no columns from clustered index in update stmt.
It seems like if we had any secondary indexes along with clustered
any changes of columns from those nonclustered indexes would cause updating
of clustered index.
Please tell me why...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Consider this, how does a clustered index work in regards to the tableand what happens if that index is no longer clustered?

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Oleg D SSC Veteran Points: 279 More actions · Answer 2

Here is the plan for nonclustered:

|--Table Update(OBJECT:([od_Test].[dbo].[A]), SET:([A].[TWO]=[Expr1004]))

|--Top(1)

|--Compute Scalar(DEFINE:([Expr1004]=Convert([@1])))

|--Index Seek(OBJECT:([od_Test].[dbo].[A].[PK]), SEEK:([A].[ONE]=[@2]) ORDERED)

Is it the way how it should be?

I mean if I have a lot of updates, should I get rid of clustered indexes?

Antares686 SSC Guru Points: 125444 More actions · Answer 3

No keep the clustered index. I was trying to get at that clustered indexes physically order the table, so this would make sense.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Oleg D SSC Veteran Points: 279 More actions · Answer 4

Ok then,

could you tell me what is better to have

exculsive KEY lock or exlusive RID lock?

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Depends on you query. If the item is a single update like this then a RID shoudl show but a KEY would not have a negative impact since it still is only locking a range of 1 row. If you however do an update on a table and where the PK were btween 1 and 6 you should get a KEY locking the range, a RID may have a slight negative performance impact but I don't think I have ever seen that happen in those cases when I checked in testing (but I don't always check).

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)