How to check sql server version? (Various ways explained)

In this article, I will provide you the possible ways to find or check Microsoft sql server version, service pack etc installed on your pc/server. This is helpful for person like DBA and who want's to upgrade SQL server now, so to upgrade you must first know the current version and details of your service pack, so in that case this article would be helpful for you.

Although it shouldn't be difficult for anyone to find details of version but you should know the below listed various easy ways to determine it quickly.

Checking SQL server version using various methods

Now, I will provide you several possible ways to check sql server version and service pack, let's get started with it.

1. Using @@VERSION query

Start your SQL server management studio (SSMS), connect to your local database server and right-click on your Server for which you want to check version (if there are multiple SQL Server connected to SSMS) click "New Query", and excute the below query

SELECT @@VERSION

Output must be as below

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
	Feb 10 2012 19:39:15 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

as you can see in the above image we are getting complete details of our SQL server version which is 2012, it is 64-bit version.

You can also use below SQL Query to check sql server version

declare @sqlVers numeric(4,2)
select @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)

You will get output as 8.00, 9.00, 10.00 and 10.50, 11 for SQL 2000, 2005, 2008, 2008R2 and 2012 respectively

2. Using ServerProperty in query

Well this is another method to determine sql server version in detail running scripts, here is the sample script which you can run your SQL server Management studio

SELECT  
  SERVERPROPERTY('MachineName') AS ComputerName,
  SERVERPROPERTY('ServerName') AS InstanceName,  
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion,  
  SERVERPROPERTY('ProductLevel') AS ProductLevel;  
GO  

Output will be in tabular form with specific details which we have selected in the above query

ComputerName                   InstanceName                                                        Edition                                           ProductVersion              ProductLevel
DESKTOP-1PM1CJ9       DESKTOP-1PM1CJ9\SQLEXPRESS2       Express Edition (64-bit)      11.0.2100.60                       RTM

There are many more Server properties which you can get using the ServerProperty query in SQL server, here is the complete list for SERVERPROPERTY

3. Using system extended stored procedure

This is another good method which returns all the details of your SQL server version, try executing the master database stored procedure, in my case it was located in System database-> master -> Programmability -> sys.sp_server_info

Executing this stored procedure, as below

exec master.sys.sp_server_info

provide me lots of information in single query

4. Using SQL server properties

This is probably one of the easiest method to find out SQL server details. In this method we will check SQL server version through sqlservr properties.Here is the list of procedure you need to follow in this way to get all the details

• Navigate to C:\Program Files\Microsoft SQL Server\YourServer.SQLExpress\MSSQL\Binn (in my case it was C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS2\MSSQL\Binn)
• look for sqlservr executable file
• Right click the file and click Properties.
• Click Details tab
• Find product version and product name listed.

Here is the output for me

5. Using the Windows Command Prompt (CMD)

To determine what edition of SQL you are running, do the following:

1. Open a command prompt windows on the machine SQL is installed to (Start > Run, type cmd, hit enter)
2. Type SQLCMD -S servername\instancename (where servername = the name of your server, and instancename is the name of the SQL instance).The prompt will change to 1>
3. Type select @@version and hit <ENTER>.
4. At the 2> prompt type go and hit <ENTER>.

This will return the SQL version that is running on your server. If you have multiple instances repeat the process for each instance.

The servername mentioned above will be the name of the machine SQL is installed to. If you are unsure of your instance name do the following:

1. Open a command prompt window as described above.
2. Type services.msc.
3. Browse down to entries beginning with SQL.
4. There will be an entry for each instance called SQL Server (instancename).
5. Whatever appears in the parentheses is your instance name.

6. Using SQL CMD Utility

SQLCMD is a part of the SQL Server Client Tools, you can use it also to check sql server version

Example:

sqlcmd.exe -S ServerName\InstanceName -E -Q "SELECT @@VERSION"

sample output

7. Using Powershell

We can also check the installed SQL server version using Powershell, once you open PowerShell, you can simply use the below command

$inst = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
foreach ($i in $inst)
{
   $p = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
   (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Edition
   (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup").Version
}

Output:

OR

You can simply use the below command

Invoke-SqlCmd -query "select @@version" -ServerInstance "DESKTOP-DOG5T0Q\SQLEXPRESS"

Will return output as below

Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64)

You may also like :

Difference between Stored procedure and function in SQL Server (Lists differences and explains each of them separately.)

Restore (Import) database from .bak file in SQL server (With & without scripts)

How should i display first 10 records at a time in sql (paging in SQL)?

Aggregate Functions in SQL Server (SUM, AVG, COUNT, MIN, MAX)

That's it, we are done, these were some of the widely used ways to check version of SQL Server.