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

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:

SELECT 
	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:

select 
	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.

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

    Hi
    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.

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

    Hi
    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 ??

  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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: