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.

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.

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.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.