Sql-server – the difference between ALTER INDEX and DBCC DBREINDEX

Through testing, it seems as though to get back to the default fillfactor on an index you need to drop and recreate it. When you do an ALTER INDEX ... REBUILD, any unspecified parameters will remain the same in the index's metadata unless otherwise explicitly changed in the ALTER INDEX command.

drop index IX_YourIndex
on YourTable
go

create index IX_YourIndex
on YourTable(YourKeyColumn)
go

To verify this:

select
    name,
    fill_factor
from testdb.sys.indexes
where name = 'IX_YourIndex'

fill_factor should be set to 0 to denote that it is using the instance's default value. (Note: 0 is for the default value, even if the instance configured fill factor is set to 95)

They satisfy different queries.

The first one will be useful for queries that seek on ColA = X and return ColB (without the INCLUDE this would require a lookup back to the base table)

The second one will be useful for queries that seek on ColB = Y

Both of them can be used for queries like ColA = X AND ColB = Y but the second one will do this more efficiently as the first index would require a seek on ColA then the residual predicate be evaluated against all matching index rows.