70-465 · Question #147
You need to change the ProdDB database. Which two database options should you change to meet the requirements? Each correct answer presents part of the solution. Choose two.
The correct answer is E. COLLATE IN.MEMORY F. CONTAINS MEMORY OPTIMIZED DATA. To enable In-Memory OLTP and memory-optimized tables in SQL Server, the database must be configured with both a memory-optimized filegroup and the appropriate collation setting for in-memory objects.
Question
Options
- ACONTAINS FILESTREAM
- BChange recovery model to FULL
- CCONTAINMENT = PARTIAL
- DChange recovery model to BULK_LOGGED
- ECOLLATE IN.MEMORY
- FCONTAINS MEMORY OPTIMIZED DATA
How the community answered
(14 responses)- B7% (1)
- C14% (2)
- D7% (1)
- E71% (10)
Why each option
To enable In-Memory OLTP and memory-optimized tables in SQL Server, the database must be configured with both a memory-optimized filegroup and the appropriate collation setting for in-memory objects.
CONTAINS FILESTREAM is used to store unstructured BLOB data in the file system and is unrelated to In-Memory OLTP or memory-optimized table requirements.
Changing the recovery model to FULL affects transaction log backup behavior and point-in-time recovery capabilities, but is not a requirement for enabling In-Memory OLTP or memory-optimized tables.
CONTAINMENT = PARTIAL enables partially contained databases to manage their own users and settings independently, but this is unrelated to In-Memory OLTP configuration.
The BULK_LOGGED recovery model minimizes log space for bulk operations but does not fulfill any requirement for enabling memory-optimized tables or In-Memory OLTP features.
COLLATE IN_MEMORY (or configuring the database collation to support In-Memory OLTP) is required because memory-optimized tables require a supported collation to function correctly with in-memory data structures.
CONTAINS MEMORY_OPTIMIZED_DATA is required to add a memory-optimized filegroup to the database, which is a mandatory prerequisite for creating memory-optimized tables and enabling In-Memory OLTP functionality in SQL Server.
Concept tested: Configuring SQL Server In-Memory OLTP database options
Source: https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/the-memory-optimized-filegroup
Topics
Community Discussion
No community discussion yet for this question.