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:

• Connect #1575257 : New DMV to expose various bits of @@VERSION

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 ProductMinorVersion (0).)

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 ProductVersion):

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.