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.
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.
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.
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.
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.
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.
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.
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
SERVERPROPERTY('Productversion') As [SQL Server Version],
SERVERPROPERTY('Productlevel') As [SQL Server Build Level],
SERVERPROPERTY('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.
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 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.
The below window will open. Now click on the Advanced tab and you can see the version as shown below.
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
