SQL Server snippets

How to query the version of SQL Server

You can use the following query

SELECT
    SERVERPROPERTY ('productversion') AS Product_Version,
    CASE
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'
    ELSE 'unknown' END AS Major_Version,
    CASE
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '1804890536' THEN 'Enterprise'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '1872460670' THEN 'Enterprise Edition: Core-based Licensing'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '610778273' THEN 'Enterprise Evaluation'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '284895786' THEN 'Business Intelligence'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '-2117995310' THEN 'Developer'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '-1592396055' THEN 'Express'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '-133711905' THEN 'Express with Advanced Services'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '-1534726760' THEN 'Standard'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '1293598313' THEN 'Web'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '1674378470' THEN 'Database SQL o Azure Synapse Analytics'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '-1461570097' THEN 'SQL Edge di Azure Developer'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('EditionID')) = '1994083197' THEN 'SQL Edge di Azure'
    ELSE 'unknown' END AS Edition_ID,
    CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
        WHEN 1 THEN 'Integrated security (Windows Authentication)'
        WHEN 0 THEN 'Not integrated security. (Both Windows Authentication and SQL Server Authentication.)'
    ELSE 'unknowk' END AS Is_Integrated_Security_Only,
    CASE SERVERPROPERTY('EngineEdition')
        WHEN 1 THEN 'Personal'
        WHEN 2 THEN 'Standard'
        WHEN 3 THEN 'Enterprise'
        WHEN 4 THEN 'Express'
        WHEN 5 THEN 'Database SQL'
        WHEN 6 THEN 'Microsoft Azure Synapse Analytics'
        WHEN 8 THEN 'Azure SQL Managed Instance'
        WHEN 9 THEN 'Azure SQL Edge'
        WHEN 11 THEN 'Azure Synapse serverless SQL pool'
    ELSE 'unknowk' END AS Engine_Edition,
    CASE SERVERPROPERTY('IsClustered')
        WHEN 0 THEN 'Clustered'
        WHEN 1 THEN 'Not Clustered'
    ELSE 'unknowk' END AS Is_Clustered,
    SERVERPROPERTY('ProductLevel') AS Product_Level,
    SERVERPROPERTY('Edition') AS SQL_Edition,
    SERVERPROPERTY('ProductVersion') AS Product_Version,
    SERVERPROPERTY('BuildClrVersion') AS Build_Clr_Version,
    SERVERPROPERTY('MachineName') AS Machine_Name,
    SERVERPROPERTY('ResourceLastUpdateDateTime') AS Resource_Last_UpdateDateTime;

You can find an interactive version of this example following this link .

Back to SQL Server cookbook page