How to display the SQL Server version with a query?

How to display the SQL Server version exact number with a simple SQL Server query? Using SQL Server Management Studio, to get the exact version of the SQL Server installation, it’s possible by using simple T-SQL queries.

 

For example, it’s very helpful to know the exact version of SQL Server installed, especially when working on security patches and upgrades. But also when third-party software needs a specific version of SQL Server to work properly. To clarify, the SQL Server exact number is not only “SQL Server 2016 for example” but also the numbers that goes after, for example “Microsoft SQL Server 2016 – 13.0.5366.0”.

 

How to display SQL Server version with an SQL query and ?

The answer to this question is simple and there are different ways to display the server version. 

 

First option is to use the @@VERSION system variable:  

SELECT @@VERSION; 

 

How to display SQL Server version number with sp_server_info system stored procedure?

The second option is to use the sp_server_info system procedure: 

EXEC sp_server_info 2;

 

The result is the DBMS_VER system attribute which is actually the SQL Server version. In this example, the query displays the exact number in the attribute_value column and is:

Microsoft SQL Server 2008 R2 – 10.50.6000.34

 

In conclusion, these 2 different options are very quick to execute. The queries display the SQL Server version number with 2 different level of details.

 

This section of the blog explains some Microsoft SQL Server abbreviations like MDX for example.