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.


1 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

create index IX_YourIndex
on YourTable(YourKeyColumn)

To verify this:

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)

  • Weird. I right-clicked - script- drop and recreated the index, removed the fillfactor line and executed, and it's now specifying 95 for the fillfactor (same as the server).
    – DForck42
    Apr 3, 2012 at 19:49
  • Also, went into designer, removed the pk, saved, then readded the pk, same thing.
    – DForck42
    Apr 3, 2012 at 19:51
  • That's how I thought it was supposed to work, but also seeing some oddities here. Apr 3, 2012 at 20:13
  • @DForck42 I can't recreate that behavior. Apr 3, 2012 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.