SQL2000 IndexRebuild confusion...

[DLu]

Hi:
I think SQL2000 does not allow on-line indexRebuild without single user status.

1. But from one server with maintenance plan, it looks like it could to it without knocking out all users connections.
2. Also, I doubt its result: did it really dropped and recreated indexes?
-------------------------------------------------------

Job 'DB_Weekly_IndexRebuild' : Step 1, 'Step 1' : Began Executing 2008-02-07 01:00:02
output
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039Copyright (C) Microsoft Corporation, 1995 - 1998Logged on to SQL Server 'ZYNXDB2' as 'NT AUTHORITY\SYSTEM'
Starting maintenance plan 'DB_Maintain_Weekly_IndexRebuild' on 2/7/2008 1:00:02 AM [3] Database CatalogAndVocabularyImport: Index Rebuild (leaving 10%% free space)... Rebuilding indexes for table 'Tbl_ABC'
------------------------------------------------------
thanks
David

[rmiao]

Sql2k can't do online reindex, it's sql2k5 enterprise edition's feature.

[DLu]

Yes, I know on-line reindex is part of SQL2005 new feature.

My puzzle is why the following output from the maintenance job exec, looks Rebuild Index works for SQL2000 without requiring single user mode?

--=========================================
Database ABC: Index Rebuild (leaving 10%% free space)... Rebuilding indexes for table 'Tbl_ABC'

I want to know this "Index Rebuild (leaving 10%% free space)... " in SQL2000 just a 'fake' or not ?

thanks
David

[rmiao]

Rebuild index doesn't need db in single user mode in any version of sql, it's different from online indexing.

[DLu]

Hi Ramio:

dbcc dbreindex (in SQL2000)
does drop/re-create index,
thanks for your reminder.

Just want to clarify something:
1. what is the difference with index reorg?
2. I know it is different from indexdefrag which is only at leaf level. then, what is the difference from on-line index which is the sql2005 new feature?

thanks
-D

[rmiao]

1. Index reorg just moves data around in index pages with same extent, and it doesn't update stats. Not for highly fraged table.
2. Online index is build index without locking table in whole duration.