Adding T-SQL query option functionality

[ph-mosadex]

I am looking for the ability to add the OPTION(RECOMPILE) to queries. I know it is possible to do this using an interceptor, but due to the way NHibernate is configured in my organisation this requires some ugly code to accomplish.

I have found some earlier discussion around this topic (#918) and a branch by @IQS-Leopold. This is all from 2017.

Is there any chance that this will get implemented in the foreseeable future? nhibernate's parameterized queries prevent the usage of filtered indexes unless the recompile query option is used so it would be great to have the ability to add query options build into nhibernate.

[fredericDelaporte]

Worth checking: these kind of troubles may be gone with SQL Server 2022. They announce having worked on solving performance troubles bound to "parameter sensitive" queries (among other highlights).

[gliljas]

1. Yes, it would be nice to be able to provide query and table hints. It's worth considering.
2. Could you describe why the interceptor version isn't an option?
3. Could you describe a query where you would want filtered indexes to be used? There may be ways to accomplish it, in certain scenarios.

[SLeopold1986]

Hello aim iqs-leopold,
we are using a customized nhibernate version with the support for query hints for years now. Also version 5.3. I will do a pull request for this and you could decide to use it or not to use it. It is also easier for us if this is provided directly.

[SLeopold1986]

Okay, i made a pull request.
#3008