查詢Microsoft SQL Server當前的版本

每次用到都要再查一次,所以將其記錄再此。這些指令可以查出你目前的Microsoft SQL Server的版本號,以及所依存的主機作業系統。

簡單查詢SQL版本

結果會以單一列資料的形式呈現。

Select @@version
-----
Microsoft SQL Server 2008 R2 (RTM) - 10.xx.xxxx (X64) May 31 2019 00:00:00 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1

詳細查詢SQL版本

如果要看比上面更詳細的資料(其實也沒多顯示很多東西,就大概是把欄位切一切分開顯示),可以考慮用下列的指令。

SELECT
  @@SERVERNAME 'InstanceName',
  CASE
    WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '8%'    THEN 'SQL Server 2000'
    WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '9%'    THEN 'SQL Server 2005'
    WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '10.0%' THEN 'SQL Server 2008'
    WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '10.5%' THEN 'SQL Server 2008R2'
    WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '11%'   THEN 'SQL Server 2012'
    WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '12%'   THEN 'SQL Server 2014'
    WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '13%'   THEN 'SQL Server 2016'
    WHEN CONVERT(VARCHAR(99), SERVERPROPERTY('ProductVersion')) like '14%'   THEN 'SQL Server 2017'
    ELSE 'unknown'
    END AS 'SQLServerVersion', 
  CASE
    WHEN SERVERPROPERTY('IsClustered')   = 1 AND  SERVERPROPERTY('IsHadrEnabled') = 1 THEN 'Failover Cluster + Availability Groups'
    WHEN SERVERPROPERTY('IsClustered')   = 1 THEN 'Failover Cluster'
    WHEN SERVERPROPERTY('IsHadrEnabled') = 1 THEN 'Availability Groups'
    ELSE 'unknown'
    END AS 'HighAvailability',
  SERVERPROPERTY('ProductVersion')         'ProductVersion',
  SERVERPROPERTY('ProductLevel')           'ProductLevel',
  SERVERPROPERTY('Edition')                'ProductEdition',
  SERVERPROPERTY('ProductUpdateLevel')     'ProductUpdateLevel', 
  SERVERPROPERTY('ProductBuildType')       'ProductBuildType', 
  SERVERPROPERTY('ProductUpdateReference') 'ProductUpdateReference', 
  DATABASEPROPERTYEX('master','Version')   'DatabaseInternalVersion'
GO
Microsoft SQL Server Version Query Command