6 Ways to Get SQL Server Versions


By:   |   Updated: 2021-07-07   |   Comments   |   Related: More > SQL Server Configurations


Problem

In this article, I will show multiple ways to get SQL Server version details, some include using the SSMS GUI functionality and others use Transact-SQL code. You can use any option that best suits your needs.

Solution

The SQL Server database engine has many different versions like SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, etc. Every new version comes with some new enhancements to make it more reliable, faster and scalable than their previous versions. There are lot of activities in our day to day job that require getting version related information. We can get this information using SQL Server Management Studio, SQL Server services, SQL Server error logs or using T-SQL. Although there are many T-SQL options to fetch version related details, I explain just three of them in this article.

Let's start with getting version information using SQL Server Management Studio, then I will show you other options later in the article.

Get SQL Server Version in Microsoft SQL Server Management Studio

This is a very easy and simple approach to get the SQL Server version for your installed SQL Server instance using the SSMS Object Explorer. When we connect to a SQL Server instance in SQL Server Management Studio it displays the SQL Server instance name along with its version and the login which is connected to the instance as shown in the below image.

Below we can see the SQL Server version, which in our case is 15.0.2000.5. Version 15.x is for SQL Server 2019. If you want to know about the service pack level or cumulative updates, then you need to read and understand complete numbers 15.X.XXX.X.  The numbers in place of X represents the patch level for the SQL Server.

ssms instance and version

Get SQL Server Version using SQL Server Instance Properties Window

This is another simple way to get SQL Server version related details.

Right click on the instance name in SSMS and select Properties as shown below.

ssms instance properties

You will get the Server Properties window for your SQL Server instance.

Below you can see a lot of useful information related to the system on the General page. You can see the same version details we saw above. You can also get which version of the operating system (Windows Server, Linux, etc.) is installed for your SQL Server instance.  If you want to explore other server level properties, then select the page on the left.

ssms instance properties

Get SQL Server Version using SQL Server Error Log File

This section will describe how to find SQL Server version related information from the SQL Server error log file.

Go to the Error Logs section in SSMS and double click on the Current error log.

ssms error log

If you scroll to the bottom of the error log, you should see an entry like below. If you select this line you will get a better view of the message which will show the SQL Server version as shown below.

ssms error log

Get SQL Server Version using Compatibility Level

We can also get the SQL Server version by looking at database compatibility level options.

If you right click on a database in SSMS and select Properties the window below will open.

Then go to the Options page and select the dropdown for Compatibility Level. The latest version in the dropdown will be the version that is installed.  So, below we can see that we can use up to SQL Server 2019 compatibility level, so the SQL Server version of the instance is SQL Server 2019. 

Another thing you can get from this window is the compatibility level your database is running. So below we can see that we can select to run the database in many different SQL Server version compatibility levels.

database properties

Get SQL Server Version using T-SQL

Getting SQL Server version details using T-SQL is very easy. There are many T-SQL statements we can use to get such information. Let's start with very popular command to get this information i.e. SELECT @@VERSION

--Get SQL Server Version
SELECT @@VERSION

You can see the output of the above T-SQL statement. It shows the same information that was in the error log file.

Get SQL Server Version using SELECT @@VERSION

Another useful T-SQL statement to get SQL Server version related details is using the system function SERVERPROPERTY(). Below is the statement you can run to get such information.

--Get SQL Server Version using SERVERPROPERTY()
SELECT 
   SERVERPROPERT('Productversion') As [SQL Server Version],
   SERVERPROPERT('Productlevel') As [SQL Server Build Level],
   SERVERPROPERT('edition') As [SQL Server Edition]

You can see the output of the above statement and it shows the same version we saw in the other options. This system function is also very useful to get lot of other details like edition of SQL Server, product build level, etc.

Get Version using SERVERPROPERTY

Another T-SQL option is to use the system stored procedure sp_server_info.

--Get SQL Server Version using system stored procedure
EXEC sp_server_info

You can see I have executed the above stored procedure and get the SQL Server version as shown in the below image.

Get SQL Server Version using system stored procedure sp_server_info

Get SQL Server Version using SQL Server Services

Next, I will show you how to get SQL Server version using the services running on the server. You need to open SQL Server Configuration Manager to see all the SQL Server services.

Click on SQL Server Services on the left to get all of the installed services for SQL Server on the right side. Right click on the SQL Server service and select Properties.

Get SQL Server Version using SQL Server services using SQL Server Configuration Manager

The below window will open. Now click on the Advanced tab and you can see the version as shown below.

sql server services properties
Next Steps

I have explained various ways to get SQL Server version related information. Go ahead and explore the different options in this article.

Read more articles on SQL Server



Last Updated: 2021-07-07


get scripts

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

How to tell what SQL Server versions you are running

Resolving could not open a connection to SQL Server errors

Identify SQL Server TCP IP port being used

Changing SQL Server Collation After Installation

Managing Maximum Number of Concurrent Connections in SQL Server














get free sql tips
agree to terms