Flashcards in In Memory Tables Deck (7):
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
What system stores procedure do you use to enable collection of stored procedure level stats on natively compiled stored procedures?
What system stored procedure do you use to enable the collection of query level stats on natively compiled stored procedures?
How would you explicitly tell SQL to make data durable for an in memory table?
Use the DURABILITY = SCHEMA_AND_DATA
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA
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)
What are good cases for natively compiled stored procedures?
1. Applications needing the best possible performance
2. Queries that execute frequently
4. Nested Koop joins
5. Multi statement select, insert, update or delete operations
6. Complex expressions
Procedural logic such as conditions and loops