@@VERSION and SERVERPROPERTY – What changed?

  • Article

 

Cross post with aka.ms/sqlserverteam

With the recent release of SQL Server 2014 SP1 CU3 and SQL Server 2014 RTM CU10, you will notice another change in the output @@VERSION.

The motivation to introduce this change in the @@VERSION output string is completeness of the string information itself. This has been true over the years, as we have been introducing changes since at least SQL Server 2008 – when we added Service Pack labels. We then also added hypervisor related information in SQL Server 2008R2. Now, for SQL Server 2012 and 2014 we are adding Cumulative Update information.

We realize it is common for many SQL Server users asking for assistance in a forum or DL, to post (or be asked to post) the output of @@VERSION. The same also happens with customers calling Microsoft Support. In turn, this allows others (including Microsoft Support Engineers) to understand several important characteristics of the instance, and better contextualize any reply.

We also realize that some scripts rely on parsing @@VERSION to come up with information about a SQL Server instance, and such practice goes back many years. So with each change to @@VERSION, scripts relying on such parsing techniques also needed changes over the years.

For a consistent programmatic access to the same information in the @@VERSION string, SERVERPROPERTY provides all the coverage and does not require string parsing. Scripts relying on SERVERPROPERTY are therefore more resilient to any changes.

Take ProductVersion for example - this could also be parsed to get information and several scripts rely on this property also.

However, we realize that a user might need to access the components of ProductVersion separately, in a way that does not require parsing. That is why we are also introducing ProductMajorVersion, ProductMinorVersion and ProductBuild properties (see below), but not changing ProductVersion itself.

clip_image002

We are also introducing ProductUpdateLevel, to compliment the information in ProductLevel, and even ProductUpdateReference (corresponding KB article) and ProductBuildType (is the build a CU, GDR or OD hotfix) as seen below.

clip_image004

With these additions we achieve better consistency between the @@VERSION string information, and any requirement to retrieve a part of that string, but using SERVERPROPERTY instead.

All the changes to SERVERPROPERTY are documented in Books Online: https://msdn.microsoft.com/en-US/library/ms174396.aspx. Note this will also apply to SQL 2016, in an upcoming CTP.

Pedro Lopes (@sqlpto) - Program Manager