Danny (is****@flash.net) 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****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp