It’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:
[sourcecode language=’sql’]SELECT @@VERSION[/sourcecode]
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
[sourcecode language=’sql’]EXEC xp_msver[/sourcecode]
Sample output from my SQL Server 2012 install…
Method 3 – “SELECT SERVERPROPERTY”
You run SELECT SERVERPROPERTY against your server and pass in some parameters to get back the information you want.
[sourcecode language=’sql’]SELECT
SERVERPROPERTY(‘productversion’) AS ProductVersion
, SERVERPROPERTY (‘productlevel’) AS ProductLevel
, SERVERPROPERTY (‘edition’) AS ProductEdition[/sourcecode]
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.
Thank you Andy. I inherited very old that parsed the @@Version to get the Product Version to determine whether to use the AppRole. Don’t know how old it is but it expects the first digit to be between 2 and 8 to set it.
I want to update the check because in some dark, distant time, in a galaxy far far away, SP_SetAppRole will be replaced.