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,
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 |