It Used To Be
That when you wrote scripts that had to work across different versions of SQL Server, you were pretty safe.
Microsoft would release a new version, or a Service Pack that had something new in it, and it wouldn’t start showing up in older versions.
That’s not so true anymore. Microsoft has been doing some awesome work to add and back port new features. It’s doubly true when you’re dealing with cloud products (I’m looking at you, Azure SQL DB) where who knows what’s going to be or not be there, what version numbers will be, or anything like that. It’s the Wild West.
Which means if you write the kind of scripts that might throw errors if they go looking for a DMV or column that might not be there, you constantly have to figure out which major and minor versions you can go look for stuff in, often using dynamic SQL.
You still have to use dynamic SQL. That much is obvious. But it doesn’t make sense to do stuff like this anymore:
DECLARE @ProductVersion NVARCHAR(128),
@ProductVersionMajor DECIMAL(10, 2),
@ProductVersionMinor DECIMAL(10, 2);
SET @ProductVersion = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128));
SELECT @ProductVersionMajor = SUBSTRING(@ProductVersion, 1,CHARINDEX('.', @ProductVersion) + 1 ),
@ProductVersionMinor = PARSENAME(CONVERT(VARCHAR(32), @ProductVersion), 2)
Why? Because all your code is going to do something like this:
IF (@ProductVersionMajor = 11 AND @ProductVersionMinor >= 6020)
OR (@ProductVersionMajor = 12 AND @ProductVersionMinor >= 5000)
OR (@ProductVersionMajor = 13 AND @ProductVersionMinor >= 1601)
OR (@ProductVersionMajor >= 14)
This is gonna be a real pain in the butt to keep up with. Stuff can get released in Cumulative Updates that’s brand spanking new.
Here’s an example from 2017 CU3. That got backported to 2016 SP2.
Awesome! Unless your code looks like… that. Up there.
It’s a whole lot more reliable (and a whole lot less work) to explicitly check for new things in DMVs, and use that to inform your code what to look for.
SELECT OBJECT_NAME(ac.object_id) AS object_name, *
FROM sys.all_columns AS ac
WHERE ac.name IN ('total_spills', 'last_spills', 'min_spills', 'max_spills')
AND OBJECT_NAME(ac.object_id) = 'dm_exec_query_stats'
For example, this just makes sure all four columns are in dm_exec_query_stats:
DECLARE @tempdb_spills BIT = 0;
IF 4 = ( SELECT COUNT(*)
FROM sys.all_columns AS ac
WHERE ac.name IN ( 'total_spills', 'last_spills', 'min_spills', 'max_spills' )
AND OBJECT_NAME(ac.object_id) = 'dm_exec_query_stats' )
SET @tempdb_spills = 1;
Then your dynamic code becomes a bit simpler, too:
IF @tempdb_spills = 1
SET @sql += N' total_spills, last_spills, min_spills, max_spills '
Now it doesn’t matter if Microsoft adds, removes, changes names, or definitions down the line. That stuff could normally cause problems or create a lot of busy work keeping dynamic SQL up to date. Now it doesn’t.
Now, there’s still a whole lot of version checking in our code in the First Responder Kit. Cleaning it up isn’t a priority, but not writing more code like that is.
And this won’t work for everything, unfortunately. For example, if there’s a bug in certain versions and you need to skip those, you still need to rely on version numbers. There are likely other examples where it won’t work out there, too.
Thanks for reading!
Brent says – another thing I love about this approach is that when Microsoft backports a feature to an older SQL Server version by way of cumulative update, you don’t have to go revise your scripts right away. If the field is there, you’ll use it, whenever it happens to show up.