I have a few stored procedures that will require slightly different but critical syntax for option hints added to some version of SQL server but these would be unrecognized and error in early versions of SQL server prior to SQL Server 2014. Is there a way to write your procedure deploy scripts or other options in DACPAC to maintain and deploy different versions of the same scripts and have one version deploy on >SQL 2014 and a different version deploy on < SQL 2014?
You might try to check the version inside the stored procedure. If the target SQL server is 2014 and later you can use the critical syntax for option hints, otherwise you do not use them.
The other way you might try is to remove the stored procedure from the object list in the database project. Instead, you create two None Build scripts for different versions of SQL. In the Post Deployment script, you can call one of the files based on the SQL version.
Appreciate all the feedback and they are all good answers.
This line of thinking was what I was sort of considering already. I'll test and post results later on.
No there is no way to do that in a DACPAC or database project. The entire project is targeted to a version.
However, I would question what exactly you are doing with hints which is required. Hints are extremely rare and normally should not be used. Can you give an example?
In going to the more recent releases of SQL Server we have run into a significant number of very large stored procedures that perform very poorly on newer releases of SQL Server due to cardinality estimation problems.
The FORCE_LEGACY_CARDINALITY_ESTIMATION hint solves these issues quickly in the short term as we are finding quite a few in our client systems that were not seen in QA. We do not want to address this on the database scope level as a whole because most queries run better or the same on the current releases of SQL Server but for those that don't it's a pretty painful issue.
Our method of deployment uses DACPAC but we do not want to maintain an entirely separate project/package just for this issue and we still have a few clients on 2012 so this option/hint does not exist yet on the release.
I was hoping there was a flag or method to have a component have varying versions somehow based on the SQL server release being deployed to
Thanks for the feedback.
Those problems should almost all be resolved. Have you tried with the current patch level?
On SQL 2016 or later, you can force plans through query store. First run in compat level 110, so that you have the old cardinality estimator. After some time flip the new compat level, and then you can force plan where you get regressions.
You can also force the hint through plan guides (which you could ship with your installation), but no one loves plan guides.
15 people are following this question.
