Can I have different versions of a stored procedure in DACPAC deploy based on the SQL Server version level due to syntax changes?

Troy Tech 21 Reputation points
2021-03-05T18:19:55.907+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,828 questions
0 comments
Accepted answer
  1. Guoxiong 8,126 Reputation points
    2021-03-05T21:47:31.29+00:00

    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.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-03-05T20:10:28.347+00:00

    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?

  2. Erland Sommarskog 101.9K Reputation points MVP
    2021-03-05T22:32:39.873+00:00

    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.

    0 comments