I have an aversion to @@VERSION
Published On: July 22, 2015
Categories: SQL Server, SQL Server Builds 4
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:
What build of SQL Server are you using?
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:
- This can potentially break existing scripts that rely on parsing the output of
- 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
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:
|Licensing||VARCHAR(32)||< would say core-based when true >|
|OperatingSystem||VARCHAR(32)||Windows NT 6.3|
|OperatingSystemServicePack||TINYINT||< NULL >|
|LegacyVersion||VARCHAR(2000)||< match @@VERSION output >|
Yes, some of this information is available elsewhere, like various spots in
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.
Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com. Aaron's blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.