Rebuild index with "Alter Index Rebuild" or "DBCC DBREINDEX"

You can rebuild all index for a table with "Alter Index Rebuild" and "DBCC DBREINDEX" .

First, in BOL, for "DBCC DBREINDEX" , it mentions
This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.

Looks like we'd better replace all "DBCC DBREINDEX" script to "Alter Index Rebuild", however there is a little bit difference between their behavior, let's look a sample:


1. Turn on the "auto  create statistics" option for the testing db
ALTER DATABASE [test] SET AUTO_CREATE_STATISTICS ON WITH NO_WAIT
GO

2. Create sample table
create table abc(t1 int, t2 int)
go
declare @i int
set @i = 0
while @i < 20000
begin
     insert into abc(t1, t2) values (@i, @i)
     set @i = @i + 1 
end
create index abcindx on abc(t1)
go


3. Run the select command below, it will create a statistics automatically on column t2
select count(*) from abc where t2> 500 and t2<600

so we will get 2 statistics on the abc table

• abcindx is created by index abcindx
• _WA_Sys_00000002_025D5595 is created by SQL Server automatically to optimize the query performance

4. Check the statistics status
dbcc show_statistics ( abc, _WA_Sys_00000002_025D5595)
go
dbcc show_statistics ( abc, abcindx)
go

 5. Update the table
update abc set t1=12000 where t1>12000
update abc set t2=12006 where t2>11000
go
6.  Rebuild index with DBCC DBREINDEX
DBCC DBREINDEX (abc)

7.  Check the Statistics again, both 2 statistics are updated

 8. Drop the table abc, and redo the step 1 to 5

9. Rebuild index with "ALTER INDEX ALL REBUILD"
ALTER INDEX ALL ON dbo.abc rebuild

10. Check the Statistics again, only statistics abcindx is updated, the auto statistics is not updated yet.

 11. In order to update the auto statistics, you need to run
UPDATE STATISTICS abc
12. Drop the table, and redo step 1 and 2

13. Manually create statistics abcstc on column t2
CREATE STATISTICS abcstc on abc(t2)

14. Redo Step 5, update the table

16. Rebuild index with "ALTER INDEX ALL REBUILD"
ALTER INDEX ALL ON dbo.abc rebuild

17. Check the statistics, you will find the statistics abcstc still hasn't been updated.

So DBCC DBREINDEX not only update the indexes, but also update the statistics(manual or auto created), however, ALTER INDEX  only update the statistics which created by index. if you run ALTER INDEX, you'd better run UPDATE STATISTICS as well.