Many times we come across a scenario where we need to know the Sql Server Version Name (i.e. like Sql Server 2005, Sql Server 2012, Sql Server 2014, Sql Server 2016 etc), Version (i.e. like 11.0.2100.60 it is of the format major.minor.build.revision), Edition (i.e. like Express Edition, Developer Edition, Enterprise Edition etc), Product Level (i.e. like RTM, SP, CTP etc) etc. This article explains how we can get all this information.
Approach 1: Using @@VERSION global variable
We can use the @@VERSION global variable like below to get the Sql Server Version name, Version, Edition etc
SELECT @@VERSION
RESULT:
Approach 2: Using SERVERPROPERTY function
We can use the SERVERPROPERTY function to get various information like Sql Server Version name, Version, Edition etc
How to get Server Version Name using SERVERPROPERTY function
DECLARE @Version NVARCHAR(128)
SET @Version =
CONVERT(NVARCHAR(128),SERVERPROPERTY ('ProductVersion'))
SELECT
CASE
WHEN @Version like '8%' THEN 'SQL SERVER 2000'
WHEN @Version like '9%' THEN 'SQL SERVER 2005'
WHEN @Version like '10.0%' THEN 'SQL SERVER 2008'
WHEN @Version like '10.5%' THEN 'SQL SERVER 2008 R2'
WHEN @Version like '11%' THEN 'SQL SERVER 2012'
WHEN @Version like '12%' THEN 'SQL SERVER 2014'
WHEN @Version like '13%' THEN 'SQL SERVER 2016'
ELSE 'Unknown'
END AS 'Sql Server Version Name',
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel
RESULT:
Version to Sql Server Product mapping
Below table lists out the Sql Server Version to Product mapping
