Do triggers cause extra index updates? I am dealing with a db managed by a 3rd party framework and I can't change it... yet. Isn't that always the case.
Long story short they are never setting nullable fields to null. They are putting in defaults for everything. Think 0 for Int and '1900-01-01 00:00:00.000' for DateTime.
While there are millions of rows in the table, the columns are sparse and should be null most of the time.
I have created filtered indexes that have a [Where SomeField is not null].
While analyzing some query plans I noticed I was getting giant numbers back for [Estimated Number of Rows] and upon investigation I noticed my values weren't null like I thought would be.
This meant all of my indexes are bloated with default values.
I would like to create triggers that set the actual values to null if the value is a default value.
My question is, &quot;Do triggers get fired before indexes are updated? Will I be updating my index twice?&quot;
It depends on if you use an INSTEAD OF trigger or an AFTER trigger.
It sounds like you want an INSTEAD OF trigger where you can prevent the default values from being INSERTed or UPDATEd and replace them with NULLs instead.
(An AFTER trigger would effectively cause the index to update twice.)
You can read more on how to implement an INSTEAD OF trigger here.