Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 13 of 212

3 C H A P T E R 1 | Faster queries beneficial when you have queries that scan large data sets. It also supports parallel plans when you are using interop access with memory-optimized tables. SQL Server 2016 also added multithreaded capabilities to the following operations affecting memory- optimized tables: Persistence of changes to memory-optimized tables in checkpoint files. Log apply in a recovery operation. MERGE operation. Note Because of the change to the checkpoint operation, the sys.dm_db_xtp_checkpoint_stats and sys.dm_db_xtp_checkpoint_files dynamic management views (DMVs) are significantly different from the versions in SQL Server 2014. See "sys.dm_db_xtp_checkpoint_stats" at and "sys.dm_db_xtp_checkpoint_files" at for more information. Introducing native client enhancements You can now enable Multiple Active Result Sets (MARS) when connecting to a memory-optimized table or running a natively compiled stored procedure. This way, the database engine can begin fetching rows from a new result set before completely retrieving rows from an earlier request. To use MARS, you must enable it explicitly in the connection string, like this: Data Source=; Initial Catalog=; Integrated Security=SSPI; MultipleActiveResultSets=True For the most part, you can use MARS with memory-optimized tables just as you do with disk-based tables, but there are a few differences: If two statements attempt to modify the same row, a write-write conflict occurs and the new operation fails. Because each statement runs under SNAPSHOT isolation and is in a batch-scoped transaction, the result of an operation performed by one statement is not visible to another statement. However, a rollback of one batch-scoped transaction does affect other transactions in the same batch. A user transaction cannot perform Data Definition Language (DDL) operations. When you use a MARS-enabled connection, you can also execute natively compiled stored procedures. If a stored procedure contains a SELECT statement, that statement can yield execution to another statement; otherwise, the stored procedure runs completely without yielding to other statements. By using the BEGIN TRANSACTION statement in a T-SQL statement, you can begin a new user transaction within the current user transaction. You can also create a save point in a transaction by using the SAVE TRANSACTION statement or by making a call to transaction.Save(). However, you cannot use these features in a natively compiled stored procedure. Exploring T-SQL enhancements New enhancements to the query surface area in SQL Server 2016 make it easier to implement memory-optimized tables. In particular, the ALTER TABLE statement supports more functionality so that you can make changes to existing memory-optimized tables without needing to first drop a table

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016