November 21, 2002 at 4:04 pm
If there is a clustered index on a primary key(type int) on a table, that table has a varchar or nvarchar fields, will the table be reindexed when the varchar fields are updated?
here is the scenario:
Table1
Field1 int PK Clustered
Field2 varchar(50) not indexed
If field 2 is updated will the table1 be reindexed?
Thanks in advance.
Tom.
November 21, 2002 at 4:33 pm
no. although I can see how someone might think it would.
They are thinking that the clustered index, being the data, would naturally have to be re-arranged because its the data. The second column making up part of the data. This is actually a false idea. The data is arranged by clustered index, namely the column(s) in the index. Past that, its a heap structure. You can prove this easily enough, as well.
Using the structure you posted, create a couple of scripts.
Insert a set of records into the table. say 20 to 30.
Select the records with no sort order.
You will get the records by order of the clustered index.
update 5 or ten records 2nd column.
select the records with no sort order.
You will still get the records by order of the clustered index.
run a dbcc reindex statement on the table
select the records with no sort order.
You will still get the records by order of the clustered index.
Now, taking it a little further, let's say there was no primary key, and the clustered index was not unique. It would still work exactly the same, with the records always being returned by clustered index values, but not sorted on the second column. Instead, by order of insertion.
It could have its statistics updated, however.
Edited by - Scorpion_66 on 11/21/2002 4:35:56 PM
November 21, 2002 at 4:44 pm
Thanks Scorpion_66!
I did not think it would be updated because you are not updating the data that is being indexed.
But the updating of statistics would have to manual unless you have automatically update stats option selected on that table, right?
November 21, 2002 at 4:57 pm
Yep.
I figured you were fighting developers. I've seen that idea come from some before. Had to prove it to them.....
Edited by - Scorpion_66 on 11/21/2002 4:59:01 PM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply