New SERVERPROPERTY Options to Help Phase Out @@VERSION
As I mentioned in a previous post, the SQL Server team is interested in exposing more descriptive information about the patch level of a SQL Server instance. They described their initial plan in the post, "What build of SQL Server are you using?" I objected almost immediately, due to the fact that changing the shape of
@@VERSION could cause backward compatibility issues for any code that parses that information to determine the build or other information. I felt strongly enough about this that I turned to Connect, asking for a DMV to expose this information instead:
If you voted for this item, thank you, I appreciate your support. I have not given up on my idea, but as of the latest round of updates for SQL Server 2014,
@@VERSION still exposes this disruptive information. Which will break code like this:
DECLARE @bitness CHAR(3) = SUBSTRING(@@VERSION, CHARINDEX('(', @@VERSION) + 1, 3); IF @bitness = 'X64' BEGIN PRINT 'X64'; -- do something for X64 END ELSE BEGIN PRINT 'X86'; -- do something for X86 END
64-bit instances of 2014 < SP1 CU3 or < RTM CU10 will enter the branch for x64, while those with the latest updates will actually enter the branch for x86. Now, I am not advocating code like this or trying to justify not revisiting it, but I have it on good authority that several SQL Server environments will have a lot of code to update that matches this type of pattern.
On the plus side, they have added additional locations to retrieve it; namely, four new server properties, documented under the section Method 4 in KB #321185 : How to determine the version and edition of SQL Server and its components:
SELECT SERVERPROPERTY('ProductVersion'), -- pre-existing build string e.g. 12.0.4427.24 SERVERPROPERTY('ProductLevel'), -- what major branch? RTM / SPx / CTP SERVERPROPERTY('ProductUpdateLevel'), -- which cumulative update #? SERVERPROPERTY('ProductBuildType'), -- is this a GDR / QFE / COD update or hotfix? SERVERPROPERTY('ProductUpdateReference'); -- KB article describing current patch level
ProductBuild also seems to be a new property, but it isn't documented that way, and all it does is produce the 3rd octet of
ProductVersion, e.g. for 12.0.4427.24,
ProductBuild returns 4427. The first two octets can be returned using
ProductMajorVersion (12) and
Some sample results from some of the systems I had handy at the time of writing (knowing that many of these builds would simply return NULL for most of the properties, and omitting the unimportant 4th octet of
(hover for info)
As a note,
@@VERSION has been altered in this way going back to SQL Server 2008 RTM, but this didn't get put into SQL Server 2014 until the most recent CUs. For example, the SQL Server 2012 build above, 11.0.5636, has a service pack and an on-demand hotfix applied. While that build of SQL Server has not been updated to support these new
SERVERPROPERTY arguments, its
@@VERSION output shows that this instance has Service Pack 2 installed, has all of the fixes from Cumulative Update #8 (either by installing that update manually, or by virtue of installing a later GDR/QFE/COD hotfix), and that it has the on-demand connection pooling hotfix from KB #3097636:
Microsoft SQL Server 2012 (SP2-CU8-OD) (KB3097636) – 11.0.5636.3 (X64) Sep 18 2015 18:43:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 (Build 10240: ) (Hypervisor)
If this change to
SERVERPROPERTY gets back-ported to SQL Server 2012, then for any new builds, the information would also be exposed there.
Going forward, the new
SERVERPROPERTY output is how you should be deriving this information. This is especially true for any new code you think you might write specifically for extracting information out of the large
@@VERSION string, because the only thing that will stay consistent about that is that it will keep changing. I'm not entirely happy about that, but I think the decision has been made.
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, and serves as a community moderator for the Database Administrators Stack Exchange. 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.