[SQL Server] Query version, edition and update level - 版本、版次、編號

查詢 SQL Server 的產品版本、版本編號資訊等，可以使用以下的範例：

-- Simple - SQL Server system and build information
SELECT @@VERSION;

V1: Query SQL Server system and build information

-- V1_SQL Server system and build information
/*
Applies to: SQL Server 2012：
 SERVERPROPERTY('ProductUpdateLevel') 'ProductUpdateLevel', 
 SERVERPROPERTY('ProductBuildType') 'ProductBuildType', 
 SERVERPROPERTY('ProductUpdateReference') 'ProductUpdateReference', 
*/
SELECT @@SERVERNAME 'InstanceName',
 CASE 
      WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '8%' THEN 'SQL Server 2000'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '9%' THEN 'SQL Server 2005'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '10.0%' THEN 'SQL Server 2008'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '10.5%' THEN 'SQL Server 2008 R2'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '11%' THEN 'SQL Server 2012'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '12%' THEN 'SQL Server 2014'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '13%' THEN 'SQL Server 2016' 
   WHEN CONVERT(VARCHAR(128), 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 'High-Availability',
 SERVERPROPERTY('ProductVersion') 'ProductVersion',
 SERVERPROPERTY('ProductLevel') 'ProductLevel',
 SERVERPROPERTY('Edition') 'ProductEdition',
 SERVERPROPERTY('ProductUpdateLevel') 'ProductUpdateLevel', 
 SERVERPROPERTY('ProductBuildType') 'ProductBuildType', 
 SERVERPROPERTY('ProductUpdateReference') 'ProductUpdateReference', 
 DATABASEPROPERTYEX('master','Version') 'DatabaseInternalVersion'
GO

V2: Query SQL Server system and build information

適用於 SQL Server 2017
加上 作業系統平台
使用 SQL Server 2017 新增加的 sys.dm_os_host_info

-- V2_SQL Server system and build information
-- Applies to: SQL Server 2017

SELECT @@SERVERNAME 'InstanceName',
 CASE 
      WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '8%' THEN 'SQL Server 2000'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '9%' THEN 'SQL Server 2005'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '10.0%' THEN 'SQL Server 2008'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '10.5%' THEN 'SQL Server 2008 R2'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '11%' THEN 'SQL Server 2012'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '12%' THEN 'SQL Server 2014'
   WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '13%' THEN 'SQL Server 2016' 
   WHEN CONVERT(VARCHAR(128), 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 'High-Availability',
   host_distribution 'HostDistribution', host_release 'HostRelease',
 SERVERPROPERTY('ProductVersion') 'ProductVersion',
 SERVERPROPERTY('ProductLevel') 'ProductLevel',
 SERVERPROPERTY('Edition') 'ProductEdition',
 SERVERPROPERTY('ProductUpdateLevel') 'ProductUpdateLevel', 
 SERVERPROPERTY('ProductBuildType') 'ProductBuildType', 
 SERVERPROPERTY('ProductUpdateReference') 'ProductUpdateReference', 
 DATABASEPROPERTYEX('master','Version') 'DatabaseInternalVersion'
FROM sys.dm_os_host_info 
GO

-- SQL Server 2017 RC2 on Windows Server



-- SQL Server 2017 RC2 on CentOS 7



-- SSMS_伺服器屬性_CentOS_7

---

檢視各個版本回傳的資訊

-- SELECT @@VERSION



-- 010_SQL Server 2008 R2



-- 011_SQL Server 2012 SP1



-- 012_SQL Server 2014_SP1



-- 013_SQL Server 2016_SP1



-- 014_SQL Server 2016 SP1



-- 015_SQL Server 2017 RC2

---

參考資料

How to determine the version, edition and update level of SQL Server and its components
https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version--edition-and-update-level-of-sql-server-a

查詢 SQL Server 的產品版本、版本編號
http://sharedderrick.blogspot.tw/2011/01/sql-server.html

sys.dm_os_host_info (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-host-info-transact-sql