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

How to check sql server version? (Various ways explained);- In this article, We will provide you the possible ways to find or check Microsoft sql server version, service pack etc installed on your pc/server.

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network.

check sql server version

This is helpful if you 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.

Checking SQL server version using various methods

Now, I will explain possible ways to determine sql server version and service pack, let’s get started with it.

1. Check sql server version using @@VERSION query

Start your SQL server management studio, connect to the server and right-click on your Server, 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. Check SQL server version 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. Check SQL server version 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. Check SQL server version 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.

 

5. Check sql server version using the Windows Command Prompt

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