Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
ref: sqlskills.com/BLOGS/KIMBERLY/post/… –  Eric Higgins Apr 3 '12 at 18:21
add comment

1 Answer

up vote 1 down vote accepted

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)

share|improve this answer
    
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 '12 at 19:49
    
Also, went into designer, removed the pk, saved, then readded the pk, same thing. –  DForck42 Apr 3 '12 at 19:51
    
That's how I thought it was supposed to work, but also seeing some oddities here. –  Eric Higgins Apr 3 '12 at 20:13
    
@DForck42 I can't recreate that behavior. –  Thomas Stringer Apr 3 '12 at 20:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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