SSC IMD SA 2017 – Question 185

What is true about an index?

• Index increases performance even if the table is frequently updated
• It makes harder work for the SQL Server engine to work on indexes that have large keys
• It is not difficult for the SQL Server engine to work on indexes with large keys
• None of these

Detailed Explanation:

The correct answer is b) It makes it harder/more work for the SQL Server engine to work on indexes that have large keys (यह एसक्यूएल सर्वर इंजन के लिए इंडेक्स पर काम करने के लिए कठिन काम करता है, जिसमें बड़ी की हैं).

Database Index Architecture & Key Size Impact:

• Database indexes are structured as balanced trees (B-Trees). Index pages in engines like Microsoft SQL Server have a fixed size of 8 KB (8192 bytes).
• Impact of Large / Wide Keys: When an index key consists of large or wide columns (e.g., composite keys or long character strings), each index page can hold fewer index keys (lower fan-out). This causes:
  • The B-Tree must become taller (increased index depth/height).
  • The database engine must execute more page I/O read operations to traverse from the root node to the leaf level.
  • Cache efficiency drops because index pages consume excessive memory in the buffer pool.
  • Write overhead increases significantly during INSERT, UPDATE, and DELETE operations due to frequent page splits.
• Why Option (a) is False: If a table is frequently updated, indexes actually degrade overall write performance because the database engine must constantly update the index tree for every change.