sql

SQL Server Versions

In this article, we’re going to see several Microsoft SQL Server versions and editions and how to identify them.

1. Introduction

On MSSQL Server we need to determine the version and edition to troubleshoot some issues or to know if feature support is available.

To do that, MSSQL Server introduces some procedures to identify the version and edition which we’re using in our environment.

In the next sessions, I’ll show these procedures to identify the Database Engine and Client Tools installed with MSSQL Server.

2. Checking version and edition of SQL Server Database engine

There are many ways to identify the version and edition. Therefore, we can use SQL Server Management Studio (SSMS) or any SQL query application (e.g. SQuirreL, SQLWorkbenchJ…) to use the built-in procedures in the SQL Server instance.

2.1 Using T-SQL

We can execute the query below to take the data about our SQL Server instance.

Running T-SQL

SELECT @@VERSION

The result shows something like this:

Version query result

SQL Server 2019
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 17763: )

The important here is to see the instance version in the second line and the edition in the last line.

2.2 Using SSMS

Another functionality present in Management Studio (SSMS) is right-clicking on the instance name and choose the Properties option. The information should look like below.

sql server versions - instance properties
SQL Server instance properties

Above, we find the Version and the edition on the Product line. This works from 2008 to 2019 versions.

2.3 SERVERPROPERTY function

SQL Server has a functionality called SERVERPROPERTY to retrieve the information about the SQL Server installed. However, I’ve created the following script to show us some information about the instance.

SERVERPROPERTY

SELECT
CASE
 WHEN convert(varchar(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL Server 2000'
 WHEN convert(varchar(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL Server 2005'
 WHEN convert(varchar(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL Server 2008'
 WHEN convert(varchar(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL Server 2008 R2'
 WHEN convert(varchar(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL Server 2012'
 WHEN convert(varchar(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL Server 2014'
 WHEN convert(varchar(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL Server 2016'
 WHEN convert(varchar(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL Server 2017'
 WHEN convert(varchar(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL Server 2019'
 ELSE 'Unknown'
END AS MajorVersion,
SERVERPROPERTY ('productversion') AS ProductVersion,
SERVERPROPERTY ('productlevel') AS ProductLevel,
SERVERPROPERTY ('edition') AS Edition

The query result should bring the following:

sql server versions - serverproperty result
SERVERPROPERTY result

3. How to determine SQL Server Client tools version?

Client tools such as SQL Server Management Studio are shared features common across all instances on the same machine.

Furthermore, if the SQL Server client tools are installed during setup on the first node of the SQL Server cluster, they are automatically added to any nodes that may be added later to the instance of SQL Server using Add Node.

To check which version of client tools is installed, use the Management Studio About on the Help menu.

SSMS About information

4. Latest update available for currently supported versions

In the sheet below, I put the latest update available for each supported version. It’s based on the official SQL Server Documentation site.

VersionLatest Service PackLatest GDRLatest cumulative updateComplete Version InformationGeneral Guidance
SQL Server 2019NoneGDR (15.0.2080.9 – January 2021)CU11 for 2019 (15.0.4138.2 – June 2021)
CU8 + GDR (15.0.4083.2 – January 2021)
SQL Server 2019 buildsSQL Server 2019 Installation
SQL Server 2017NoneGDR (14.0.2037.2 – January 2021)CU25 (14.0.3401.7 – July 2021)
CU22 + GDR (14.0.3370.1 – January 2021)
SQL Server 2017 buildsSQL Server 2017 Installation
SQL Server 2016SP2 (13.0.5026.0 – April 2018)
SP1 (13.0.4001.0 – November 2016)
GDR for SP2 (13.0.5103.6 – January, 2021)
GDR for SP1 (13.0.4259.0 – July 2019)
GDR for RTM (13.0.1745.2 – January 2018)
CU17 for 2016 SP2 (13.0.5888.11 – March 2021)
CU15 + GDR for SP2 (13.0.5865.1 – January 2021)
CU15 + GDR for SP1 (13.0.4604.0 – July 2019)
CU15 for SP1 (13.0.4574.0 – May 2019)
CU14 for SP2 (13.0.5830.85- August 2020)
CU9 for RTM (13.0.2216.0 – November 2017)
SQL Server 2016 buildsSQL Server 2016 Installation
SQL Server 2014SP3 (12.0.6024.0 – October 2018)
SP2 (12.0.5000.0 – July 2016)
SP1 (12.0.4100.1 – May 2015)
GDR for SP3 (12.0.6164.21 – January 2021)
GDR for SP2 (12.0.5223.6 – January 2019)
GDR for SP1(August 2017)
MS 15-058 (July 2015)
CU4 + GDR for SP3 (12.0.6433.1 – January 2021)
CU4 for SP3 (12.0.6329.1 – July 2019)
CU18 for SP2 (12.0.5687.1 – July 2019)
CU13 for SP1 (12.0.4522.0 – August 2017)
SQL Server 2014 buildsSQL Server 2014 Installation
SQL Server 2012SP4 (11.0.7001.0 – September 2017)
SP3 (11.0.6020.0 – November 2015)
SP2 (11.0.5058.0 – June 2014)
SP1 (11.0.3000.00 – November 2012)
GDR for SP4 (11.0.7507.2 – January 2021)
GDR for SP3 (January 2018)
MS 16-136 (November 2016)
MS 15-058 (December 2015)
CU10 for SP3 (11.0.6607.3 – August 2017)
CU16 for SP2 (11.0.5678.0 – January 2017)
CU16 for SP1 (11.0.3487.0 – May 2015)
SQL Server 2012 buildsSQL Server 2012 Installation
SQL Server 2008 R2SP3 (10.50.6000.34 – September 2014)
SP2 (10.50.4000.0 – July 2012)
GDR for SP3 (January 2018)
MS 15-058 (July 2015)
NoneSQL Server 2008 R2 buildsSQL Server 2008 R2 SP3 Installation
SQL Server 2008SP4 (10.0.6000.29 – September 2014)
SP3 (10.00.5500.00 – October 2011)
GDR for SP4 (January 2018)
MS 15-058 (July 2015)
NoneSQL Server 2008 buildsSQL Server 2008 Servicing
SQL Server updates sheet

Important note: the versions SQL Server 2008 and 2008 R2 reached the end of extended support. That means Microsoft will no longer support these versions and recommend the change for a newer version. Click here for more details.

5. Summary

In summary, we saw how to determine the version and edition of SQL Server Database Engine. Also, we use built-in functions to show important information about SQL Server installed instances, and take a look on SSMS to check client tools installed version.

Finally, we could see the latest updates for supported versions of SQL Server.

Sergio Lauriano Junior

Sergio is graduated in Software Development in the University City of São Paulo (UNICID). During his career, he get involved in a large number of projects such as telecommunications, billing, data processing, health and financial services. Currently, he works in financial area using mainly Java and IBM technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button