If you would like to know/check SQL Server version that is currently being used in your server Check SQL Server Version then the easiest way is using the below query:

SELECT @@VERSION

The above query when executed in SQL Server Management Studio will return something as shown below:

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

But the information is returned as a single string, so if you would like get the individual properties then you can use the SQL Server built-in function.

Check SQL Server Version using Built-in-function

If you are looking for some fine-tuned results based on which you could modify the output then you can make use of the Built-in-function called SERVERPROPERTY.

Syntax:

SERVERPROPERTY (propertyname)

There are various properties available but let us make use of those that helps us to find the SQL Server version.

Property Name What does it return Example
EDITION This property will return the Edition of SQL Server that is installed. There are various editions like,

  • Enterprise Edition
  • Developer Edition
  • Express Edition

This result that you get using this query may help you to understand the Capacity Limits of your installed SQL Server.

SELECT SERVERPROPERTY(‘EDITION’)Output:Enterprise Edition (64-bit)
EDITIONID This will return the Edition ID that is associated with each Edition. SELECT SERVERPROPERTY(‘EDITIONID’)Output:1804890536
PRODUCTVERSION The version of SQL Server installed is returned in major.minor.build.revision format. Refer the Product Version Reference table. SELECT SERVERPROPERTY(‘ProductVersion’)Output:10.50.1600.1 
PRODUCTLEVEL This returns whether you have original release version, service pack version or Community Technology Preview version. SELECT SERVERPROPERTY(‘ProductLevel’)Output:RTM

 

So based on the above parameters you could frame your own query as shown below,

SELECT SERVERPROPERTY(‘PRODUCTVERSION’) as VersionNumber, SERVERPROPERTY (‘PRODUCTLEVEL’)as ProductLevel, SERVERPROPERTY (‘EDITION’) as Edition

Which will give you the below result:

10.50.1600.1       RTM       Enterprise Edition (64-bit)

SQL Server Product Version Reference:

You can use the below table as a reference to check which SQL Server Release is installed in your server for the respective Product Version. For example: Version 10.50.1600.1  means SQL Server 2008 R2 RTM is the SQL Server Release installed on the server.

Release Name Product Version
SQL Server 2000 Version Information
SQL Server 2000 RTM 8.00.194
SQL Server 2000 Service Pack 1 8.00.384
SQL Server 2000 Service Pack 2 8.00.534
SQL Server 2000 Service Pack 3 8.00.760
SQL Server 2000 Service Pack 4 8.00.2039
SQL Server 2005 Version Information
SQL Server 2005 RTM 9.00.1399
SQL Server 2005 Service Pack 1 9.00.2047
SQL Server 2005 Service Pack 2 9.00.3042
SQL Server 2005 Service Pack 3 9.00.4035
SQL Server 2005 Service Pack 4 9.00.5000.00
SQL Server 2008 Version Information
SQL Server 2008 RTM 10.00.1600.22
SQL Server 2008 Service Pack 1 10.00.2531.00
SQL Server 2008 Service Pack 2 10.00.4000.00
SQL Server 2008 Service Pack 3 10.00.5500.00
SQL Server 2008 Service Pack 4 10.00.6000.29
SQL Server 2008 R2 Version Information
SQL Server 2008 R2 RTM 10.50.1600.1
SQL Server 2008 R2 Service Pack 1 10.50.2500.0
SQL Server 2008 R2 Service Pack 2 10.50.4000.0
SQL Server 2008 R2 Service Pack 3 10.50.6000.34
SQL Server 2012 Version Information
SQL Server 2012 RTM 11.00.2100.60
SQL Server 2012 Service Pack 1 11.00.3000.00
SQL Server 2012 Service Pack 2 11.0.5058.0
SQL Server 2014 Version Information
SQL Server 2014 RTM 12.0.2000.80