In Memory Tables Flashcards Preview

70-762 Developing SQL Databases > In Memory Tables > Flashcards

Flashcards in In Memory Tables Deck (7):
1

13 Syntax Limits of Natively Complied Stores Procedures

1. Cannot Access Tempdb
2. Cannot use Cursors
3. Cannot use CASE statement
4. Cannot use MERGE statement
5. Cannot use SELECT INTO clause
6. Cannot use PERCENT or WITH TIES in TOP clause
7. Cannot use DISTINCT with Aggregates
8. Cannot use the following operators: INTERSECT, EXCEPT, APPLY, PIVOT, UNPIVOT, LIKE, CONTAINS
9. Cannot use Common Table Expressions
10. Cannot use multi row insert statements
11. Cannot use EXECUTE WITH RECOMPILE
12. Cannot use Views
13. Cannot use FROM Clause in update statement

2

What system stores procedure do you use to enable collection of stored procedure level stats on natively compiled stored procedures?

Sys.sp_xtp_control_proc_exec_stats

3

What system stored procedure do you use to enable the collection of query level stats on natively compiled stored procedures?

Sys.sp_xtp_control_query_exec_stats

4

How would you explicitly tell SQL to make data durable for an in memory table?

Use the DURABILITY = SCHEMA_AND_DATA

For instance:
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA

5

What are the durability options to make in memory tables more performant?

1. Set the durability option on the table creation

Example DURABLITY = SCHEMA_ONLY

2. Set the delayed durability option at the database level

Example ALTER DATABASE WorldWideTraders SET DELAYED_DURABILITY = ALLOWED

3. Set the delayed durability at the transaction level

COMMIT TRANSACTION WITH (DELAYED_DURABLITY = ON)

6

What are good cases for natively compiled stored procedures?

1. Applications needing the best possible performance
2. Queries that execute frequently
3. Aggregation
4. Nested Koop joins
5. Multi statement select, insert, update or delete operations
6. Complex expressions
Procedural logic such as conditions and loops

7

Use Cases for In Memory Tables

1. High data ingestion rates
2. High Volume, high performance data reads
3. Complex business logic in stored procedures
4. Real time data access
5. Session state management
6. Applications relying heavily on temporary tables, table variables and table valued parameters
7. ETL operations