I have a question about filtered indexes. I have a main table that I'm going to be linking to a flag table to create view. The flag indicates if the data should be published or not. Essentially the flag is 1 if published, 0 if it isn't and I'm thinking of creating a filter index to support the view, I.e. where flag = 1
The question I have is that the flag can change. Initially it will be 0 and when it is published it will become 1 and thus appear in the view and it is possible that the flag could be set back to zero in the future.
At the end of a financial year the data is static and so the partition for that year will not change, however the current financial year will change.
Do to the changing of the flag status I'm building the index on, the question I have, as I haven't used a filter before, is do I have to rebuild the index each time?
Thanks in advance.