Javatpoint Logo
Javatpoint Logo

How to find SQL Server Version

A version is a specific form of something that varies from other forms of the same version. This article will explain the complete overview to find the SQL Server version running on your system.

We can get the SQL Server version in multiple ways, and some of them are listed below:

  • Using a Query
  • Using Console/Command Prompt
  • Using SQL Server Management Studio
  • Using PowerShell
  • Using Log Files
  • Using SQL Server Properties

Let us discuss each in detail.

Using a Query

It is the easiest way to see the SQL Server version installed on our system. We can use the @@VERSION function to get all of the SQL Server instance's version details. This function returns a one-line string output containing all the relevant information about the SQL Server. It will display the following information in the output:

  • SQL Server Version
  • Processor Architecture
  • SQL Server Build Date
  • Copyright Statement
  • SQL Server Edition
  • Operating System Version

Now, we will execute the below query to see the current version of the SQL Server:

Here is the output:

How to find SQL Server Version

The below table will explain this information in a meaningful form:

SQL Server Version Microsoft SQL Server 2019 (RTM) - 15.0.2000.5
Processor Architecture X64
SQL Server Build Date Sep 24 2019 13:48:23
Copyright Statement Copyright (C) 2019 Microsoft Corporation
SQL Server Edition Express Edition (64-bit)
Operating System Version Windows 10 Enterprise 10.0 (Build 18362: )

Using Console/Command Prompt

Command prompt simulates the input field in a text-based user interface screen with the Windows GUI. It can be used to run commands and carry out advanced administrative tasks.

We can find the SQL Server version using console using the below steps:

  1. Open the console/command prompt (Start> Search CMD and click Enter).
  2. Type the below command:

Here we need to change the name in place of servername and instancename.

  1. Type the SELECT @@VERSION and click Enter.
  2. Type GO and click Enter.

The following image shows the SQL Server version on the cmd screen. We can repeat this process in other instances.

How to find SQL Server Version

Using SQL Server Management Studio

SSMS is a valuable tool for managing the SQL Server installation. When we open SSMS for any SQL Server instance, we can see the product version appears under the Object Explorer tab between the parentheses. We can identify the SQL Server product version by this set of numbers. See the below image where the product version is shown on the red rectangular box:

How to find SQL Server Version

Let us convert this meaningless set of numbers into a meaningful product version. The product version is specified as 15.0.2000, which we convert into more meaningful and useful information. In this set of numbers, the first two digits (15) indicate the numeric definition of the SQL Server product name.

We can find the SQL Server version against the product name with the help of the following table quickly:

SQL Server Product Name SQL Server Product Version
SQL Server 2019 15.0.xxxx.xx
SQL Server 2017 14.0.xxxx.xx
SQL Server 2016 13.0.xxxx.xx
SQL Server 2014 12.0.xxxx.xx
SQL Server 2012 11.0.xxxx.xx
SQL Server 2008 R2 10.5.xxxx.xx
SQL Server 2008 10.0.xxxx.xx
SQL Server 2005 9.0.xxxx.xx
SQL Server 2000 8.0.xxxx.xx

However, the first two digits provide the product name, and we can get more information about the SQL Server instances. The remaining numbers (0.2000) determine the service pack and cumulative upgrade levels details. We can get more details regarding the remaining numbers; we need to visit the BuildNumbers website:

How to find SQL Server Version

Using PowerShell

PowerShell is a powerful cross-platform tool for automation tasks made up of a command-line shell, a scripting language, and simplifying configuration. It can automate almost every Windows, Linux, and macOS activity, including Active Directory and Exchange. Microsoft has two modules of SQL Server PowerShell, but both are not installed with SSMS starting with version 17.0:

  1. SqlServer: newer, requires PowerShell 5.0
  2. SQLPS: no longer being updated

We can also find the installed SQL server version using PowerShell by entering the following command:

Using Log Files

We can also find the SQL Server version in the SS error logs file. This file can be used to troubleshoot SQL Server issues because it records user-defined events as well as specific device events. It will also help us to get SQL Server version details. The first few lines of this file provide detailed information regarding the SQL Server's version.

The error log is located in "Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG" by default. However, we can also have an option SERVERPROPERTY function to get the location of the error log file. We can find the position of the error log file using the following query:

It will display the below output:

How to find SQL Server Version

Now, we can open the error log file in the given location. Here we can see the detailed information about SQL Server build.

How to find SQL Server Version

We can also read the error log by using the stored procedure sp_readerrorlog. The first line of its output shows the SQL Server instance details:

How to find SQL Server Version

Using SQL Server Properties

We can also use the SQL Server properties method to get the SQL server version information. It is one of the easiest methods to get the SQL server version details.

In this method, we need to navigate to the C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn. Now, search the sqlservr executable file, right-click on it to open the context menu, and select the Properties option. We will get the wizard where we will click the Details tab to see the product version and product name.

How to find SQL Server Version

Conclusion

This article will explain various methods to check the SQL Server version and learned how to convert them into meaningful information.







Youtube For Videos Join Our Youtube Channel: Join Now

Feedback

  • Send your Feedback to feedback@javatpoint.com

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA