March 10, 2006 at 10:37 am
Someone suggested to me that the CREATE CLUSTERED INDEX WITH DROP_EXISTING command didn't actually work the way it was supposed to, and that it did actually drop/recreate any existing non-clustered indexes at the same time (which is contrary to the purpose of this command).
I wasn't convinced, and so tried some tests. From what I can tell from looking in sysindexes before and after the dropping/recreating of a clustered index, the page number of the first page of any non-clustered indexes changes, which is what I'd expect (the NC indexes have to be rebuilt to remove and then re-add the clustered key).
However, the same thing seems to happen even when I use the WITH DROP_EXISTING clause, which I think should leave the non-clustered indexes completely untouched. The fact that the page number has changed suggests that the index has been modified in some way.
If I repeat this in SQLServer2005, the first page number of the NC indexes does NOT change following a DROP_EXISTING command, which does imply that the command doesn't work exactly as stated in SQLServer2000.
Am I missing something, or can anyone confirm what I'm seeing?
Here is the test script I ran and the resulting output in SQL2000:
create table temptab(col1 int, col2 int, col3 int)
insert temptab values (1,2,3)
create clustered index IX_CI on temptab(col1)
select * from sysindexes where object_name(id) = 'temptab'
create index IX_NCI_1 on temptab (col2)
create index IX_NCI_2 on temptab (col3)
selectid, status, first, indid, root, minlen, reserved, used
from sysindexes where object_name(id) = 'temptab'
drop index temptab.IX_CI
select id, status, first, indid, root, minlen, reserved, used
from sysindexes where object_name(id) = 'temptab'
create clustered index IX_CI on temptab(col1)
select id, status, first, indid, root, minlen, reserved, used
from sysindexes where object_name(id) = 'temptab'
create clustered index IX_CI on temptab(col1) with DROP_EXISTING
select id, status, first, indid, root, minlen, reserved, used
from sysindexes where object_name(id) = 'temptab'
RESULTS from sysindexes: (Apologies for the formatting, but I can't use rich text on here from my PC for some reason)
(1 row(s) affected)
(1 row(s) affected)
== Just Clustered Index ==
id status first indid root minlen reserved used
----------- ----------- -------------- ------ -------------- ------ ----------- -----------
514100872 2113552 0xDE1200000100 1 0xE01200000100 16 3 3
(1 row(s) affected)
== All Indexes In Place ==
id status first indid root minlen reserved used
----------- ----------- -------------- ------ -------------- ------ ----------- -----------
514100872 2113552 0xDE1200000100 1 0xE01200000100 16 7 7
514100872 2097152 0xDC1200000100 2 0xDC1200000100 9 2 2
514100872 2097152 0xE11200000100 3 0xE11200000100 9 2 2
(3 row(s) affected)
== DROP Clustered Index ==
id status first indid root minlen reserved used
----------- ----------- -------------- ------ -------------- ------ ----------- -----------
514100872 0 0xDE1200000100 0 0xDE1200000100 16 6 6
514100872 2097152 0xE41200000100 2 0xE41200000100 13 2 2
514100872 2097152 0xE61200000100 3 0xE61200000100 13 2 2
(3 row(s) affected)
== RE-CREATE Clustered Index ==
id status first indid root minlen reserved used
----------- ----------- -------------- ------ -------------- ------ ----------- -----------
514100872 2113552 0xDC1200000100 1 0xE01200000100 16 7 7
514100872 2097152 0xE11200000100 2 0xE11200000100 9 2 2
514100872 2097152 0xE81200000100 3 0xE81200000100 9 2 2
(3 row(s) affected)
== CREATE Clustered Index WITH DROP_EXISTING
id status first indid root minlen reserved used
----------- ----------- -------------- ------ -------------- ------ ----------- -----------
514100872 2146320 0xDE1200000100 1 0xE41200000100 16 7 7
514100872 2097152 0xE51200000100 2 0xE51200000100 9 2 2
514100872 2097152 0xE71200000100 3 0xE71200000100 9 2 2
(3 row(s) affected)
March 13, 2006 at 4:08 am
Some time ago there was a discussion on this site about effects of clustered index rebuild on non-clustered indexes. If I remember correctly, the bottom line was that if the clustered index is unique, the nonclustered indexes aren't rebuilt. If the ci is not unique, the nonclustered indexes are rebuilt because the value given internally to a ci by sql server to ensure its uniqueness is regenerated so the pointers in nonclustered indexes are no longer valid. I'm not sure but it could apply to your case as your ci is not unique.
March 14, 2006 at 11:14 am
Sounds plausible Martin. I'll set up a test to see if this is the case. All the same, the test I ran in SQLServer2005 (which appeared to leave the NC indexes untouched), used exactly the same script as the test on SQLServer2000.
BTW - I tried running Profiler to see if I could catch the NC drop/create happening, but I couldn't, even without the DROP_EXISTING option - all I saw was the Clustered index commands. So, I'm either capturing the wrong events or it isn't possible to record the implicit rebuilds.
Thanks for your response. Does anyone else know anything that might back up Martin's theory?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply