How can I set the index fill factor back to server default?

Question

I have some indexes that I was playing with on my dev server. I set the fill factor of these indexes using the following command:

alter index all on dbo.Table
rebuild with (fillfactor=80)

That sets and rebuilds all of the index on dbo.Table. Now what I want is to set the fillfactor back to the default of the server. IE, at the server level all indexes are set to have a fillfactor of 95. I want the indexes to use the server default again, not just have the fill factor set to 95.

The reason I want to do this is so that, if I script this index and move it to a new server, it will use the default fill factor of that server rather than the specified 95.

Answer 1

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)