How to find which SQL Server Version is Running
May 22nd, 2018 in MySQL |

Issue #1 : Determining what service pack is installed on the SQL Server and examining the presently running version information of SQL Server.

Resolution

There are a few approaches to find out the version of SQL Server that is installed.

Approach #1

Use either Enterprise Manager or SQL Server Management Studio and perform right clicking on the instance name and go on properties. On the usual section you will get the information and the following screenshots. The depicted “Product version” or “Version” offer you a number of the version that is installed. The screenshot of SQL Server 2000 shows you the service pack which is installed.

SQL Server 2000

SQL Server 2000

SQL Server 2005

SQL Server 2005

SQL Server 2008 / 2008 R2 / 2012 / 2014 / 2016

This will look similar for all of these versions.

SQL Server 2008 - 2008 R2 - 2012 - 2014 - 2016

Approach #2

Second option is to execute the T-SQL command to provide you this information. As you will observe the output from the variant versions is very similar. The one thing which you do not get when you perform this is the Service Pack name, in the similar fashion as you used to do in Enterprise Manager.

SELECT @@VERSION

When this is executed it will provide you following information as listed below:

SQL Server 2000

Microsoft SQL Server 2000 – 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

SQL Server 2005

Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

SQL Server 2008

Microsoft SQL Server 2008 (SP1) – 10.0.2573.0 (X64)
Feb 4 2011 11:27:06
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

SQL Server 2008 R2

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.0 <X64> (Build 6002: Service Pack 2)

SQL Server 2012

Microsoft SQL Server 2012 – 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

SQL Server 2014

Microsoft SQL Server 2014 – 12.0.2254.0 (X64)
Jul 25 2014 18:52:51
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1) (Hypervisor)

SQL Server 2016

Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)
Apr 29 2016 23:23:58
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 6.3 <x64> (Build 14393: )

Approach #3

This is another option to examine what version of SQL Server is set up and installed to look at the version of the SQL Server files. This is again an option in case SQL Server is not running and you need to know the version.

Explore Windows Explorer and traverse on to the folder where SQL Server is installed like “C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn”, for SQL Server 2008.

Search file sqlservr.exe and then right click and choose properties and a window will get open like the one shown below. In this case we can see that this file is version 10.0.2573.0.

Explore Windows Explorer - 4

Approach #4

Few more suggested method using the SERVERPROPERTY function are shown below:

SELECT

CASE

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (‘productversion’)) like ‘8%’ THEN ‘SQL2000’

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (‘productversion’)) like ‘9%’ THEN ‘SQL2005’

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (‘productversion’)) like ‘10.0%’ THEN ‘SQL2008’

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (‘productversion’)) like ‘10.5%’ THEN ‘SQL2008 R2’

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (‘productversion’)) like ‘11%’ THEN ‘SQL2012’

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (‘productversion’)) like ‘12%’ THEN ‘SQL2014’

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY (‘productversion’)) like ‘13%’ THEN ‘SQL2016’

ELSE ‘unknown’

END AS MajorVersion,

SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,

SERVERPROPERTY(‘Edition’) AS Edition,

SERVERPROPERTY(‘ProductVersion’) AS ProductVersion

When you execute these commands it is going to returns results similar to the following:

returns results similar

What does these number implies

The meaning of these numbers like 9.00.1399.06 or 8.00.760 is as follows:

The first digits depict the version of SQL Server like:

  • 8.0 for SQL Server 2000
  • 9.0 for SQL Server 2005
  • 10.0 for SQL Server 2008
  • 10.5 for SQL Server 2008 R2
  • 11.0 for SQL Server 2012
  • 12.0 for SQL Server 2014
  • 13.0 for SQL Server 2016