Archive for October 5, 2012

SQL Tips – Different ways to get SQL Server Version

October 5, 2012 2 comments

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.