SentryOne Team Blog (blogs.sentryone.com)

I have an aversion to @@VERSION

I've been posting about SQL Server service packs, cumulative updates, and hotfixes for years. One of the things that has been sorely lacking in SQL Server all this time is the ability to quickly determine which specific service pack or cumulative update is installed. Oh sure, @@VERSION has a build number, and you can cross-reference that against a number of sites that try to keep tables mapping build numbers to KB articles. But that is tedious and unreliable.

This week the SQL Server team has revealed that they plan to address this issue. See the following post:

I like the spirit, but not the delivery. The way that they have addressed this is they are changing the output of SELECT @@VERSION;. Currently it has output like this:

Microsoft SQL Server 2014 – 12.0.4416.0 (X64)
    Jun 11 2015 19:18:41
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 (Build 10240: )

As of SQL Server 2012 Service Pack 2 Cumulative Update 7, the output now looks like this (and unless you can help me convince them otherwise, this pattern will surely follow for subsequent cumulative updates for SQL Server 2014 & SQL Server 2016):

Microsoft SQL Server 2012 (SP2-CU7) (KB3072100) – 11.0.5623.0 (X64)
    Jul  9 2015 12:03:12
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 (Build 10240: )

I've highlighted it to make it obvious, but they've added the SP/CU details, as well as the relevant KB, to the first line (not to be confused with service pack information you might see in the fourth line, which is about the operating system, not SQL Server).

So what's the problem? Well, IMHO, it's two-fold:

  1. This can potentially break existing scripts that rely on parsing the output of @@VERSION.
  2. This is 2015. Why are we still presenting data in string output that requires barbaric string parsing to automate?

My request is simple.

 
First, don't touch @@VERSION.

Second, add a new DMV, say sys.dm_server_version, which exposes all of this information in tidier columns with proper data types. Of course, it should still expose a column containing a big ugly string exactly as is currently produced by @@VERSION. Here is what the DMV columns would be, and sample output based on the string from the Microsoft blog post that I adapted above:

Column Data type Output
MajorVersion TINYINT 11
MinorVersion TINYINT 0
Build SMALLINT 5623
BuildSuffix TINYINT 0
FullVersion VARCHAR(14) 11.0.5623.0
ServicePack TINYINT 2
CumulativeUpdate TINYINT 7
UpdateKBArticle VARCHAR(12) KB3072100
BuildDate DATETIME 2015-07-09 12:03:12
Edition VARCHAR(32) Developer
Licensing VARCHAR(32) < would say core-based when true >
Bits TINYINT 64
OperatingSystem VARCHAR(32) Windows NT 6.3
OperatingSystemBits TINYINT 64
OperatingSystemBuild INT 10240
OperatingSystemServicePack TINYINT < NULL >
Hypervisor BIT 0
LegacyVersion VARCHAR(2000) < match @@VERSION output >

Yes, some of this information is available elsewhere, like various spots in SERVERPROPERTY and sys.dm_os_sys_info. So if the powers that be want to leave some of this out of this new DMV, that's fine – but I think the new DMV is a much cleaner approach than injecting new data into a long-established built-in function. If you agree, please vote for my Connect item:

I'll be honest, I had long given up any notion that this information would be exposed by the SQL Server metadata, but their willingness to muck with @@VERSION has given me new hope.

Comments ( 5 )

Leave A Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.