I found the option to use NOPARAM for constants in queries so that some values are not parameterized in query plans: NOPARAM
I understand that a potential disadvantage to using this would be that new plans would be generated for any new value used for those constants with NOPARAM applied. Are there any advantages to using NOPARAM for queries where some constants are expected to always be the same?
The advantage is that you will get a different plan for each literal you pass in that is surrounded by a NOPARAM call. So if you have queries that really need different query plans for different literals, you can get that using NOPARAM. For example, if you have 1000 customers ID values, and the biggest customer has 50% of all the data, they may need their own query plan because the data is so skewed toward them. So you could use NOPARAM for that particular customer ID in some of your critical queries.