Tag Archives: Sql Server Edition

How to get Sql Server Version, Edition, Product Level etc

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:
sql-server-version

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:
sql-server-version-details-using-serverproperty-function

Version to Sql Server Product mapping

Below table lists out the Sql Server Version to Product mapping

verion-to-product-mapping

[ALSO READ] 100 Frequently used queries in Sql Server