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:

image

image

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 (11)

  1. Ilya Geller says:

    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.

  2. Joseph Dantoni says:

    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

    1. Dean says:

      As per other suggestions, please put this information into a system view that returns each value in a separate column. Then it would be really useful! As it stands, to do any kind of analysis or automation we have to parse these strings which is cumbersome and error-prone. Thanks.

  3. Mike Fal says:

    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.

  4. Andy Ball says:

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

  5. Chris Wood says:

    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.

  6. Suresh Kandoth says:

    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.

  7. Chirag Shah says:

    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)

  8. Urban says:

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

  9. p@man says:

    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.

  10. Ramdas says:

    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

Skip to main content