DP-800 Practice Test

Sample DP-800 Practice Questions

10 real questions from the free DP-800bank. Pick an answer in your head, then open “Show answer” for the explanation.

1. 1. A trading application requires extremely low-latency inserts and lookups on a hot session-state table, with data structures kept resident in memory rather than on disk pages. Which table type addresses this?

   • A. A memory-optimized (in-memory OLTP) table
   • B. An external table
   • C. A columnstore-based table with no rowstore indexes
   • D. A partitioned heap
   Show answer

   Correct answer: A

   Memory-optimized tables are held in memory and use optimistic, latch-free concurrency control, which is designed for very low-latency, high-throughput OLTP workloads like session state. External tables point to data outside the database, a columnstore-only design targets analytics, and a partitioned heap only improves manageability, not in-memory access latency.

2. 2. A telemetry table receives billions of rows per year and old data must be purged monthly with minimal impact to concurrent inserts and queries. Which partitioning-based technique achieves this most efficiently?

   • A. Partition the table by month and use ALTER TABLE...SWITCH to move the oldest partition out before dropping it, avoiding a row-by-row DELETE
   • B. Run a single DELETE statement each month filtered by date range against the unpartitioned table
   • C. Drop and recreate the entire table each month, reloading just the data to keep
   • D. Disable indexes on the table before each delete operation
   Show answer

   Correct answer: A

   Partitioning by a time column lets you use the metadata-only ALTER TABLE...SWITCH operation to move an entire partition's worth of old data out almost instantly, after which it can be dropped, avoiding the heavy logging and locking of a row-by-row DELETE against billions of rows. A single large DELETE is comparatively slow and blocking, dropping and recreating the whole table is destructive and unnecessary, and disabling indexes does not address the purge problem and would hurt query performance.

3. 3. An ingestion pipeline needs to load millions of JSON events per hour with minimal load latency, and downstream consumers query only a handful of well-known fields on nearly every event. Which storage design best balances load speed with query performance?

   • A. Reject any event that cannot be fully flattened at ingestion time
   • B. Fully normalize any possible nested field into its own relational column before each insert
   • C. Store the JSON as plain nvarchar(max) with no indexing of any kind
   • D. Store the raw JSON as-is in a json column, and parse fields into indexed computed columns or a JSON index for the frequently queried properties
   Show answer

   Correct answer: D

   Storing JSON as-is (LOB-style storage) keeps ingestion fast because it matches the speed of loading string data, while adding computed columns or a JSON index over the small set of frequently queried properties gives query performance close to relational storage for those fields. Fully normalizing every nested field at insert time slows ingestion, storing with no indexing at all hurts query performance, and rejecting unflattenable events is an unnecessary and destructive constraint.

4. 4. You need a rule that guarantees a Discount column can never store a value greater than the OriginalPrice column in the same row. Which constraint type enforces this?

   • A. A UNIQUE constraint spanning both columns
   • B. A DEFAULT constraint on the Discount column
   • C. A CHECK constraint referencing both columns, such as CHECK (Discount <= OriginalPrice)
   • D. A FOREIGN KEY constraint from Discount to OriginalPrice
   Show answer

   Correct answer: C

   A CHECK constraint can express a logical expression comparing multiple columns in the same row, such as ensuring Discount never exceeds OriginalPrice, and it is evaluated on every insert or update. A DEFAULT constraint only supplies a fallback value when none is given, a UNIQUE constraint only prevents duplicate combinations, and a FOREIGN KEY constraint enforces referential integrity against another table, not an inter-column comparison within the same row.

5. 5. Which system stored procedure allows an application to reserve a whole block of sequence numbers at once rather than calling NEXT VALUE FOR one at a time?

   • A. sys.sp_sequence_get_range
   • B. sys.sp_executesql
   • C. sys.sp_helpindex
   • D. sys.sp_rename
   Show answer

   Correct answer: D

   sys.sp_sequence_get_range is designed specifically to retrieve a contiguous range of sequence numbers in a single call, which is more efficient than repeated NEXT VALUE FOR calls when an application needs many numbers up front. sp_executesql runs dynamic SQL, sp_helpindex reports index metadata, and sp_rename renames database objects, none of which relate to sequence ranges.