I will give an example. Once upon a time, one index was created in the table, yesterday I ran into a problem query, without noticing this index created the same, and what do you think? Yes, he helped me. And yet, I have a script for re-indexing every day.

One person told me that rebuilding clustered indexes in MS SQL Server 2000 is possible only by rebuilding them. My index is not clustered.

Reindex script:

CREATE PROCEDURE dbo.spDBREINDEX /* Created: 12/18/2002 Created By: AJ Ahrens - SQL DBA AT&T Labs x3375 Purpose(s): Dynamically reindex table(s) */ AS DECLARE @TableName VARCHAR(255) DECLARE @IndexName VARCHAR(255) DECLARE @OrigFillFactor INTEGER DECLARE @OuterCounter INTEGER DECLARE @InnerCounter INTEGER DECLARE @OuterLoop INTEGER DECLARE @InnerLoop INTEGER DECLARE @ClusteredYN INTEGER DECLARE @strSQL NVARCHAR(2000) SELECT SO.[name] tblName, MAX(SI.OrigFillFactor) OrigFillFactor, '01/01/1900 12:00:00AM' ReIndexedDtTm, SI.[name] IndexName, CASE WHEN SI.indid = 1 THEN 1 ELSE 0 END ClusteredYN INTO #IndexedTables FROM sysindexes SI LEFT JOIN sysobjects SO ON SI.[id] = SO.[id] WHERE xtype = 'U' AND SI.indid <> 0 AND SI.indid <> 255 GROUP BY SO.[name], SI.[name], SI.indid SET @OuterCounter = 1 SET @OuterLoop = (SELECT COUNT(DISTINCT tblName) FROM #IndexedTables) WHILE @OuterCounter <= @OuterLoop BEGIN SET @TableName = (SELECT TOP 1 tblName FROM #IndexedTables WHERE ReIndexedDtTm = '01/01/1900 12:00:00AM') SET @InnerCounter = 1 SET @InnerLoop = (SELECT COUNT(*) FROM #IndexedTables WHERE tblName = @TableName) WHILE @InnerCounter <= @InnerLoop BEGIN SET @IndexName = (SELECT TOP 1 IndexName FROM #IndexedTables WHERE ClusteredYN = 1 AND tblName = @TableName AND ReIndexedDtTm = '01/01/1900 12:00:00AM') SET @ClusteredYN = 1 IF RTRIM(@IndexName) IS NULL BEGIN SET @IndexName = (SELECT TOP 1 IndexName FROM #IndexedTables WHERE ClusteredYN = 0 AND tblName = @TableName AND ReIndexedDtTm = '01/01/1900 12:00:00AM') SET @ClusteredYN = 0 END SET @OrigFillFactor = (SELECT OrigFillFactor FROM #IndexedTables WHERE tblName = @TableName AND ClusteredYN = @ClusteredYN AND IndexName = @IndexName) SET @strSQL = 'DBCC DBREINDEX ' + '(' + CHAR(39) + @TableName + CHAR(39) + CHAR(44) + SPACE(1) + CHAR(39) + @IndexName + CHAR(39) + CHAR(44) + SPACE(1) + CONVERT(VARCHAR(3), RTRIM(@OrigFillFactor)) + ')' PRINT @strSQL EXEC sp_executesql @strSQL UPDATE #IndexedTables SET ReIndexedDtTm = GETDATE() WHERE tblName = @TableName AND ClusteredYN = @ClusteredYN AND IndexName = @IndexName SET @InnerCounter = @InnerCounter + 1 END SET @OuterCounter = @OuterCounter + 1 END
  • five
    I'm already curious, who in 2017 is adding a question about SQL Server released back in 2002 (removed from support in 2008 and from extended support in 2013)? - AK ♦
  • @NickProskuryakov That is, in your opinion, re-indexing does not include rebuilding indexes? - aaa
  • Find online 2005 and do not suffer. It works almost everywhere (except for Windows 2016 there) and is rather stable. - nick_n_a
  • @NickProskuryakov Have you carefully read the question? Firstly, the question is not this, and secondly, I wrote in the question that I created the same index and it helped. - aaa
  • @aaa I apologize to read "did not help", well, I thought that you still have the old index in the plan. - Nick Proskuryakov

1 answer 1

DBCC DBREINDEX is an outdated instruction that rebuilds the index, recreating it again (similar to ALTER INDEX ... REBUILD)

DBCC INDEXDEFAG is an outdated statement (similar to ALTER INDEX ... REORGANIZE)

ALTER INDEX ... REORGANIZE / REBUILD appeared in 2005 version

So in 2000m it was also possible to either rebuild or defragment (reorganize)