Current commands for index management, as supported in SQL 2000 and discussed previously, are still supported in SQL 2005. However, they will be phased out & are unlikely to be supported in future releases.
With this in mind it is probably worth mentioning a few of the successors:
DBCC SHOWCONTIG - this has been replaced by the sys.dm_db_index_physical_stats ( ) function.
e.g.
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'Database Name'), OBJECT_ID(N'Table Name'), NULL, NULL , 'LIMITED')
DBCC DBREINDEX - this is now dealt with using the ALTER INDEX statement.
e.g.
ALTER INDEX ALL ON Table Name REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF)
As a final point to mention, there are quite a few parameter options and switches that go along with these new methods. It's therefore advisable to do some research into what is actually available so that you can refine your use of these methods accordingly. SQL Server Books Online is always a good one but you'll find generally find detailed information anywhere on the internet.
Oliver Cox
Comments