How to Determine SQL Server Version Number In Various Ways

determine sql server versionIt’s important to be able to determine SQL Server version number. DBA’s need to know what SQL Server version numbers they are supporting so that during patching windows, they can source and apply the correct patches or service packs.

This information is useful as a reference to what issues may be contained in a certain build that could affect production. The DBA can quickly reference the latest build list with their current SQL Server version number and work out where their build falls on the list and which patches to apply.

Here is the latest SQL Server 2012 build list which also contains a link to lists for previous versions. Please note that you have to register on SQL Server Central if you are not already a member to view this. (I’d recommend that you do register as it is a brilliant forum 🙂 )

Ways to determine SQL Server version number

All of these methods are straightforward so lets take a look.

Method 1 – “SELECT @@VERSION”

Simply open up a new query and run the following:

SELECT @@VERSION

The output will look something like:

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.1  (Build 7601: Service Pack 1)

Method 2 – “xp_msver”

I like this one as it provides quite a lot of useful information about your SQL Server installation and server configuration

EXEC xp_msver

Sample output from my SQL Server 2012 install…
determine sql server version number

Method 3 – “SELECT SERVERPROPERTY”

You run SELECT SERVERPROPERTY against your server and pass in some parameters to get back the information you want.

SELECT
SERVERPROPERTY('productversion') AS ProductVersion
, SERVERPROPERTY ('productlevel') AS ProductLevel
, SERVERPROPERTY ('edition') AS ProductEdition

Which produces the following output…

ProductVersion       ProductLevel         ProductEdition
-------------------- -------------------- ------------------------------
11.0.2100.60         RTM                  Express Edition (64-bit)

(1 row(s) affected)

Method 4 – Use Management Studio (Client Tools)

You can access the Help menu in Management Studio and goto “About” to ascertain which SQL Server versions of the client tools you have installed on your workstation or SQL Server.

Summary – How to determine SQL Server version

So there are some ways to find out which versions of your client tools and server engine you are running. There may be others and if you know of them, please do leave a comment.

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

Speak Your Mind

*


*