Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

How to tell what SQL Server version you are running


By:   |   Read Comments (18)   |   Related Tips: 1 | 2 | 3 | More > SQL Server Configurations

Problem

One issue that I am often faced with is determining what service pack is installed on the SQL Server. In this tip we look at how to find the version of SQL Server that is running and also what service pack or hotfix is installed.

Solution

There are a few ways to determine the version of SQL Server that is installed.


Method 1

The first is by using either Enterprise Manager or SQL Server Management Studio and right clicking on the instance name and selecting properties. In the general section you will see information such as on the following screenshots. The "Product version" or "Version" gives you a number of the version that is installed. As you can see with the SQL Server 2000 screenshot it also shows you the service pack that is installed.

SQL Server 2000

SQL Server 2000 version information

SQL Server 2005

SQL Server 2005 version information

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

This will look similar for all of these versions.

SQL Server 2008 version information

Method 2

Another option is to run the T-SQL command to give you this information. As you can see the output from the different versions is pretty much the same. The one thing that you do not get when you do this is the Service Pack name like you do in Enterprise Manager.

SELECT @@VERSION

When this is run it will give you information such as the following:

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: )


Method 3

Another option to determine what version of SQL Server is installed is to look at the version of the SQL Server files. This is an option if SQL Server is not running and you need to find the version.

Open Windows Explorer and navigate to the folder where SQL Server is installed such as "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn", for SQL Server 2008.

Find file sqlservr.exe and right click and select properties and a window will open like the one below. Here we can see that this file is version 10.0.2573.0.

SQL Server version from file

Method 4

One of our readers, Ben Pomicter, also suggested this method using the SERVERPROPERTY function.

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 run it returns results similar to the following:

SQL Server server property version

What do these Numbers Mean?

So now that you have this number such as 9.00.1399.06 or 8.00.760 what do these even mean? The first digits refer to the version of SQL Server such as:

  • 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

Here are a couple of links to other articles that give you the details of what is installed for SQL Server 6.0, 6.5, 7.0, 2000, 2005, 2008, 2008 R2, 2012, 2014 and 2016 based on the version number that is returned.

Next Steps
  • Keep these links handy to determine what version of SQL Server you are running


Last Update:






About the author


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Saturday, September 24, 2016 - 11:20:06 AM - Raghu Back To Top

 How to check sql server version [All the instances], IS, AS and RS version along with service startup type and service acount details, 

 

 


Monday, September 12, 2016 - 4:37:51 PM - Aaron Nelson Back To Top

 Method 5

 Works on SQL Server 2016 & SQL Server 2014 SP1+

SELECT SERVERPROPERTY ('ProductMajorVersion')  AS 'ProductMajorVersion'
 ,  SERVERPROPERTY ('ProductMinorVersion')   AS 'ProductMinorVersion'
 ,  SERVERPROPERTY ('ProductBuild')     AS 'ProductBuild'
 ,  SERVERPROPERTY ('ProductUpdateLevel')   AS 'ProductUpdateLevel'
 ,  SERVERPROPERTY ('ProductUpdateReference') AS 'ProductUpdateReference'


Friday, September 09, 2016 - 10:58:19 AM - Greg Robidoux Back To Top

Thanks Ben for another way to get this data.  I will update the tip to include your script.

-Greg


Friday, September 09, 2016 - 10:52:14 AM - Ben Pomicter Back To Top

I'm surprised the SERVERPROPERTY function isn't mentioned.  That would seem to be the most straightforward way, especially if you're pulling version/edition/SP into a report or repository (easier than parsing the output of @@VERSION), e.g.

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'
            ELSE 'unknown'
       END AS MajorVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('ProductVersion') AS ProductVersion

which would give output such as

MajorVersion   ProductLevel   Edition                            ProductVersion
SQL2008 R2    SP1               Standard Edition (64-bit)   10.50.2796.0

 


Thursday, July 14, 2016 - 6:15:19 AM - Someone Back To Top

Thanks,

 

Method 3 was able to easily provide me with all the info I needed.

 

*thumbs up*


Monday, August 10, 2015 - 4:40:06 PM - Steve Back To Top

Unfortunately, I have found that the version # does not reflect the patch level. The version # represents the base level for the SQL Server installation. However, if you want to know if your server has the latest patches, you need to check  the OS Registry. Do you know of a tool that can provide the patch level for SQL server? The MAP tool only provides the version #.


Tuesday, August 04, 2015 - 10:18:40 AM - John Suit Back To Top

Awesome, thanks so much for taking the time to write this. As usual, a third party source of information is far superior to that of Microsoft themselves. Kudos.


Friday, May 29, 2015 - 4:09:56 AM - Bart Thieme Back To Top

You could also use xp_msver to retrieve the SQL version and the OS version, but in a table form that is more comprehensable.


Thursday, April 30, 2015 - 1:56:11 PM - Greg Robidoux Back To Top

Take a look at these posts to see if this gives you want you need:

http://blogs.msdn.com/b/sqlosteam/archive/2013/02/17/when-will-my-sql-server-evaluation-edition-expire.aspx

http://mssqlfun.com/2013/09/16/how-to-check-sql-server-evaluation-version-expire-date/

I think if you run SELECT @@VERSION it will say it is an evaluation edition.

 


Thursday, April 30, 2015 - 12:53:16 PM - Susan Back To Top

How do you decipher if it is a Demo version with an expiration date?

 


Friday, January 09, 2015 - 2:32:55 PM - Manuel Back To Top

Another link in Spanish:

http://www.sysadmit.com/2015/01/mssql-saber-la-version-de-sql-server-instalada.html

 

Regards.

 


Thursday, June 19, 2014 - 9:15:56 AM - Greg Robidoux Back To Top

Hi Geoff, I guess technically if the only access to SQL Server is the server itself and there are no connections from other machines just having the server level license should be sufficient.  Take a look at this document for more info on SQL 2005 licensing: http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc


Wednesday, June 18, 2014 - 10:33:35 PM - Geoff Koo Back To Top

Hi Greg,


If you purchase: SQL Server 2005 Standard Full edition with Product Key...That the owner says "does not have a CAL with it."  What does that really mean?  (*Understand, that we are not wanting to network version)

Can you still run SQL 2005 as a single User on a single PC?  

OR do you have to purchase an actual CAL to make it work?

Thanks


Geoff


Thursday, February 07, 2013 - 8:26:00 AM - AGL Back To Top

Another link: http://www.sqlserverbuilds.blogspot.com.es/

Regards.


Tuesday, April 03, 2012 - 9:49:22 AM - Greg Robidoux Back To Top

Iain, thanks for letting me know. 

That link has been updated and now when you get to that page you need to select the version of SQL Server you are working with.

 


Tuesday, April 03, 2012 - 7:49:29 AM - Iain Back To Top

Good article apart from

SQL Server versions (this is the most complete list)

 

That's a dead link.


Friday, March 30, 2012 - 2:11:33 PM - Greg Robidoux Back To Top

The tip has been updated to reflect the note above from James.


Thursday, March 01, 2012 - 9:56:23 AM - James Collett Back To Top

You write:-

"The first is by using either Enterprise Manager or SQL Server Management Studio and right clicking on the database instance name and selecting properties."

[Italics mine]

I think this should read:-

"right clicking on the SQL Server instance name"

They are different entities. It certainly threw me.


Learn more about SQL Server tools