Update clustered column in place?

[Danny]

Is there any way to update a column in a clustered index without incurring
the cost of reordering.

Example:
Create table TableX (
Col1 int,
Col2 smalldatetime,
Col3 varchar(10))
go
create clustered indext ix_test on tableX (Col2, Col1)
go
update TableX set Col2 = '2004-12-07'

-- Yes I specifically left off the criteria to update every row.

It would seem to me in this situation that the data types require the same
storage, all rows are being updated in one transaction, and there's no new
to reorder since all will be the same.

Thanks,
Danny

[Gang He [MSFT]]

Danny,

No, when a key column in an index is updated, the row has to be deleted and
re-inserted into the index, even if all rows in the index are updated to the
same value. Server has to keep the index(btree) consistent without any out
of order row at any point of time for many reasons. For example, this is
needed to for concurrent user of the index to read the index while the
update is being performed. Even if the user ask to do the update under a
table lock, it is needed to make concurrent user conducting nolock scan
work, and most importantly if the update transaction rollback in the middle
or the server crashes in the middle of the update, undo of the update
transaction requires the btree to be consistent at any point of time.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Danny" <is****@flash.n et> wrote in message
news:V6******** ***********@new ssvr33.news.pro digy.com...

Is there any way to update a column in a clustered index without incurring
the cost of reordering.

Example:
Create table TableX (
Col1 int,
Col2 smalldatetime,
Col3 varchar(10))
go
create clustered indext ix_test on tableX (Col2, Col1)
go
update TableX set Col2 = '2004-12-07'

-- Yes I specifically left off the criteria to update every row.

It would seem to me in this situation that the data types require the same
storage, all rows are being updated in one transaction, and there's no new
to reorder since all will be the same.

Thanks,
Danny

[Erland Sommarskog]

Danny (is****@flash.n et) writes:

Is there any way to update a column in a clustered index without incurring
the cost of reordering.

Example:
Create table TableX (
Col1 int,
Col2 smalldatetime,
Col3 varchar(10))
go
create clustered indext ix_test on tableX (Col2, Col1)
go
update TableX set Col2 = '2004-12-07'

-- Yes I specifically left off the criteria to update every row.

It would seem to me in this situation that the data types require the same
storage, all rows are being updated in one transaction, and there's no new
to reorder since all will be the same.

Maybe if you had updated Col1. But now you are updating the lead column:

INSERT TableX (Col1, Col2, Col3)
VALUES (1, '20040101', 'Slabank')
INSERT TableX (Col1, Col2, Col3)
VALUES (2, '20030101', 'Slabank')

When you perform the update above, the two rows will have to change places.

In practice, even if you updated Col1, I would not be surprised if there
were data moved around. Recall that in a non-unique clustered index,
SQL Server adds a four-byte uniquifier, and your update could force a
change in these. And this could lead to data being moved, although I
can't really say that I know that this will happen.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp