Strategies on maintaining a codebase that supports various SQL Server Editions/Versions. RRS feed

  • Question

  • Hey all,

    I have searched long and hard to find something about this on the internet as I can not be the only one on the planet with this issue, however, I can not find anything close to what I am required to do.  I've seen lots of things about version control... but that (as in Version1, Version2, version3 of an object) is not what I am needing.

    I'm looking for a strategy for keeping my code base maintainable yet supporting various code for different versions and editions of SQL Server. 

    For instance,

    I have to support customers on SQL Server 2008-2016.  And, we would like to have a table (or object) that makes use of a feature in 2016.  Say.. "The in memory OLTP" feature of a table.  Obviously, this feature is not in 2008 or 2012, don't know if we want it for 2014, but would love to use it in 2016.  So, I would need Different "Create Table" scripts based on the SQL Server Version.  While I could have a single script with IF blocks for SQL Server Versions/Editions, The Create Table statement would have to be dynamic.. and therefor in a VARCHAR String.. which makes maintainability difficult. 

    How have people dealt with this as far as Version features that are deprecated or added and Edition features (Partitioning in Enterprise) that rely on differing scripts to be run in the single shippable codebase?

    Thursday, November 10, 2016 3:26 PM


All replies

  • Most people have a single version of their code which supports the base version that their customers would support. For example right now minimal support should be SQL 2012 as SQL 2008 r2 went out of support on July 8, 2014.

    What I have seen is scripts which detect the version and run different sections of script.

    select SERVERPROPERTY('ProductVersion'),SERVERPROPERTY('ProductLevel')

    Thursday, November 10, 2016 3:44 PM
  • There is no simple solution, but you don't need dynamic SQL, nor you should use it.

    Treat different versions as update paths.

    Start with scripting the common denominator (lowest SQL Server version). And just script the different features as "update" scripts.

    Use SQLCMD scripts to hold the version switching to keep the scripts clean.

    Thursday, November 10, 2016 3:52 PM
  • There is no good way to do what you describe.  Mostly you have to write your code to support the lowest version you are going to support.

    Most software companies simply say, the next upgrade to our product will require SQL 2014+ and force customers to update.

    Thursday, November 10, 2016 8:12 PM
  • I suggest you use version control system and you keep the various SQL Server versions (2008, 2014...) entirely separate.

    Reference: How to add Stored Procedures to Version Control

    Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
    New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    • Proposed as answer by Martin.CairneyMVP Friday, November 11, 2016 12:23 AM
    • Marked as answer by Kalman Toth Tuesday, December 20, 2016 1:06 PM
    Thursday, November 10, 2016 8:20 PM