SQL Server Version Numbers – Do they matter?

It’s a trick question, yes. Your SQL Server version matters! In this post I’ll tell you how to check your version, talk about what makes up the version number and talk about why it matters so much and why you should get a plan in place to regularly upgrade or update SQL Server. This post is part of the “Straight Up SQL Server Tips” posts.

About SQL Server Versions

SQL Server versions effectively are made up of three main components. (Using what I describe the parts of the SQL Version number here to describe the parts that make up the release you have installed.)

The Major Release – Is this SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016? (I really hope it isn’t SQL Server 6.5 or 4.2.. Or even 2000). This is the major release of SQL Server.

The Service Pack Level – Is this RTM (Release to manufacture – the first public for production release of the major release version)

The CU/Hotfix/Update Level – Sometimes between the SP levels security (though rarer than Oracle security patches, a testament to the team at Microsoft) updates are released. Sometimes there are hotfixes or other updates to fix something specific. Periodically, Microsoft rolls these various updates they release into cumulative updates. The name is as it sounds – these are cumulative. SP2 CU3 containts the fixes found in SP2 CU2, for instance. But they don’t cross SP levels. RTM CU12 of a major release won’t have changes in SP1 CU10, for instance.

Knowing where you stand here is really important. As I’ll show you later in the post, you can miss some important updates or be out of compliance really quick. One of the things Straight Path has the privilege of doing is a lot of SQL Server Health Assessments – our tool allows us to have an affordable “no-brainer” pricing structure ($997 for up to three instances) – this means that we end up seeing a lot of environments all over the spectrum. Being on a version of SQL Server or a SP or update level that is out of support or, worse, has a potential bug is probably among the top 3-5 findings we encounter. While I’d love everyone to get a Health Assessment and review their own environment and fix what’s broke or get that “good housekeeping stamp of approval” or the great environments, this is a finding that I’d be happy if everyone just sorted out on their own. Hence the rest of this post showing you how to find out where you are and telling you what you can do about it.

[optin-monster-shortcode id=”fgh1apjqzr102u8l”]

Version Numbers

There are two ways you can query this:

SELECT @@version

This function ultimately returns the SQL Server version number. In various major releases it will even  tell you which major version in the results before the SQL version number, it may report security hot fixes and SP level, but the most important part of the release is the version number result – for instance, 9.00.5057.00 or 13.0.4001.0 for SQL Server 2005 SP4 with a security fix and SQL Server 2016 SP1 respectively.

Or, you could query the SERVERPROPERTY values:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),SERVERPROPERTY ('edition')

This will return three columns (there are more available in SERVERPROPERTY function also. The version numeric (The SQL Server version number) The Service Pack Level (productlevel) and the edition (enterprise, standard, web, express, etc)

I like to always go by the version number. So that’s how you gather the data. Now what do you do with that?

Learn About SQL Server Versions

It’s good to know where you stand. There are two excellent sites for this. One that goes back to SQL Server 7.0 and sort of has “just the facts, m’am” – this is SQL Server Builds. This site lists just about every version number you could possibly see. It shows you where you are.  And it shows you the release date of your version. Generally speaking you should try and be on the latest SP, and Microsoft even recommends keeping up with CUs. More importantly, you may be on a version of SQL Server that is no longer in support. Or you could be on a SP or CU level that has known issues.  Starting with SQL Server 2012, the folks over at Sentry One ( I still call them SQL Sentry 😉 ) are maintaining a list of version numbers and updates – and more importantly – tracking known issues to a point with updates. So clicking over to their build lists will help you see if you are on a release with known issues or are pretty far back.

You can search Microsoft for “Lifecycle Support SQL Server 2005” to see when SQL Server 2005 mainstream support ended, or change those numbers to see more. There ar really two dates that we care about here. There is End of Mainstream Support (support is still available extended, but it is best effort and typically more expensive) and end of extended support (it’s not supported anymore). I have a small table here of the relevant versions based on what I encounter in the wild (sadly I still do bump into SQL Server 2000 out there – it’s rare though thankfully).

I see a LOT of SQL Server 2005 out there. It’s out of extended support. I also see a lot of SQL Server 2008. It’s out of mainstream support and into extended support. It’s really probably time to upgrade. Straight Path can help there, but however you do it – you should really look to get on the program with upgrades.

Fix Your SQL Server Version

There are a few schools of thought about this topic. Some people rush out to deploy the latest and greatest RIGHT AWAY.  Every so often, though, as that Sentry One link shows, a bad update can come out. For example, in SQL Server 2012 SP1 (as released) – a certain set of circumstances can cause corruption when doing an online index rebuild! That’s sort of a big deal. I’m still a HUGE FAN of being on the latest, but I am more a fan of waiting. What I tend to advise clients is, when possible, try and be on the latest Service Pack, but maybe wait for the first CU or at least a couple/few weeks to see if any critical bugs came out with that SP. Then I advise them to try and be within a CU or two of the latest, and to always review the notes from Microsoft of what is fixed in a new CU or SP and see what is fixed. Are you suffering from any of that? Could you be? Maybe look at testing that and deploying it. Here’s an example of the list for CU1 for SQL Server 2016 (RTM).

But do look at your version. I understand it is comfortable to stay on the version you know. So many changes have come out. If you are running anything earlier than SQL Server 2012, you are at least 3 versions back now! And Microsoft releases every year or two now. If you aren’t on SQL Server 2016 SP1 yet? You are potentially giving up ENTERPRISE FEATURES that were made available in SQL Server Standard edition starting in SP1 for SQL Server 2016 as I blogged about here. For your sanity. For your success. Try and stay on a version at least covered by mainstream support. Upgrades are fairly pain free these days and good consultants like Straight Path or the many great firms out there in this space can help make it even easier.

How about you? What’s the earliest or lowest version number you get for SELECT @@Version?? I won’t tell anyone, just leave it there in the comments.

If you learned something in this post, you might like the entire Straight Up SQL Server Tips series. Here are a couple more tips to get you started:

• How Do I Configure SQL Server Alerts? – If you aren’t configuring (and signed up to receive!) SQL Server alerts, you are missing out on your SQL server telling you when it isn’t healthy. These scripts and videos can get you up to speed here in 5 minutes.
• How Do I use SP_Configure? What is SP_Configure? What are some defaults? I have a series of posts on that, this one should get you started.
• And be sure to check out the series. This is the first post officially in the series and I’ll be adding more each week and will be updating the home base for these posts with other posts over the years that fit the category.