Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dba.learner
Starting Member
2 Posts |
 Posted - 2007-08-26 : 11:38:03
|
| Hi ExpertsI want know what happens, when i give a dbcc dbreindex command on a database what happens. 1. Does it drops all index and recreates?2. Incase if i give a dbreindex on Clustered Index, Will it drop all Non Clustered Index?3. How much time does it takes to reindex a Clustered Index?- DBA LearnerRegardsDba.Learner |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-26 : 12:01:37
|
| 1. yes2. yes3. depends on the index size4. all statistics are also updated_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-04 : 16:13:20
|
| this gave me a scare... does it recreate the non-clustered index as well?TIA--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 16:20:49
|
| "does it recreate the non-clustered index as well?"Yes, except that I don't think it automatically reindexes the non-clustered indexes. They use the Keys of the Clustered Index, instead of a pointer to the record, so there is no need to recreate them, is there?(Obviously reindexing ALL the indexes is A Good Thing, but I don't think that is triggered solely by a Reindex of the Clustered Index, is it? You can specify to DBCC REINDEX that ALL the indexes on a table should be rebuilt though)Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-04 : 16:23:53
|
thanks Kristen, I was just a bit worried and almost had a heart attack there (almost ran back to the office)  ... no matter how many times, testing is still not enough sometimes--------------------keeping it simple... |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-04 : 23:15:48
|
| You can rebuild all indexes or specific one with 'dbcc dbreindex'. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-05 : 04:59:31
|
dbcc drops and then rebuilds all indexes.reindexing differs based on the type of your CI:Sql Server 2000: - Reindex a unique CI: Since you don't change the cluster key, no NCI's are rebuilt - Reindex a non-unique CI: since the cluster key has a uniquefier, this means that we're changing the cluster key, so NCI's will be rebuilt.SQL Server 2005 - Reindex a unique CI: Same as Sql Server 2000 - Reindex a non-unique CI: A new thing here is that Sql server reuses the uniquifier so NCI's are NOT rebuilt _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
|
|
|
|
|
DBCC DBReindex