Wednesday, January 27, 2021

Buggish: Microsoft SMO Index Fillfactor Defaults Unwanted Value Instead of 0, null, or nonexistent.

I am trying to copy objects from one environment to another. I do not care whether the developer created the objects correctly or not. I only care that I create an almost exact copy of the object to another database.

One of the features of my tool is to automatically pull the index from a lower environment and creating it in the new environment. For some reason, this automatically fills in the fillfactor to the default value of the target database which I do not want. One, this is not how the developer designed his index. Two, when promote that change to the next environment it will copy that defaulted fillfactor which may be different on the target database. 

So now I have no way of telling whether the developer intentionally added a fillfactor (which from what I read online is intentional) or didn't set a value but was added because Microsoft SMO doesn't set the fillfactor to the value that I want.


I have set fillfactor to 0 and to 100. 0 will always set fillfactor to the target database default fillfactor. 100 will be 100, obviously. I cannot set the value null because it is byte type. I tried to leave it unset, but will still set to default.

This is pretty dumb because you can execute a script to not have fillfactor. And once you have fillfactor, you cannot even go into the database to set it to 0 (even if the default db server value is the same as the fillfactor).


Simply, I just want the index to be the same as the source database. If the source database does not specify a fillfactor, I want the target database to also not specify a fillfactor.