What build of SQL Server are you using?

As a person administering or supporting a SQL Server install base you will get asked this question very frequently: Which build of SQL Server are you using? If all the SQL Server instances that you manage are at the same build level then you will know exactly what that build number corresponds to. Also you need to be aware of the translation of a specific build number to its corresponding service pack level and cumulative update/security update levels. But in reality you are managing multiple versions of the product at different service pack levels and cumulative update/security update levels. So normally what people do is either go to the internet to query for the build number or create quick reference cheat sheets for some frequently used build numbers in their organization.

How will you react if we told you that starting today you will not need to worry about all that anymore? Extremely excited? Yes. We are too. Download and install the CU released today and you will notice what we are talking about. Starting with this month CU – SQL Server 2012 Service Pack 2 Cumulative Update 7, you will notice a very visible change in 2 places: SELECT @@VERSION and SQL Server Error log.

Here is a quick snippet of the outputs with the change highlighted:

So now with this change you will be able to quickly identify the servicing update level of your SQL Server installations. You will be able to determine the version of the product, the service pack level, cumulative update level or security update level.

You will notice that this change will propagate to all future servicing updates released from this point onwards. This was the outcome of great collaboration between the product group and the support team combined with great feedback from the community members. Keep your feedback flowing and we can continue to enhance this information and make it available through other interfaces that exist in the product to make it easier for you to perform identification and inventory management easier.

Suresh Kandoth [SQL Server Escalation Team – Microsoft]

Comments

• Anonymous
  July 20, 2015
  SQL, Structured Query Language - SQL obtains patterns from queries and statistics on how often they are used; neither the queries, nor patterns, nor statistics have anything in common with data itself, they are EXTERNAL. I, however, discovered and patented how to structure any data without SQL, the queries - INTERNALLY: Language has its own INTERNAL parsing, indexing and statistics and can be structured INTERNALLY. (For more details please browse on my name ‘Ilya Geller’.) For instance, there are two sentences: a) 'Sam!’ b) 'A loud ringing of one of the bells was followed by the appearance of a smart chambermaid in the upper sleeping gallery, who, after tapping at one of the doors, and receiving a request from within, called over the balustrades -'Sam!'.' Evidently, that the 'Sam' has different importance into both sentences, in regard to extra information in both. This distinction is reflected as the phrases, which contain 'Sam', weights: the first has 1, the second – 0.08; the greater weight signifies stronger emotional ‘acuteness’; where the weight refers to the frequency that a phrase occurs in relation to other phrases. SQL cannot produce that statistics - SQL is obsolete and out of business.

• Anonymous
  July 21, 2015
  Awesome. This has been needed for a long time. I do have one addition request--put the split build number in a system view like below, so it can easily be gathered programatically. sys.extendedversion Major Version Minor Version Build Suffix

  • Anonymous
    July 24, 2016
    The comment has been removed

• Anonymous
  July 21, 2015
  This information shouldn't be embedded within a string where it's only moderately useful. This either needs to be a ServerProperty or accessible separately in a DMV.

• Anonymous
  July 21, 2015
  Good work. Is there any chance that Microsoft /CSS could maintain a list of all versions , build numbers , descriptions and whether they have have vulnerablilities . I know there are various 3rd party sites that have this but I think Microsoft should provide / maintain this. Also it would be good to be able pass a version number to web service to get details for earlier versions than above I knocked something rough and ready up as per below : a) Powershell $url = "accountsweb3.azurewebsites.net/.../GetByBuildURI Invoke-RestMethod -Method Get -Uri $url b) web accountsweb3.azurewebsites.net/.../GetByBuildURI and also have Microsoft maintain a Spreadsheet / TSQL (with merge statements so can easily be updated) so that can update Internal Inventories* when new builds are released.   Andy

• Most big companies I've worked at have their own internal inventory / CMDB with SQL Server Versions numbers stored in. This would allow easy Join to SQL Server versions to produce stats on how many servers are vulnerable / lifecycle etc.  I know this is prob available in SCCM and /or SCOM ..

• Anonymous
  July 21, 2015
  Maybe a better idea to use a different mechanism like a DMV because people may be using the @@Version and now need to change its use.

• Anonymous
  July 22, 2015
  Thank you for the valuable feedback. Yes, SERVERPROPERTY already provides build information and is a natural place to start providing this extended information - update level and related information. We will update you when that appears in the product and we start exposing the CU information in SERVERPROPERTY. Over the years we have continued to enhance the information that is provided in @@VERSION. In SQL 2000 and 2005, we provided only the major product information string. In SQL Server 2008, we added the Service Pack labels. In 2008 R2, we added information about virtualization. For programmatic access, SERVERPROPERTY already provides ProductVersion and ProductLevel. Those would be better choice to query than using @@VERSION to obtain build number information using string parsing.

• Anonymous
  August 24, 2015
  Suresh, Would you happen to know if this change will port to SQL 2014? Currently SQL 2014 SP1 still shows Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun  9 2015 12:06:16 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 10240: ) (Hypervisor)

• Anonymous
  September 15, 2015
  Obviously a nice piece of information if it wasn't for the fact that it breaks a lot of applications that rely on the format of "select @@version".

• Anonymous
  March 14, 2016
  I would be enormously excited - if I was a SQL administrator.I am not enormously excited - because I am a SCCM administrator.Why, oh why, oh why does SQL provide no sensible means of identifying itself, other than within SQL itself? Who in the SQL Program Team decided that it was still acceptable to slap non-SQL admins in the face with Method 2 from https://support.microsoft.com/en-us/kb/321185 ?!?Seriously, why does SQL offer absolutely nothing to System Center - Microsoft's own systems management suite - to help gather inventory and populate a CMDB, or generate a licensing report?If anyone does know of a way to gather SQL inventory using SCCM, I'd love to hear about it - but I suspect I won't unless I stump up some cash first. Thanks a bunch, Microsoft.

• Anonymous
  September 18, 2016
  Why upper version not have this change?SELECT @@Version Output:-Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright (c) Microsoft Corporation