0

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, "Do triggers get fired before indexes are updated? Will I be updating my index twice?"

Improve this question
7
  • 4
    Wouldn't be easier to change conditions in your filtered indexes?
    – NikitaSerbskiy
    Commented Dec 5, 2020 at 19:27
  • How do you propose to differentiate between a value that was supplied as a default vs. a value that was supplied by the application / user that just happens to be the same? Maybe the application is written to work correctly with these specific default values.
    – SMor
    Commented Dec 6, 2020 at 13:45
  • @SMor You are correct in that there would be no way to differentiate that at the database level. You are also correct in that the application is written to handle defaults if it sees them. It actually treats them as null if they were null. So the problem here is that they exist and take up space.
    – Joshua Grippo
    Commented Dec 7, 2020 at 14:45
  • @NikitaSerbskiy Changing the filters is something we have thought through, but the queries that get generated specifically have [and is not null] so that the filtered indexes get used. If we look at a date, then default would be 1900-01-01 and the index filter would have to add a clause [and DateValue > '1900-01-01']. Since filtered indexes are already fickle beasts, I would like to keep them as short and sweet as possible.
    – Joshua Grippo
    Commented Dec 7, 2020 at 14:52
  • but what if somewhere in the app you already have query with WHERE condition which uses these values (SELECT TOP 1 Id FROM table WHERE CreateDate = '1900-01-01')? Adding this trigger you may broke the application.
    – NikitaSerbskiy
    Commented Dec 7, 2020 at 18:48

1 Answer 1

Reset to default
3

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.