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.
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:
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
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?
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:
Rather than parse the values myself, I created a temporary table with computed columns:
CREATE TABLE #checkversion (
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)) ;
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?
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.