sp_BlitzIndex: add warning for nonclustered indexes on temporal table history tables that will break merge statements

[ReeceGoding]

Is your feature request related to a problem? Please describe.
There are some SQL Server bugs that you can only trip if you have a specific indexing situation. For example, this article lists a few MERGE bugs and some of them require specific indexing situations. The clearest such example is the eighth issue. Namely, that MERGEs can fail against a temporal table if its history table has a non-clustered index.

Describe the solution you'd like
Add a new high-priority category to the mode 0 and 4 outputs (something like "bug-prone indexing") and include this specific example in it.

Describe alternatives you've considered
Maybe just add it to the generic notice that you have temporal tables?

Are you ready to build the code for the feature?
I'm happy to do the specific example that I've mentioned, i.e. the eighth issue in the link. I'm too pressed for time to go seek out other index-related SQL Server bugs, confirm what versions they exist in, and then implement extra checks for them. Besides, I bet that Brent already knows of a bunch.

[BrentOzar]

We actually do have warnings like that - in priority 80, for example, we have a warning about filtered index columns not being in the index definition itself, and in priority 100 we have a warning about computed columns referencing user-defined functions, which causes single-threaded processing on the table.

You can just add a new warning as you code this - no need for a new category. If you'd like to code more, you're welcome to, but I'm pretty tight on time these days and I rarely go out and proactively add new warnings. I focus more on maintaining the overall code base, getting people to use it, etc. Only so many hours in the day! The beauty of open source is that if a new check is important to someone, they can code it.

[BrentOzar]

@ReeceGoding I know you said you're pressed for time - did you want to add the code for this example? If not, no worries at all, just didn't wanna leave the issue open if you're not going to code it anytime soon.

[ReeceGoding]

@BrentOzar I'll give it a go today. I imagine it can't be hard.

[BrentOzar]

Thanks for the pull request! Looks good, merging into the dev branch, will be in the next release with credit to you in the release notes.