Can You Stop People From Creating Indexes With Low Fill Factors?

Yeah nah

So like, presented without much comment, this server level trigger will, in my limited testing, “work”.

Just make sure you understand something: this doesn’t stop the index from being created, it only rolls creation back afterwards.

If someone creates a gigantic index on an equally gigantic table, you’re in for a hell of a ride. I’d probably only deploy this on local dev boxes, and only if I really needed to prove a point.


It’ll work for create or alter index commands, i.e.

Pink Blood

Is it perfect? Probably not, but I threw it together quickly as a POC.

For instance, my first stab broke when fill factor wasn’t specified in the command.

My second stab broke when I changed the spacing around the “=”.

Let me know in the comments if you can get around it or break it, other than by changing server settings — I can’t go that far here.

Thanks for reading!

5 thoughts on “Can You Stop People From Creating Indexes With Low Fill Factors?”

      1. 10/10 patch. My next test was going to be playing around with RESUMABLE = ON and MAX_DURATION to see if it could get around the trigger, but I don’t have 2019 installed yet… So I’m declaring the updated trigger invincible.

Leave a Reply

Your email address will not be published. Required fields are marked *