With SQL Server 2005's extended support ending today, it seems appropriate to write a post about "My Favourite SQL Server Feature" for T-SQL Tuesday this month, hosted by Jens Vestergaard (@vestergaardj).
The thing is that when I consider reasons to upgrade from SQL Server 2005, I see a ton of features that could be leveraged in later versions. You could use availability groups, or columnstore indexes, or spatial. You could use the Tablix control in SSRS, or project-based SSIS configurations… so many reasons. But of course, if you're feeling stuck on older versions, then it's the backwards-compatibility of SQL Server that is the key. The new features can come later, once you've moved to a later version.
Now, I appreciate that if you have outer joins that use the "*=" syntax, then upgrading is going to take some effort. That does need to be fixed before you can move to later versions.
You see, what I love the most about SQL Server today, and giving reasons to upgrade, is actually SQL Azure. If you have provisioned SQL Database in Azure, then your system is continually being upgraded. You are on the newest version of the platform. There is no decision you can make to satisfy those people who say "Hang on, leave me on SQL Server 2008 R2, because my third-party product doesn't support SQL Server 2012 yet…"
I can assure you that Microsoft does not want to break the code that you have running successfully in SQL Database. They will continue to improve the platform, and provide new features, but I'm confident that any code that you write today for SQL Database will continue to work there for a very long time.
And that gives me hope for on-prem SQL Server environments too. I feel confident that things I do today, whether I'm dealing with new SQL 2016 work, or back as far as SQL 2008 R2, will continue to work in future versions. Because Microsoft is in the business of upgrading you to the latest version.
My Windows machine pulls down updates automatically. My iPhone does the same. My SQL Database does. I would like my on-prem SQL Server to be doing the same, automatically deploying things into a SQL 2016 environment as it becomes available.
Get off SQL 2005 as soon as you can, and brace yourself for frequent upgrades. Microsoft recommends proactive upgrades from cumulative updates now, so I'm confident that it's only a matter of time before upgrading becomes a continuous process on-prem, like it is in SQL Azure.
My favourite SQL Server feature is its backwards-compatibility.