In November 2016, we announced the general availability (GA) of In-Memory OLTP technology in Azure SQL Database worldwide. From then on we have been working towards unlocking some more useful features which we believe will significantly help you leverage this technology more efficiently into your applications.
Today we are pleased to announce a string of improvements to In-Memory OLTP technology in Azure SQL Database, which spans multiple avenues and continues to unlock and expand the T-SQL surface area and improve the overall manageability.
The new feature improvements are listed below and are available today in Azure SQL Database and will also be available in SQL Server vNext.
Increased number of indexes for memory-optimized tables
The limitation of 8 indexes for memory-optimized tables has been eliminated. You can now create as many indexes on memory-optimized tables as you can create on disk-based tables.
Any disk-based table in your database that you could not migrate previously because of this limitation can now be memory-optimized, and thus leverage the performance benefits of In-Memory OLTP technology.
In-Memory OLTP schema management
The GA of In-Memory OLTP in Azure SQL Database included support for ALTER operations on memory-optimized objects. This enabled you to perform most schema management operations such as adding or dropping columns, managing indexes, and updating natively compiled stored procedures. At that time, the one operation not yet supported was renaming objects.
We have addressed this now by adding support for the system stored procedure sp_rename with memory-optimized tables and natively compiled T-SQL modules. With this, Azure SQL Database now supports all the main schema management operations for In-Memory OLTP.
sp_rename – sp_rename is used to change the name of the user object in the current database. In-Memory OLTP objects such as tables, table columns, natively compiled procedures, etc. can now all be renamed with this stored procedure.
Note that natively compiled T-SQL modules must be created as schema-bound modules. Tables, columns, and modules referenced from a schema-bound module cannot be renamed. Therefore, to rename a table or column referenced from a natively compiled T-SQL module, first drop the module, then perform the rename, and finally recreate the module referencing the new name.
Max commit dependencies limit eliminated
The limit on the maximum number of transactions a given transaction depends on or the number of transactions that depend on a given transaction is eliminated.
So you will no longer receive the corresponding error message: "41839: Transaction exceeded the maximum number of commit dependencies".
Extended T-SQL support for In-Memory OLTP
In-Memory OLTP in Azure SQL Database at GA supported T-SQL surface area that was on full parity with SQL Server 2016, including support for constructs like DISTINCT, SUBQUERIES, IN, EXISTS, OR, NOT, OUTER JOIN, etc.
We have further extended the T-SQL support and added the following:
CASE – CASE expressions are now fully supported in SELECT statements in natively compiled T-SQL modules. Prior to this you would have to create a table variable to construct the single result set and from this table variable you would then SELECT based on the predicate that defines the CASE.
COMPUTED COLUMNS – Computed columns, as well as indexes on computed columns, are now supported in memory-optimized tables. Additionally, you can now also create indexes on these computed columns in memory-optimized tables.
CROSS APPLY and JSON – The CROSS APPLY operator is now supported in natively compiled T-SQL modules. In addition, all JSON functions and clauses are now supported in natively compiled T-SQL modules and on constraints in memory-optimized tables. Indexes on computed columns allow indexing JSON data. Learn more about optimizing JSON processing using In-Memory OLTP.
Another feature worth mentioning is the support for sp_spaceused. Even though on Azure SQL Database the storage is managed for you by the platform, sp_spaceused will give you a good insight into the storage characteristics of the data stored in memory-optimized tables.
sp_spaceused – sp_spaceused can now be used to understand the storage footprint associated with In-Memory OLTP. A new parameter '@include_total_xtp_storage' has been added to the stored procedure for the same. Get more details about sp_spaceused.
If you are currently leveraging In-Memory OLTP technology in your application or if you do plan on using it in the future, we sincerely hope that these improvements will enable new scenarios for you whilemaking it easier for you to leverage this technology in your application.
Quelle: Azure
Published by