SQL Server Version Detection

SQL Server
21 Comments

Every now and then, you need to figure out which version of SQL Server you’re using without knowing in advance. This might happen in a script (like sp_BlitzCache) or you might be using it in a migration.

Getting the SQL Server Version with @@VERSION

THe first thing that comes to mind is @@VERSION. On my SQL Server 2014 RTM installation, this returns an ugly string. Unless you like parsing multiline strings in T-SQL, you’re going to be left using LIKE on a hideous string like this one:

Imagine if you needed to get the build number of SQL Server out of there to validate against a list of builds to see if you had the current version of SQL Server installed. Pulling the build out of the major.minor.build.revision string isn’t easy when you first have to pull that string out of a bigger string.

There’s a better way than mucking around in @@VERSION.

Getting the SQL Server Version with SERVERPROPERTY

The SERVERPROPERTY built-in function has a lot of great functionality. We’re only going to concern ourselves with the ProductVersion portion of SERVERPROPERTY. How do we use it?

That should return nothing but the major.minor.build.revision for the current version of SQL Server. In my case, this returns: 12.0.2000.8.

Rather than parse the values myself, I created a temporary table with computed columns:

The common_version column will display the version of SQL Server as a floating point number – 12.0 for SQL Server 2014 or, in the case of SQL Server 2008R2, 10.5. When you’re targeting scripts for specific families of SQL Server, knowing the high level version can make scripting a lot easier.

What’s the final output look like for me?

version common_version major minor build revision
12.0.2000.8 12.0 12 0 2000 8

There you have it

That’s really all there is to finding and parsing the SQL Server version. Just a simple insert into a temporary table and a few computed columns and you’re ready to go.

Previous Post
Microsoft Ignite SQL Server Keynote Liveblog #MSIgnite
Next Post
Forcing Join Order Without Hints

21 Comments. Leave new

  • I’m using SELECT @@MICROSOFTVERSION / POWER(2, 24) for major version — no strings parsing.

    Reply
  • Any reason for the CONVERT(VARCHAR(32), version)? The best I can come up with is to support SQL Server 6.5 (which should be a varchar(30) according to BOL), but I don’t think SERVERPROPERTY existed back then (can’t find any docs on this).

    Reply
  • Henry Stinson
    May 5, 2015 6:59 pm

    Why not just use a CTE and avoid creating and dropping a temp table?
    ; WITH cte as
    (
    SELECT CAST(SERVERPROPERTY(‘ProductVersion’) AS NVARCHAR(128)) AS Version
    )
    SELECT SUBSTRING(version, 1, CHARINDEX(‘.’, version) + 1 ) AS common_version,
    PARSENAME(CONVERT(VARCHAR(32), version), 4) AS major,
    PARSENAME(CONVERT(VARCHAR(32), version), 3) AS minor,
    PARSENAME(CONVERT(varchar(32), version), 2) AS build,
    PARSENAME(CONVERT(VARCHAR(32), version), 1) AS revision
    FROM cte;

    Of course the common_version parse would not work quite right if the common version were like 14.10….

    Reply
  • This is also useful for SQL build versions: http://sqlserverbuilds.blogspot.com/

    Reply
  • You’ll probably beat me to death for posting this, but… it doesn’t work with SQL 2000 🙂
    8.00.2066 gives
    common_version = 8.0, major = NULL, minor = 8, build = 00, revision = 2066

    Reply
  • PowerShell has a nice alternative method:

    dir SQLSERVER:\SQL\server_1 | select version, VersionMajor, VersionMinor, Buildnumber

    …which you can, if you want, put in a ‘for’ loop

    foreach ($ServerName in (“server_1”, “server_2”, “server_3”)) { dir SQLSERVER:\SQL\$ServerName | select version, VersionMajor, VersionMinor, Buildnumber }

    I can’t see how to retrieve the Revision Number though

    Reply
  • Bill Bergen
    May 7, 2015 7:41 am

    Hi Jeremiah
    All of your work is OUTSTANDING…the mark of a true professional….as far as executing powershell from sql server I have included the following in the hope it sheds light on how I accomplish it….this is used multiple times during the day to check free space on my drives….enjoy and keep up the outstanding work
    declare @sql varchar(8000)
    set @sql = ‘powershell.exe -c “Get-WmiObject Win32_Volume”‘
    insert into #wmi_drive_info (all_data)
    EXEC xp_cmdshell @sql

    Reply
    • I only just saw the comment above. Rather than using Powershell, this will provide you the same data:

      insert into #wmi_drive_info exec master..xp_fixeddrives

      It also saves you from having xp_cmdshell enabled (if that’s all you use it for).

      Reply
  • Hi Jeremiah,

    Related question…from what we are seeing, it looks like when Microsoft pushes a security hotfix, the CU that is installed is masked. That is, if you install the July QFE hotfix for SQL Server 2012, version 11.00.5613, you really have no idea if it is running CU1, CU2, … CU6. They all return 11.00.5613. CU7 came out later, so if you just install it (11.00.5623), you are ok, but if you were using a lower CU, you can’t programmatically know which one. Is that what you’ve found? thanks in advance for any insight.

    Reply
    • You aren’t going to be excited about my insight – I’ve never seen this.

      Reply
      • Hi Jeremiah. Just a follow up. We talked to our account rep and found, buried in the fine print of the security fix, that if you apply it, they bump you to CU6 (in this particular case of 2012, SP2). So the security fix actually incorporates CU6 as well. Unexpected, but at least it helps answer what version we’re running.

        Reply
  • Hi, guys. 5 different ways to get SQL Server Version from awesome Microsoft KB321185 (see How to determine which version and edition of SQL Server Database Engine is running chapter): https://support.microsoft.com/en-us/kb/321185

    and my version of SQL Server Version on Github:
    https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Version.md

    Reply
  • Compare:

    CONVERT(HIERARCHYID, N’/’ + CONVERT(NVARCHAR, SERVERPROPERTY(‘ProductVersion’)) + ‘/’) < CONVERT(HIERARCHYID, N'/10.50.4000/')

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu