If you ever needed more evidence that Microsoft is a big company and often any two given people who should talk to each other don't, here you go. DBCC DBREINDEX and DBCC INDEXDEFRAG do very similar jobs, and should take an identical set of input parameters. DBREINDEX takes at a minimum a table name, and it will reindex the table. INDEXDEFRAG, however, takes a minimum of 3 parameters: Database name, Table Name and Index name (specified as DBCC INDEXDEFRAG ('MyDB', 'MyTable', 'MyIndex')). It is neccessary to pass all three. Why? I've got to write a cursor on sysindexes AND sysobjects to defrag indexes. Ick. Why not make both commands the same? Do we not have enough stupid arcane knowledge to keep in our poor little heads without this kind of…uh…(graz wants a G rating)…poo?

In case you don't know the difference, DBREINDEX completely reindexes each table, and INDEXDEFRAG just defragments the indexes. The fundamental practical difference between the two is that DBREINDEX on a table witha clustered index will acquire an exclusive table lock on the table for the duration, and INDEXDEFRAG only acquires a shared lock (enough of a lock to keep the table from being dropped) for it's duration. INDEXDEFRAG is supposedly less effective, but if you're dealing with tables that actually get changed (inserts, updates, deletes) it's probably just as good over some period of time/number of transactions.

rs.