Home > DBA Stuff, SQL Tips > SQL Tips – Different ways to get SQL Server Version

SQL Tips – Different ways to get SQL Server Version

Today I got an email form a newbee regarding some help in SQL Server.
His question was a typical “SQL Server Interview Question”: What are the various ways to get SQL Server version number?

So I researched a bit and come up with following different methods for the same, as follows:

–> Method #1:

select @@version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
	Jun 28 2012 08:36:30 
	Copyright (c) Microsoft Corporation
	Data Center Edition (64-bit) on Windows NT 6.1  
		(Build 7601: Service Pack 1) (Hypervisor)

–> Method #2:

	SERVERPROPERTY ('productversion') as ProductVersion, 
	SERVERPROPERTY ('productlevel')   as ProductLevel, 
	SERVERPROPERTY ('edition')		  as Edition
ProductVersion	ProductLevel	Edition
10.50.4000.0	SP2		Data Center Edition (64-bit)

–> Method #3:

	CAST(@@microsoftversion as binary(10))	as VerBinary,
	@@microsoftversion / 0x01000000		as VersionNumber1, 
	@@microsoftversion / power(2, 24)	as VersionNumber2, 
	@@microsoftversion & 0xFFFF			as ReleaseNumber
VerBinary		VersionNumber1	VersionNumber2	ReleaseNumber
0x0000000000000A320FA0	10		10		4000

–> Method #4:

EXEC xp_msver 'ProductVersion'
Index	Name		Internal_Value	Character_Value
2	ProductVersion	655410		10.50.4000.0

–> Method #5:

EXEC sp_server_info
attribute_id	attribute_name		attribute_value
1		DBMS_NAME		Microsoft SQL Server
2		DBMS_VER		Microsoft SQL Server 2008 R2 - 10.50.4000.0
500		SYS_SPROC_VERSION	10.50.4000

–> Method #6:
Check the INSTANCE name in SSMS Object explorer. It shown SQL Server Version Number in brackets, like: (SQL Server 10.50.4000 – DOMAIN\user).

–> Method #7:
Check by “SQL Server Features Discovery report”.
Go to Start Menu -> Pragram Files -> Microsoft SQL Server -> Configuration Tools -> SQL Server Installation Center (64-bit)
A window will open, click on Toolsat the left navigation bar, then click on “Installed SQL Server Features Discovery report” link.
This will open up a HTML page in web-browser, which looks like in the image below:

–> Method #8:
Simply, in SSMS go to menu, Help -> About.
You will get a pop-up window which shows version number of difefrent Components installed as a part of SQL Server installation.

  1. rajiarul@gmail.com
    January 17, 2013 at 3:52 am

    After SP1 of SQL server 2008 R2, the versions displayed by Select @@version and that displayed by running the SQL Server discovery report are different.
    Any ideas why ??

  2. rajiarul@gmail.com
    January 17, 2013 at 5:08 am

    To my previous comment about version/build numbers, here is an error when trying to apply a CU patch for SQL 2008 R2 SP1.

    A SQL Server update with a higher version has already been installed on SQL Server instance MSSQLSERVER, so the current SQL Server update cannot be applied. The version of the SQL Server update that is already installed is GDR 10.51.2550.0(10.51.2860.0) with a KBKB2716440 and the current SQL Server update is 10.51.2769.0 with a KBKB2544793.

    Even though in server management it displays 10.50.2550 – .

    Does not allow one to apply any patches.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: