This article explains various methods to find the SQL Server version for an instance. Knowing the SQL Server version is important for several reasons:

• Feature availability: Different versions of SQL Server have different sets of features available. Some features may only be available in certain versions, while others may be deprecated or removed in newer versions. Therefore, knowing the version of the SQL Server you are using is important to ensure that you have access to the features you need.
• Security updates: Microsoft releases security updates and patches for SQL Server to address vulnerabilities and bugs. It's important to know the version of the SQL Server you are running so you can ensure that it is up-to-date with the latest security patches and updates.
• Compatibility: Some applications or tools may only be compatible with certain versions of SQL Server. If you are using an older application or tool, it may only be compatible with an older version of SQL Server.
• Support: Knowing the version of the SQL Server you are using is important to determine if it is still supported and eligible for updates and technical support.

Overall, the version of the SQL Server you are using can have a significant impact on the features available to you, security and stability, compatibility with applications and tools, and access to support and updates.

We can identify the SQL Server version numbers using the following methods.

• Object explorer of SQL Server management studio.
• SQL Server configuration manager (Microsoft Windows Operating System).
• Windows registry (Microsoft Windows Operating System).
• T-SQL Queries
• SQL Server error log.
• Powershell and SQLCMD command (Windows and Linux-based OS).

Each of these is described below.

Method 1 - Using the SSMS Object Explorer

First, open the SQL Server management studio -->Connect to the Database Engine. Once connected, you can see the version of SQL Server next to instance name which is 15.0.2101.7. For more details of the SQL Server version and edition, Right-click on the Instance Name (Nisarg-PC)-->  Select Properties. Following See image:

As shown in following image, a dialog box named Server Properties – Nisarg-PC opens. In the dialog box, you can see various SQL Server instance parameters. The edition of SQL Server is next to the Product parameter which Microsoft SQL Server Developer (64-bit).   The version of SQL Server is next to the Version entry, which is 15.0.2107.7 in this case.

Method 2 - SQL Server Configuration Manager

We can also view the details of the version and edition from the SQL Server configuration manager. Note that the SQL Server configuration manager is only available in the Windows operating system. Open SQL Server configuration manager.

The SQL Server configuration manager opens. Select SQL Server services from Left pan --> Right-click on SQL Server service from right pan--> Select properties.

As shown in following image, a dialog box named SQL Server (MSSQLSERVER) Properties opens. Click on Advanced tab. You can see various SQL Server configuration parameters. The SQL Server edition in Stock keeping unit name text box, which is Developer Edition. The Version Number in the Version text box, which is 15.0.2000.5

Method 3 - Using the Windows registry

We can find the SQL Server version from the Windows registry. Note that this method is only available in the Windows operating system.

Launch the Windows registry and navigate to "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\Setup". As shown in following screenshot of Registry Editor, you can view the list of registry keys that shows the data of the SQL Server instance configuration parameter.

The patch level key shows the current version number, and the EditionType key shows the edition of the SQL Server.

Method 4 - T-SQL Queries

We can also use a few T-SQL queries to populate the edition and version of the installed SQL Server. You can run these queries in SQL Server Management Studio or dbForge Studio for SQL Server.

Query

select @@version as [SQL Server version]
Go

Query output for SQL Server on Windows:

Query output for the Azure SQL instance:

Query output for the SQL Server on Linux:

Method 5 - Check the SQL Server Errorlog file

You can also find the edition and version of SQL Server from the SQL Server error log file. You can find the error log in the instance root directory. Open the ERRORLOG file in the instance root directory using Notepad or other text editors. You can see the SQL Server edition, version number, and build number in the ERRORLOG file. Following is the screenshot of SQL Server ERRORLOG file.

Suppose you are using the Linux operating system. In that case, you can open the SQL Server error log using Vim or any other editor. The command to open the error log is the following:

root@UbuntuSQL2019:/# vim /var/opt/mssql/log/errorlog

Following is the screenshot of ERRORLOG of SQL Server on Linux.

Method 6 - PowerShell Command or Bash command

You can view the edition and version number using PowerShell. Execute the following command:

Invoke-Sqlcmd -ServerInstance "Nisarg-PC" -Query "Select @@version"

Command Output

If you are using SQL Server on Linux, you can use the SQLCMD command line utility to view the SQL Server version. The command is the following:

root@UbuntuSQL2019:/# sqlcmd -S UbuntuSQL2019 -U sa
Password:
1> select @@version as [SQLServer Version]
2> Go

Command Output

Summary

This article explains the importance of knowing SQL Server version and different methods to identify the SQL Server version. In my upcoming articles, you will learn how to display the details of SQL Server version and other key parameters on SSRS report.