T-SQL Tuesday: This Weird Trick Still Missing from SQL Server 2016
This month, T-SQL Tuesday is being hosted by fellow Canadian and all-around good guy Michael Swart. The topic? SQL Server 2016. I've blogged quite a bit about the new version already, but one of Michael's bullets of inspiration did exactly what he meant it to:
It’s 2016, why is this still (not) a thing?
For nearly a decade now, I have been a strong advocate of having a switch in the Developer Edition engine that allows me to tell SQL Server that it should behave like Standard, or Web, or Workgroup, or Express - whatever edition my actual production instance is running. I think I have renewed interest in this now that Developer Edition is free - there are going to be a lot more people using it for local development and prototyping, instead of Express, or an MSDN copy of Standard, and so on.
So, it would be great to have this switch, allowing me to get an error message locally if I tried to implement a feature not supported by the target, like partitioning, for example.
Or at the very least, have Management Studio (SSMS) support a scripting option that targets edition, and raises a warning or error if it has to generate any scripts that include features not supported on the target edition. (I can currently target major version, as seen at left, but not edition.)
Or an equivalent option in SQL Server Data Tools (SSDT), where I can currently target Azure SQL Database vs. traditional SQL Server, but not edition.
There are a couple of Connect items you can vote for, or if you don't like the proposed solutions there, you can always create your own suggestion:
- Connect #331297 : Developer Edition in Standard / Workgroup mode (Rob Farley)
- Connect #496380 : Enable SQL Developer Edition to target specific SQL version (Des Norton)
As always, your vote is important, but leaving a comment indicating your business case can be even more compelling.
What do we do in the meantime?
Several people have blogged about sys.dm_db_persisted_sku_features; for example, Paul Randal. The TL;DR: This DMV will tell you about certain Enterprise Edition features that are being used; the list is as follows in SQL Server 2016:
- Change Data Capture
- Columnstore Indexes (clustered or not)
- Data Compression (page or row)
- In-Memory OLTP
- Table Partitioning
- Transparent Data Encryption
But, you might notice some omissions from that list; for example, the new Always Encrypted feature in SQL Server 2016.
And there are other things none of these methods will spot; as examples, your reliance on non-expansion of indexed views without employing a NOEXPAND hint, dynamic SQL that creates an in-memory table, or a job that will create a clustered columnstore index on a table when it hits <n> rows (yes, I've heard of someone doing this).
Conclusion
I think now, more than ever, Developer Edition needs some kind of configuration option to make it behave like any potential target edition. In fact, I should be able to make Enterprise, or Evaluation, or even Standard Edition, behave like a lower edition (just not for the sake of bypassing proper licensing).
Maybe someday we'll get our wish, but for now, I'll repeat Michael's question:
It’s 2016, why is this still (not) a thing?