If you’re getting SQL Server error Msg 129 that reads “Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100“, it appears that you’re trying to set the fillfactor of an index to zero.

While it’s true that zero is the default setting, we can’t actually explicitly set the fillfactor to zero when creating or rebuilding an index.

But all is not lost. Below are two solutions to this problem.

Example of Error

First, let’s take a look at code that produces the error:

ALTER INDEX IX_CustomerLastName
ON Customers
REBUILD WITH (FILLFACTOR = 0);

Output:

Msg 129, Level 15, State 1, Line 3
Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100.

In this case I tried to set the fillfactor of an index called IX_CustomerLastName to zero. I got an error telling me that the fillfactor must be between 1 and 100, even though I know that the default value is zero.

Solution 1: Set Fillfactor to 100

When it comes to fillfactor in SQL Server, 0 is the same as 100. Having a fillfactor of 0 will have the same effect as having it at 100. When fillfactor is set to 0 or 100, the leaf pages are filled to capacity.

So we could easily fix the error by doing the following:

ALTER INDEX IX_CustomerLastName
ON Customers
REBUILD WITH (FILLFACTOR = 100);

Output:

Commands completed successfully.

This time it ran successfully.

All I did was change 0 to 100. The rest of the code is the same.

Solution 2: Re-Create the Index, using WITH (DROP_EXISTING=ON)

If you really want your fillfactor set to 0, then this option is for you.

Creating an index using WITH (DROP_EXISTING=ON) will re-create the index and set its fillfactor to the default value. Unless your system has had its fill factor server configuration option changed, the default value will be 0.

Therefore, you could do the following:

CREATE NONCLUSTERED INDEX IX_CustomerLastName
ON Customers (LastName)
WITH (DROP_EXISTING=ON);

Output:

Commands completed successfully.

After running this, the index has been recreated with the default fillfactor.

You can use the following query to check its fillfactor:

SELECT
    name,
    type_desc,
    fill_factor
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = 'Customers';

Example output:

name                            type_desc     fill_factor
------------------------------  ------------  -----------
PK__Customer__A4AE64B805C24F66  CLUSTERED     0          
IX_CustomerLastName             NONCLUSTERED  0          

We can see that the fill_factor column is zero.

Not Zero?

The value used in the above example depends on the value specified in the fill factor configuration option. SQL Server sets this at 0 by default, but we do have the option of changing it. See How to Change SQL Server’s Default Fillfactor to view and change this value.