none
How to find the first SQL Server version where new functionality was introduced? RRS feed

Answers

  • Consider using the Microsoft.SqlServer.TransactSql.ScriptDom.dll library to parse scripts according to your target SQL version. This could be included as a static code analysis step in your database build pipleline process.

    # Microsoft.SqlServer.TransactSql.ScriptDom.dll available in NuGet package https://www.nuget.org/packages/Microsoft.SqlServer.DacFx.x64/
    
    # load Script DOM assembly for use by this PowerShell session
    Add-Type -Path "C:\temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
    
    Function Parse-Script ($script, $parser, $versionString) {
    
        $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
        $stringReader = New-Object System.IO.StringReader($script)
        $fragment = $parser.Parse($stringReader, [ref]$parseErrors)
    
        if($parseErrors.Count -gt 0) {
            Write-Host "Fail: $versionString ($($parser.GetType().Name))" -ForegroundColor Yellow
        }
        else {
            Write-Host "Success: $versionString ($($parser.GetType().Name))" -ForegroundColor Green
        }
    }
    
    Function Test-VersionCompatibility ($script) {
    
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql80Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2000"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql90Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2005"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql100Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2008"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql110Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2012"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql120Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2014"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql130Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2016"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2017"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2019"
        
    }
    
    #this is an example T-SQL script to parse
    $script = @"
    SELECT TRY_CAST('1' AS int);
    "@
    
    Test-VersionCompatibility -script $script
    

    Sample results:

    Fail: SQL 2000 (TSql80Parser)
    Fail: SQL 2005 (TSql90Parser)
    Fail: SQL 2008 (TSql100Parser)
    Success: SQL 2012 (TSql110Parser)
    Success: SQL 2014 (TSql120Parser)
    Success: SQL 2016 (TSql130Parser)
    Success: SQL 2017 (TSql140Parser)
    Success: SQL 2019 (TSql150Parser)


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, November 17, 2019 10:14 PM
    • Marked as answer by Naomi N Monday, November 18, 2019 1:07 PM
    Sunday, November 17, 2019 10:12 PM

All replies

  • Hi Naomi N,

    Please refer to https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh974669%28v%3dsql.110%29 for SQL Server 2012.

    Hope this could help you.

    Best Regards,

    Amelia Gu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 15, 2019 1:14 AM
  • Hi everybody,

    I think there needs to be something in the documentation that would say when (which SQL Server version) this function first appears.

    Totally agree!

    If the response helped, do "Mark as answer" or Upvote it
    - Vaibhav

    Friday, November 15, 2019 5:45 AM
  • I have upvoted below similar feedback:

    SQL Documentation missing SQL Server Version since a function or DMV was added


    If the response helped, do "Mark as answer" or Upvote it
    - Vaibhav

    Friday, November 15, 2019 2:14 PM
  • Thanks, I did as well.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, November 15, 2019 4:12 PM
  • They used to have this in the docs for a while, when tried to have a version-independent T-SQL reference. But it seems that they have returned to the old style with one set per version. Which may just as well be better, since it can be quite confusing in a long topic where things have been added for about every version. As one example, take ALTER TABLE. And this is also why I think that this idea is not really fruitful. Single commands is one thing, but when it comes to additional syntax in individual commands, it easily becomes all to nitty-gritty.

    In your cases, it seems simple enough. If you have a requirement that your product should run on SQL 2012, you should be developing on SQL 2012, or at least have an instance where you can test and verify that your code runs on SQL 2012.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, November 17, 2019 9:31 PM
  • Consider using the Microsoft.SqlServer.TransactSql.ScriptDom.dll library to parse scripts according to your target SQL version. This could be included as a static code analysis step in your database build pipleline process.

    # Microsoft.SqlServer.TransactSql.ScriptDom.dll available in NuGet package https://www.nuget.org/packages/Microsoft.SqlServer.DacFx.x64/
    
    # load Script DOM assembly for use by this PowerShell session
    Add-Type -Path "C:\temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
    
    Function Parse-Script ($script, $parser, $versionString) {
    
        $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
        $stringReader = New-Object System.IO.StringReader($script)
        $fragment = $parser.Parse($stringReader, [ref]$parseErrors)
    
        if($parseErrors.Count -gt 0) {
            Write-Host "Fail: $versionString ($($parser.GetType().Name))" -ForegroundColor Yellow
        }
        else {
            Write-Host "Success: $versionString ($($parser.GetType().Name))" -ForegroundColor Green
        }
    }
    
    Function Test-VersionCompatibility ($script) {
    
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql80Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2000"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql90Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2005"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql100Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2008"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql110Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2012"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql120Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2014"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql130Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2016"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2017"
        $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true)
        Parse-Script -script $script -parser $parser -versionString "SQL 2019"
        
    }
    
    #this is an example T-SQL script to parse
    $script = @"
    SELECT TRY_CAST('1' AS int);
    "@
    
    Test-VersionCompatibility -script $script
    

    Sample results:

    Fail: SQL 2000 (TSql80Parser)
    Fail: SQL 2005 (TSql90Parser)
    Fail: SQL 2008 (TSql100Parser)
    Success: SQL 2012 (TSql110Parser)
    Success: SQL 2014 (TSql120Parser)
    Success: SQL 2016 (TSql130Parser)
    Success: SQL 2017 (TSql140Parser)
    Success: SQL 2019 (TSql150Parser)


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Sunday, November 17, 2019 10:14 PM
    • Marked as answer by Naomi N Monday, November 18, 2019 1:07 PM
    Sunday, November 17, 2019 10:12 PM