MS SQL Server - 81 - In-Memory OLTP MCQ

81 – In-Memory OLTP MCQ

1 / 25

1. What is the primary purpose of In-Memory OLTP in SQL Server?

A. Improve backup and restore operations

C. Boost transaction processing performance

B. Enhance reporting capabilities

D. Improve data compression techniques

2 / 25

2. Which version of SQL Server introduced In-Memory OLTP?

D. SQL Server 2016

A. SQL Server 2008

C. SQL Server 2014

B. SQL Server 2012

3 / 25

3. In-Memory OLTP tables in SQL Server are stored in which type of files?

B. .ldf files

D. .xtp files

C. .ndf files

A. .mdf files

4 / 25

4. What is the maximum size limit for an In-Memory OLTP database in SQL Server?

D. 2 TB

C. 256 GB

A. 1 GB

B. 100 GB

5 / 25

5. Which of the following is a requirement for using In-Memory OLTP in SQL Server?

A. The database must be in Full Recovery mode.

B. The database must be in Simple Recovery mode.

D. The database must have compatibility level 80.

C. The database must use the Latin1_General collation.

6 / 25

6. In-Memory OLTP uses a lock-free, latch-free data storage mechanism. What is it called?

A. B-tree

D. Columnstore Index

B. In-Memory Index

C. Hash Index

7 / 25

7. In-Memory OLTP supports which type of indexes?

A. Clustered Indexes

B. Non-clustered Indexes

D. None of the above

C. Both A and B

8 / 25

8. Which stored procedure is used to natively compile a T-SQL module in In-Memory OLTP?

C. sp_recompile_native

D. sp_compile_native

B. sp_compile

A. sp_recompile

9 / 25

9. What is the maximum number of indexes allowed per memory-optimized table in SQL Server?

A. 2

B. 8

D. 32

C. 16

10 / 25

10. Which isolation level is used by default for transactions in In-Memory OLTP?

A. READ UNCOMMITTED

B. READ COMMITTED

D. SERIALIZABLE

C. SNAPSHOT

11 / 25

11. What is the main benefit of using In-Memory OLTP for high-performance applications?

D. Enhanced backup capabilities

A. Improved disk I/O

C. Reduced contention and locking

B. Lower memory usage

12 / 25

12. Which dynamic management view (DMV) can be used to monitor the performance of In-Memory OLTP in SQL Server?

C. sys.dm_db_index_usage_stats

D. sys.dm_db_xtp_memory_consumers

A. sys.dm_exec_requests

B. sys.dm_os_memory_objects

13 / 25

13. In-Memory OLTP supports which programming languages for writing stored procedures?

B. C# only

A. T-SQL only

C. Both T-SQL and C#

D. Python only

14 / 25

14. What is the purpose of the In-Memory OLTP Checkpoint process in SQL Server?

D. To optimize query plans

A. To create backups of memory-optimized tables

C. To compile T-SQL modules

B. To flush changes from memory to disk

15 / 25

15. Which statement accurately describes the durability of data in memory-optimized tables in SQL Server?

C. Durability depends on the database recovery model.

A. Data is always durable in memory-optimized tables.

D. Data is only durable if explicitly marked as such.

B. Data is non-durable and can be lost in case of a crash.

16 / 25

16. What is the recommended use case for disk-based tables in a database that also contains memory-optimized tables?

D. Log shipping

B. High-performance transaction processing

A. Reporting

C. Data warehousing

17 / 25

17. Which type of join operation is not supported with memory-optimized tables in SQL Server?

B. LEFT JOIN

A. INNER JOIN

D. FULL OUTER JOIN

C. RIGHT JOIN

18 / 25

18. In-Memory OLTP is also known by what other name in SQL Server?

D. UltraDB

C. SpeedySQL

B. Hekaton

A. XTP

19 / 25

19. What happens to the data in a memory-optimized table when you restart the SQL Server instance?

B. Data is automatically persisted to disk

C. Data remains in memory

A. Data is lost

D. Data is moved to a separate disk-based table

20 / 25

20. In-Memory OLTP tables support which type of data types?

C. Only numeric data types

A. All SQL Server data types

B. A limited set of data types

D. Only character data types

21 / 25

21. What is the primary mechanism used for managing transactions in In-Memory OLTP?

B. Latches

C. Concurrency

A. Locks

D. Optimistic concurrency control

22 / 25

22. Which tool can be used to migrate disk-based tables to memory-optimized tables in SQL Server?

A. SQL Server Management Studio (SSMS)

C. Database Migration Assistant

D. SQL Server Integration Services (SSIS)

B. SQL Server Data Tools (SSDT)

23 / 25

23. What is the main drawback of using In-Memory OLTP in SQL Server?

B. High memory consumption

D. Incompatibility with T-SQL

C. Slower query performance

A. Limited support for indexes

24 / 25

24. Which SQL Server edition(s) support In-Memory OLTP?

C. Express Edition and above

B. Standard Edition and above

D. Developer Edition and above

A. Enterprise Edition only

25 / 25

25. What is the maximum number of memory-optimized filegroups per database in SQL Server?

A. 1

C. 4

D. 8

B. 2

Your score is

MS SQL Server – 81 – In-Memory OLTP MCQ