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

One thought on “How to get Sql Server Version, Edition, Product Level etc

  1. Awesome!
    Awesome!
    Awesome!

    “Biradar SQL Gita”
    “Biradar SQL Bible”

    I enjoyed reading this post and please keep posting.

    Thanks for educating the community and appreciate your volunteership.

Leave a Reply

Your email address will not be published. Required fields are marked *