SQL Server Version Detection
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:
|
1 2 3 4 5 |
Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) |
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?
|
1 |
SELECT CAST(SERVERPROPERTY('ProductVersion') as nvarchar(128)) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE #checkversion ( version nvarchar(128), common_version AS SUBSTRING(version, 1, CHARINDEX('.', version) + 1 ), major AS PARSENAME(CONVERT(VARCHAR(32), version), 4), minor AS PARSENAME(CONVERT(VARCHAR(32), version), 3), build AS PARSENAME(CONVERT(varchar(32), version), 2), revision AS PARSENAME(CONVERT(VARCHAR(32), version), 1) ); INSERT INTO #checkversion (version) SELECT CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)) ; |
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.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
I’m using
SELECT @@MICROSOFTVERSION / POWER(2, 24)for major version — no strings parsing.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).
No reason for it. Probably just “creativity” on my part.
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….
I use a temporary table because I need to use these data values over and over again.
This is also useful for SQL build versions: http://sqlserverbuilds.blogspot.com/
Thanks. Sadly, this link won’t help me get the current version of SQL Server via T-SQL. I also prefer sqlserverupdates.com because it points me to the current CU or KB.
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
Nah, no beatings. But I will comment that I’m not going to write new code and guarantee that it works on an unsupported version of SQL Server.
Come on, it’s *just* 15 years old 🙂
Don’t worry, on April 12 of 2016 I’ll start saying the same thing about code samples not working on SQL Server 2005.
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
I can’t see how to call PowerShell from inside of T-SQL. So there’s that, too.
Good point!
I must admit I tend to always have a PowerShell window open these days, alongside SSMS
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
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).
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.
You aren’t going to be excited about my insight – I’ve never seen this.
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.
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
Compare:
CONVERT(HIERARCHYID, N’/’ + CONVERT(NVARCHAR, SERVERPROPERTY(‘ProductVersion’)) + ‘/’) < CONVERT(HIERARCHYID, N'/10.50.4000/')
I found it easier this way to code for service pack features for multiple SQL versions.
For example, say I want all the new performance counters and know they were released on a particular service pack on multiple SQL versions. So if I code for greater than SQL Server 12 SP3, or greater than SQL 2014 SP2, OR SQL 2016 + CU9 or better I could do this.
DECLARE @sql_version INT = CONVERT(INT,LEFT(REPLACE(CAST(SERVERPROPERTY(‘ProductVersion’) AS CHAR(15)),’.’,”),7))
SELECT
CASE
WHEN
(@sql_version BETWEEN 1106020 AND 1200000) OR
(@sql_version BETWEEN 1205000 AND 1300000) OR
@sql_version >= 1301601
THEN
‘Execute code block’
ELSE
‘Execute code block’
END