It used to be fact that the leaf page of a non-clustered
index pointed to the row for the page, because it pointed to the file, page and
row if the file or page of the row changed the nonclustered index had to
be updated.
Now however the non-clustered index has the Unique Clustering Key
value for the main data row. I specifically say Unique because you can
create a non unique clustered index. In this case SQL generates an additional
bit of data to make a unique key for each row in the table. (One reason why
clustering on a non-unique column is bad).
What this means is that the data row can move about all over the place with
out affecting the non-clustered index.
So what happens when you, create a clustered index,
reindex a clustered index, recreate (using with drop existing) and drop a
clustered index. This question came up in a recent presentation I was sure of
the first and last, but not of the middle 2 so I did a repro and was pleasantly
suprised.
The first and last both result in the non-clustered index being changed, the
middle two however don't.
The example below puts the index on a different
filegroup and looks at the IO for each filegroup after the operation. File 3
relates to the nonclustered index and you can see that the index creation causes
IO, so does the creation of the clustered index (changing the row pointers to key values). But the reindex and the create with
drop existing doesn't.
So in summary if you need to rebuild a clustered don't drop it and recreate,
instead use alter index, dbreindex, or with drop_existing.
if not
exists (select *
from sysfilegroups
where groupname
= 'indexes')
begin
alter
database blogcode
add filegroup
indexes
alter
database blogcode
add file (name=indexes, filename='c:\indexes.mdf' , size=10)
to filegroup
indexes
end
go
declare @dbid int
drop table
simon
create table
simon(
clusteredCol
int identity(1,1)
,col2 uniqueidentifier )
insert into
simon(col2)
select top
20000 newId()
from syscolumns a
cross join
syscolumns b
select identity(int,1,1) id,fileId,cast('start' as
varchar(30)) as action,numberreads,numberwrites into #stats
from ::fn_virtualfilestats(db_id(),null)
create nonclustered index
IX_simon_col2 on
simon(Col2)
on indexes
insert into
#stats (fileId,action,numberreads,numberwrites)
select fileId,'create nonclustered',numberreads,numberwrites from ::fn_virtualfilestats(db_id(),null)
create clustered index
IXC_simon_clusteredCol on simon(clusteredCol)
insert into
#stats (fileId,action,numberreads,numberwrites)
select fileId,'create clustered',numberreads,numberwrites from ::fn_virtualfilestats(db_id(),null)
create clustered index
IXC_simon_clusteredCol on simon(clusteredCol) with
drop_existing
insert into
#stats (fileId,action,numberreads,numberwrites)
select fileId,'create clustered with drop',numberreads,numberwrites from ::fn_virtualfilestats(db_id(),null)
dbcc dbreindex(simon,IXC_simon_clusteredCol )
insert into
#stats (fileId,action,numberreads,numberwrites)
select fileId,'dbreindex',numberreads,numberwrites from ::fn_virtualfilestats(db_id(),null)
drop index
simon.IXC_simon_clusteredCol
insert into
#stats (fileId,action,numberreads,numberwrites)
select fileId,'drop clustered',numberreads,numberwrites from ::fn_virtualfilestats(db_id(),null)
select id,
action, fileId, numberreads,
numberwrites
from #stats
order by
fileid,
id
go
drop table
#stats