When SQL Server® 2014 brought in some interesting innovations around In-Memory objects it was one of a kind in the industry. It was amazing to see how In-Memory objects can be ACID complaint, persistent, and not be lost because of power outages. I have yet to see users using this feature to its full potential because it needs proper planning. I know that for highly transactional systems, sometimes using such capabilities brings great power and scalability to applications without additional cost.
As the SQL Server team started to build on some of the innovations it made with SQL 2014, it also invested in making the next version with some much needed improvements, which I felt are worth a call-out.
Security Improvements with TDE Support
In SQL Server 2014, the InMemory tables were not supported as part of Transparent Data Encryption (TDE). This was a serious issue for some customers because this was part of their adoption strategy. Many of the transactional tables (OLTP) that required this feature also contained business-sensitive data that needed extra protection.
With SQL Server 2016, support for TDE has been extended. Now applications can start using TDE for InMemory tables. Users who are planning to upgrade their current SQL versions and enable TDE need to:
Turn off TDE -> drop the keys -> create new keys-> turn on TDE again after the upgrade.
The process is simple to start using this enhancement.
Stats update for In-Memory Tables
In 2014, statistics were not auto-updated when involved with In-Memory tables. This feature is now enabled with the 2016 version, and personally, I feel that this is a great addition. Auto update stats is fully supported for the following scenarios:
- SET option AUTO_UPDATE_STATISTICS
- SET option AUTO_UPDATE_STATISTICS_ASYNC
- sp_autostats
- sp_updatestats
- sp_createstats
- Sampling is fully supported, and fullscan is not required. The threshold is the same as the disk-based table
- Adding index automatically populates the stats
- dm_db_stats_properties will have information on In-Memory tables
Support for expanded Query operations
When SQL Server 2014 brought in support for In-Memory capability, there were a number of limitations that made it less usable for certain workloads. The next release included enhancements that make it even more desirable to use. Some of the enhancements include:
- {LEFT|RIGHT} OUTER JOIN
- UNION [ALL]
- SELECT DISTINCT
- Subqueries (EXISTS, IN, scalar)
- FOREIGN KEY, CHECK Constrain
- UNIQUE constraints and indexes
- Nested Stored procedures (EXECUTE)
- Natively compiled scalar UDFs
- Indexes on NULLable columns
I have outlined some of the notable capabilities of In-Memory for objects inside SQL Server 2016. I am sure you will be eager to use some of them as you upgrade your version to the new release.
Leave a Reply