This is a post about one of your favorite topics, I’m sure: Documentation!
Books Online for SQL Server (current version) has always been my first go-to source for just about anything involving T-SQL. Pick any topic at random; it will have explicit syntax diagrams, and at least rudimentary examples (even if those examples don’t always conform to my personal set of best practices).
Recently, though, they’ve changed the structure, so that multiple versions are combined into a single set of documentation. This causes confusion in some cases, and I wanted to address both sides of the story.
The Good
The primary reason for the consolidation is simple: maintenance. Previously, there was a complete library for SQL Server 2005, another complete library for SQL Server 2008, one for SQL Server 2012, and so on. Considering that a rather large percentage of the documentation doesn’t change between versions, that amounts to a lot of redundancy. Also, when an update is required that affects multiple versions, it would mean a much more complicated effort – both applying the update multiple times, and keeping those updates in sync. (This would probably not be relevant for a feature change, but think fixing flawed samples or clarifying wording.)
Where this leads is the same place you get when you remove red tape and redundancy in your own work: more productivity! Seriously, that sounds sensationalist, but the result we’ve actually seen with this in practice is much faster turnaround. Less than a week ago, I pointed out an error in the CREATE TABLE syntax, which implied that inline index syntax was possible in SQL Server 2008. This was fixed the same day; that type of update used to take months.
The Bad
The consolidation of the documentation means that, as syntax for any given command is extended from version to version, the single source needs to somehow differentiate what syntax is valid for which versions of SQL Server (they already had this problem with syntax specific to editions, like Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse). This can lead to messy entries like CREATE TABLE – using, as an example, the portion that talks about inline index syntax:
You can see that multiple potential options for the inline index syntax have to be called out explicitly, since they came about in different versions. Walking through the syntax diagrams above is no walk in the park, either, since they often have to be adjusted per version as well.
The Ugly
There is a deeper problem. On a forum the other day, someone insisted that their ROW_NUMBER() syntax wasn’t working because they were using SQL Server 2005, and that Books Online topic implies that ROW_NUMBER() wasn’t introduced until SQL Server 2008:
If this gets confusing, there’s a workaround. At least for now. Whenever you have a URL, like:
You can get to an explicit, older version – if it exists – by adding the version to the page identifier. Examples:
- https://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx – SQL Server 2005
- https://msdn.microsoft.com/en-us/library/ms186734(SQL.100).aspx – SQL Server 2008/2008 R2
- https://msdn.microsoft.com/en-us/library/ms186734(SQL.110).aspx – SQL Server 2012
- https://msdn.microsoft.com/en-us/library/ms186734(SQL.120).aspx – SQL Server 2014
This isn’t foolproof, though. For example, THROW(), which was introduced in SQL Server 2012, can be accessed through the old URL, making it seem like THROW(), too, was available then. The key is to look at the header below the title – if you access the 2005 version of ROW_NUMBER(), you see this, which is quite different from the modern version above:
However, for THROW(), you are presented with a page that actually (and in this case, correctly) indicates when the function was introduced (the “THIS TOPIC APPLIES TO” section isn’t on older copies of the documentation):
The Why, and What To Do
I see their side, too, of course. They had to draw a line somewhere, and what makes more sense than the lowest supported version at the time? With the vast number of topics in Books Online, going through each and every page to differentiate which ones start with SQL Server 2008, as opposed to before SQL Server 2008, would be tedious, time-consuming, and provide little benefit. This should be an easier task in future versions, since differentiators for 2012, 2014, and so on are in the single source now.
In closing, what this all means is that you can’t necessarily trust that Books Online is telling you the truth about when a command or syntax was introduced. If you were dealing with an older version, it might not be immediately obvious whether your syntax is actually valid in that version. Microsoft doesn’t have to worry about this for their support team, because aside from a small number of perpetual support contracts, they’re not dealing with 2005 and older – never mind that the people who are in charge of those accounts are hopefully well-versed enough that they’re not relying on Books Online anyway. But for the rest of us, if you’re helping someone running SQL Server 2005 or older, don’t take too much stock in the version-specific header at the top of any given topic.
