There’s an interesting scenario that’s come up in creating some new samples for SQL Server 2016.
I know that for many, many years people have asked for a way to limit developer edition to only use standard edition features, or at least to have a way to flag them. I’ve often thought that I’d like a way to set a “target environment” and have the colorization change anything that I can’t use in standard to an obviously different color.
However, previously, if you used developer edition to create a database, as long as you didn’t use any enterprise features, you could then backup and restore that database to a standard edition server.
That’s no longer the case with SQL Server 2016.
If you create a temporal table in a database on developer edition, it automatically chooses PAGE compression for the history file. There is also no DDL to avoid this. The database can no longer be restored on a standard edition server. To work around it, you would need to rebuild the table without compression after it’s created.
I see this as quite a change in behavior. I don’t think that features that are only available in enterprise (or other perhaps Azure) editions should be “auto-injected”.
Clearly, if you script out the database and create a new database on standard edition using the script, it will create the history table without compression. But I suspect that many people create DBs as starter DBs using developer edition, and then deploy them to other editions that don’t support compression.
I’d be interested in thoughts on how common this practice currently is.
That's a good catch and sounds like something they just didn't think about.
Wow! Good catch.
Greg, you are one of the most diligent and detail-oriented guys I know. Good catch, and you should totally put in a Connect item for this.
Thanks Brent
Thanks for the post.
We / I have always just conceptualized the Dev edition as Enterprise, but way less cost for development.
From an application development thinking we have always built new databases and the schemas with DDL. It would seem a best practice as the logical implementation doesn't break with a physical implementation dependency. The application is doing application things and the database is doing database things.
This is silly but it had me intrigued.
https://msdn.microsoft.com/en-us/library/mt590957.aspx
"The history table is created as a rowstore table. PAGE compression is applied if possible, otherwise the history table will be uncompressed. For example, some table configurations, such as SPARSE columns, do not allow compression."
I wouldn't be surprised if that shows up for an answer on any connect item opened.