Let me count the ways…

T-SQL Tuesday LogoMichael J Swart is hosting this month’s T-SQL Tuesday, about the June 1st release of SQL Server 2016.

There’s so much “new” that Microsoft spread it out across several sprawling lists. The headline features from a year ago are:

  • Query Store
  • Polybase
  • Stretch Database
  • JSON Support
  • Row Level Security
  • Always Encrypted
  • In-Memory Enhancements

Microsoft’s more recent data sheet lists a few more including:

  • Temporal Tables
  • Mobile Data Visualizations
  • R Language Support
  • Dynamic Data Masking

What’s impressive to me is that, despite neglecting Standard Edition for some time, many of these trumpeted features are available (in some form) in Microsoft’s $4000 per core budget offering. You only need to pay treble damages for Always Encrypted, In-Memory Enhancements, and Mobile Data Visualizations. You can check the exhaustive list for your favorite new feature. Maybe it’s some simple syntactic sugar like DROP IF EXISTS.

As usual, all of these tasty treats are available in the developer edition which is now a free download. Technically, this started with SQL Server 2014 (but not until April of 2016). Plus, the 2016 version of SQL Server Management Studio (SSMS) is also a free and separate download, so you can enjoy toys like Live Query Statistics even for your 2014 servers.

“Awesome Abacus” by Anne Ruthmann

“Awesome Abacus” by Anne Ruthmann

Despite all of this bounty, Microsoft passed over a seemingly low-hanging fruit. All sorts of T-SQL gymnastics can be performed with a simple table of numbers—often called a Tally TableThere are a variety of ways to make them, but Itzik Ben-Gan has a widely regarded solution involving CTEs and CROSS JOINs and TOP. But it’s 2016—we shouldn’t have to roll our own row constructor, even if it’s a neat trick using the new JSON capability. Microsoft should just answer the will of the people and create an optimized, pure T-SQL mechanism to produce some numbered rows for us.

Oh, well, perhaps they thought that we really only needed them to split strings and they took care of that for us with string_split. Sure, it performs well, but it’s no generic row expander.

Maybe in 2018.

Leave a Reply

Your email address will not be published. Required fields are marked *