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.
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:
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.
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.
| Version | Latest Service Pack | Latest GDR | Latest cumulative update | Complete Version Information | General Guidance |
| SQL Server 2019 | None | GDR (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 builds | SQL Server 2019 Installation |
| SQL Server 2017 | None | GDR (14.0.2037.2 – January 2021) | CU25 (14.0.3401.7 – July 2021) CU22 + GDR (14.0.3370.1 – January 2021) | SQL Server 2017 builds | SQL Server 2017 Installation |
| SQL Server 2016 | SP2 (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 builds | SQL Server 2016 Installation |
| SQL Server 2014 | SP3 (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 builds | SQL Server 2014 Installation |
| SQL Server 2012 | SP4 (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 builds | SQL Server 2012 Installation |
| SQL Server 2008 R2 | SP3 (10.50.6000.34 – September 2014) SP2 (10.50.4000.0 – July 2012) | GDR for SP3 (January 2018) MS 15-058 (July 2015) | None | SQL Server 2008 R2 builds | SQL Server 2008 R2 SP3 Installation |
| SQL Server 2008 | SP4 (10.0.6000.29 – September 2014) SP3 (10.00.5500.00 – October 2011) | GDR for SP4 (January 2018) MS 15-058 (July 2015) | None | SQL Server 2008 builds | SQL Server 2008 Servicing |
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.
