Checking your SQL Server versions is a common task, as most database administrators will know how important it is to keep the software on the host up to date. This post is here to help you to check the version of SQL Server instances.

You may have many instances running on the same host server all running on different versions. To check if you have more than one MS SQL Instance, open SQL Server Configuration Manager or Services.msc on your host. If it’s a multi-tenant MSSQL host there will be more than one SQL Server Service and the same for other SQL Services like the SQL Agent Service.

As ever, there are many ways to get such metadata information from SQL Server. Here are a few example ways to check your SQL Server version:
# TSQL – SELECT @@VERSION / SERVERPROPERTY
# SSMS GUI – SQL Server Options

When you have your SQL Server versions noted, you’ll then want to check how up-to-date you are. There are base SQL Server Build numbers (16.0..) and then minor version numbers (0.700.4). You can learn more about determining the version of your SQL Server with the help of Microsoft Docs.

SQL Query – SELECT @@VERSION / SERVERPROPERTY

One of the quickest ways to get the SQL Server version is to use the @@VERSION in-built TSQL function, as shown in the example below.

-- Get Running MSSQL Version
SELECT @@VERSION;

Running this will show your MS SQL Major version number, Microsoft SQL Server 2022 (CTP2.1) in this case. It also shows the build version number, 16.0.700.4, which I can cross-check with this sqlserverbuilds website to verify I’m running the latest SQL Server version available. Also, note the Microsoft Docs link provided at the top of this post.

We can also get this SQL Server version information using another built-in Function, and I’m including other examples for system metadata collection for diagnostics.

-- TSQL Functions for Other Metadata
SELECT 
	@@SERVERNAME AS 'hostname',
	@@SERVICENAME AS 'instance',
	@@SPID AS 'current_running_SPID_ID',
	@@VERSION AS 'mssql_version',
	CASE
        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'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '14%' THEN 'SQL2017'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '15%' THEN 'SQL2019'
        WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('productversion')) LIKE '16%' THEN 'SQL2022'
		ELSE 'unknown'
	END AS 'mssql_version_short';

SSMS GUI – SQL Server Options

The other way to get your SQL Server Version information is to do it in SQL Server Management Studio (SSMS).

In SSMS Object Explorer, right-click the server at the top then select Properties, as shown in the example below.

This will open up the SQL Server Properties window where we are able to see the MS SQL version.

I hope this guide has given you the information you need to check SQL Server build numbers and MS SQL versions. Feel free to check out my SQL Tips tag for more random MSSQL tips from a SQL DBA.