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 crazy about the delivery. The way that they have addressed this is they are changing the output of SELECT @@VERSION;. Currently it has output like this:
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):
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:
This can potentially break existing scripts that rely on parsing the output of @@VERSION.
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 Connect #1575257 : New DMV to expose various bits of @@VERSION.
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.