Download Indexing Practice Exam and more Exams Technology in PDF only on Docsity!
Question 1. What is the primary reason databases use indexes? A) To reduce storage space B) To avoid full table scans C) To increase DML performance D) To prevent data corruption Answer: B Explanation: Indexes are primarily used to speed up data retrieval, avoiding costly full table scans. Question 2. Which of the following best describes an index in a database? A) A copy of all table data B) A data structure for faster lookups C) A backup feature D) A type of database constraint Answer: B Explanation: Indexes are specialized data structures designed to enable fast search and retrieval operations. Question 3. Which operation is negatively impacted by too many indexes? A) SELECT B) INSERT C) UPDATE D) Both B and C Answer: D Explanation: INSERT and UPDATE operations are slower with more indexes because indexes must also be updated during these operations.
Question 4. What is a full table scan? A) Reading only indexed columns B) Reading every row in a table C) Reading only the first row D) Skipping NULL values Answer: B Explanation: A full table scan reads all rows, which is what indexes aim to minimize for performance. Question 5. In terms of access methods, what is an Index Range Scan? A) Reads all rows sequentially B) Reads a contiguous set of index entries C) Finds only NULL values D) Only used with bitmap indexes Answer: B Explanation: Index Range Scan reads a sequence of rows that match a range condition, using the index. Question 6. What differentiates an Index Unique Scan from an Index Range Scan? A) Unique Scan returns at most one row B) Range Scan returns only NULL values C) Unique Scan always uses a bitmap D) Range Scan is slower Answer: A Explanation: An Index Unique Scan retrieves at most a single row, using a unique index. Question 7. How are indexes typically stored on disk?
B) They maintain sorted order of keys C) They cannot be used for range queries D) They are only for text data Answer: B Explanation: B-Tree indexes keep keys sorted, making range queries efficient. Question 11. In a B+ Tree, which nodes contain pointers to actual table data? A) Root nodes B) Branch nodes C) Leaf nodes D) All nodes Answer: C Explanation: In B+ Trees, only the leaf nodes point to actual table records. Question 12. What is the main performance benefit of balanced trees in B-Tree indexes? A) Faster full table scans B) Predictable search time O(log n) C) More storage space D) Slower updates Answer: B Explanation: Balanced trees ensure that search, insert, and delete operations have predictable logarithmic time complexity. Question 13. Hash indexes are best suited for which type of query? A) Range queries
B) Equality comparisons C) ORDER BY operations D) Grouping Answer: B Explanation: Hash indexes excel at equality comparisons due to hashing. Question 14. What is a limitation of hash indexes? A) Cannot be stored on disk B) Poor performance for range queries C) Require unique values D) Cannot index text columns Answer: B Explanation: Hash indexes do not support range queries efficiently because hashing destroys the order. Question 15. Bitmap indexes are most effective for columns with: A) High cardinality B) Low cardinality C) Text data only D) Unique values Answer: B Explanation: Bitmap indexes are ideal for columns with a small number of distinct values (low cardinality). Question 16. How do bitmap indexes handle AND/OR operations efficiently? A) Using hash joins
C) Reduces index storage D) Can have multiple per table Answer: B Explanation: Clustered indexes determine the physical order of rows, which speeds up range and sequential access. Question 20. How many clustered indexes can a table have? A) One B) Unlimited C) Two D) None Answer: A Explanation: A table can have only one clustered index because data can be physically ordered only one way. Question 21. What is a non-clustered index? A) Index that cannot be used for joins B) Index that does not affect physical row order C) Index that always uses a bitmap D) Index on the primary key Answer: B Explanation: Non-clustered indexes do not change the actual order of data in the table. Question 22. What is a composite index? A) Index on a single column B) Index on multiple columns
C) Index with only NULL values D) Index on a view Answer: B Explanation: Composite indexes cover two or more columns in a defined order. Question 23. Why does column order matter in a composite index? A) The leftmost columns are used first in searches B) The rightmost column is always used C) Order does not matter D) Only unique columns are considered Answer: A Explanation: The "Left-to-Right" rule means that searches benefit most from the leftmost columns in the index definition. Question 24. What is a covering index? A) Index that includes all columns needed by a query B) Index that covers only primary keys C) Index that replaces a table D) Index that uses bitmap storage Answer: A Explanation: A covering index contains all columns required by a query, eliminating the need to access the table. Question 25. What is the "Included Columns" feature in some databases' indexes? A) Ability to store extra columns in index leaf nodes B) Only include primary keys
B) Searching using a function like LOWER(email) C) Searching for NULL values D) Searching for exact matches Answer: B Explanation: If queries use a function like LOWER or UPPER, a function-based index makes such queries efficient. Question 29. What is selectivity in the context of indexes? A) Percentage of NULL values B) Fraction of unique values to total rows C) Table's storage size D) Number of indexes on a table Answer: B Explanation: Selectivity is the ratio of unique values; high selectivity usually means an index is more useful. Question 30. How does cardinality affect index usage? A) High cardinality columns are less useful for indexing B) High cardinality means more unique values, making indexes more effective C) Low cardinality always improves performance D) Cardinality is unrelated to indexes Answer: B Explanation: High cardinality means many unique values, making indexes more likely to be used for efficient lookups. Question 31. What is the role of the Query Optimizer regarding indexes?
A) Always uses indexes B) Decides whether to use an index or not C) Drops indexes D) Creates new indexes automatically Answer: B Explanation: The Query Optimizer analyzes queries and decides whether using an index is cost-effective. Question 32. What is the purpose of the EXPLAIN command? A) Modify table structure B) Show the query execution plan C) Create a new index D) Run a query without indexes Answer: B Explanation: EXPLAIN shows how a query will be executed, including whether indexes are used. Question 33. What does SARGability mean? A) Ability to run queries in parallel B) Ability to use indexes efficiently in a query C) Ability to use only clustered indexes D) Ability to skip table scans Answer: B Explanation: SARGable queries are those that can efficiently use indexes for filtering data. Question 34. Which WHERE clause is SARGable? A) WHERE YEAR(order_date) = 2023
B) Index pages are not physically contiguous C) Index is always unique D) Index is dropped automatically Answer: B Explanation: Fragmentation occurs when index pages are scattered, slowing down range scans. Question 38. What is internal fragmentation in indexes? A) Unused space inside index pages B) Out-of-order index pages C) Duplicate index entries D) Index stored in RAM Answer: A Explanation: Internal fragmentation refers to wasted space within the pages of the index. Question 39. What is external fragmentation in indexes? A) Index pages not in logical order B) Unused space within pages C) Indexes on binary columns D) Indexes with only NULL values Answer: A Explanation: External fragmentation is when the logical order of index pages does not match their physical order on disk. Question 40. When should an index be rebuilt? A) When statistics are stale
B) When fragmentation is high C) When DML operations are slow D) When queries return wrong results Answer: B Explanation: Rebuilding an index is typically done when fragmentation degrades performance. Question 41. What is the difference between rebuilding and reorganizing an index? A) Rebuilding removes and recreates the index; reorganizing defragments it in place B) Both are the same C) Rebuilding is online only D) Reorganizing drops the index Answer: A Explanation: Rebuilding recreates the index from scratch, while reorganizing defragments it without dropping. Question 42. What is the effect of stale index statistics? A) Index is dropped B) Query optimizer may make poor index choices C) Index is rebuilt D) Index becomes unique Answer: B Explanation: Stale statistics can lead to suboptimal query plans because the optimizer relies on accurate statistics. Question 43. What is a histogram in index statistics? A) Chart of table sizes
B) They must be updated during INSERT/UPDATE/DELETE C) They reduce storage D) They improve fragmentation Answer: B Explanation: Unused indexes add overhead during data modifications, as they must be kept in sync. Question 47. Why should foreign key columns be indexed? A) To speed up foreign key validation and joins B) To reduce table size C) To enable clustered indexes D) To prevent fragmentation Answer: A Explanation: Indexing foreign keys prevents table scans during join and integrity checks. Question 48. How do indexes help with ORDER BY clauses? A) They allow the database to skip explicit sorting B) They make queries slower C) They always increase fragmentation D) They prevent DML operations Answer: A Explanation: Indexes can provide pre-sorted data, reducing the need for an explicit sort operation. Question 49. How do indexes help GROUP BY performance? A) They store grouped data B) They allow for quick grouping by indexed columns
C) They disable other indexes D) They prevent aggregation Answer: B Explanation: Grouping on indexed columns is faster, as the index can be scanned in order. Question 50. How can indexes affect concurrency and locking? A) They eliminate all locking B) They can reduce row-level lock contention C) They prevent deadlocks D) They slow down SELECT Answer: B Explanation: Indexes can direct queries to specific rows, reducing the need for broad locks. Question 51. When can indexes cause deadlocks? A) When multiple indexes are updated in conflicting orders B) When SELECT is run C) When indexes are rebuilt D) When only clustered indexes exist Answer: A Explanation: Deadlocks can occur if transactions update multiple indexes in different orders. Question 52. What is an index join? A) Joining tables using only indexed columns B) Merging two indexes C) Joining indexes to tables
Answer: B Explanation: Inverted indexes are optimized for searching individual terms in text. Question 56. What is the effect of an index that is too wide (includes many columns)? A) Increases storage and update overhead B) Always improves performance C) Reduces fragmentations D) Makes statistics unnecessary Answer: A Explanation: Wide indexes use more storage and slow writes, potentially offsetting read benefits. Question 57. Which of the following is NOT a valid reason to drop an index? A) It is unused B) It is redundant C) It always speeds up queries D) It slows down DML Answer: C Explanation: Indexes are not always beneficial; unused or redundant ones should be dropped. Question 58. What is a filtered index in SQL Server? A) Index built with a WHERE clause to include only certain rows B) Index on a view C) Index with only unique values D) Index on a function Answer: A
Explanation: Filtered indexes are partial indexes built over a filtered subset of rows. Question 59. Which of the following is a disadvantage of too many indexes? A) Increased query speed B) Slower data modifications C) Reduced storage D) Faster table scans Answer: B Explanation: Too many indexes make INSERT/UPDATE/DELETE slower due to maintenance overhead. Question 60. What is index selectivity? A) The ratio of unique indexed values to total rows B) The number of indexes per table C) The number of table columns D) The number of queries per second Answer: A Explanation: Selectivity is the fraction of unique index values over total records. Question 61. What is index density? A) Number of unique values per index key B) Number of duplicate entries in an index C) Percentage of NULLs in an index D) Number of columns in an index Answer: B Explanation: Index density measures how many duplicate entries exist for a single key value.
