CODEWITHSUNDEEP
sql-server
bornfromanegg
bornfromanegg

Reputation: 2928

How do I set Fillfactor to 0 in SQL Server?

I have a set of indexes in my databases, and I would like to set the Fill Factor to 0. Some of these are currently set to 100. I know that as far as SQL Server is concerned, this is the same, but we have a piece of software which is comparing two databases with the same schema and it is detecting a difference between them if the Fill Factor of an index is 0 in one database and 100 in the other. This is a problem.

Whilst we go about updating our software to be a bit cleverer about this, I would like to be able to set the Fill Factor to 0 on various indexes. SQL Server won't let you specify a Fill Factor of 0 (it must be 1..100), so the only way I can think of doing this is to DROP the index and recreate it (with the server default set to 0).

But is there another (and preferably quicker) way?

Upvotes: 0

Views: 896

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

If the default instance fill factor (%) configuration value is set to zero, you can omit FILLBACTOR and recreate indexes with CREATE INDEX...WITH (DROP_EXISTING=ON). This will be a bit more efficient than DROP/CREATE because no sort will be needed to recreate the index and, the case of the clustered index, non-clustered indexes won't need to be rebuilt twice.

Upvotes: 0

Related Questions