February 7, 2008 at 2:33 pm
Hi,
we are using sql server 2005 enterprise edition. in production database there sometables are heap and they are fragmented and also they have non-clustered indexes.
i want to defragment heap tables by creating clusterindex and then drop cluster index as following script
create Clustered index Idx_temp_heap on abc(column1)
with (SORT_IN_TEMPDB = ON, Online = On,fillfactor=100) On IDX
Go
drop index tbl_abc.Idx_temp_heap
Go
my question is, how non-clusterindex rebuilds. is it online or offile(since it is automatic)
thanks
February 7, 2008 at 2:40 pm
You have to specify if you want to do it online ... which can only be done in enterprise edition.
Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:
- Disabled indexes
- XML indexes
- Indexes on local temp tables
- Partitioned indexes
- Clustered indexes if the underlying table contains LOB data types
- Nonclustered indexes that are defined with LOB data type columns
February 7, 2008 at 3:30 pm
adam thanks for you reply.
if you check the script i pasted in my previous post i menstioned online=on.
but my question is non-cluster indexes recreates automatically when cluster index creates or drops. so is non-cluster index online or off-line in this situation?
February 7, 2008 at 5:09 pm
Correct me if i am wrong. Let me know why do u want to create an index and then immediately. i hope u have a non clustered index. you need to create a clustered index which can be done and then drop the non clustered index from the table. if my understanding is wrong let me know.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 7, 2008 at 5:26 pm
my heap table has fragmented to 70%. i want to defragment. to defragment heaps i need to create and drop the cluster index. i need my existing non-cluster indexes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply