Design and Implement Database Objects DP-800 Practice Questions (Page 9)

Questions 41–45

1. 41foundation · easy

   What is the primary purpose of creating a nonclustered index on a table?

   • ATo compress the table data
   • BTo speed up queries that filter or sort by the indexed columns
   • CTo enforce uniqueness of the indexed columns
   • DTo physically reorder the table data
   Reveal Solution Discussion Select an answer first
2. 42foundation · easy

   What is a key characteristic of in-memory (memory-optimized) tables in SQL Server?

   • AData is always persisted to disk immediately
   • BData is stored in memory and accessed without traditional disk I/O
   • CThey cannot be indexed
   • DThey are only available in Azure SQL Database
   Reveal Solution Discussion Select an answer first
4. 43foundation · easy

   What is the primary purpose of a PRIMARY KEY constraint?

   • ATo speed up queries
   • BTo maintain referential integrity
   • CTo enforce that column values are not NULL
   • DTo uniquely identify each row in a table
   Reveal Solution Discussion Select an answer first
5. 44foundation · easy

   Which type of index is most beneficial for a column that is frequently used in aggregate queries over large datasets?

   • AClustered index
   • BNonclustered index
   • CHash index
   • DColumnstore index
   Reveal Solution Discussion Select an answer first
6. 45application · medium

   An e-commerce platform stores product attributes as JSON in a Product table. Users frequently search for products by a specific attribute, e.g., 'Color'. The JSON structure is consistent across all rows. You need to optimize these queries without changing the application's JSON storage. What should you implement?

   • ACreate a full-text index on the JSON column.
   • BCreate a nonclustered index on the JSON column itself.
   • CCreate a computed column that extracts the Color value and add an index on it.
   • DUse a UNIQUE constraint on the JSON column.
   Reveal Solution Discussion Select an answer first