SQL Quiz: Index Basics
10 questions · exam conditions
0:00
Index BasicsQuestion 1 of 10

A transaction table is loaded continuously. After several indexes are added, read queries improve, but insert and update throughput decreases even though the rows have not become substantially larger.

Which factor most directly explains the decrease in write throughput?

Each data change may also require changes to one or more index structures and their pages.
Each index forces the DBMS to execute every insert once for each existing table row.
Each index converts inserted values to text before the values can be stored in the table.
Each data change must rerun every read query that previously benefited from an index.
← Back to quizzes

SQL Quiz

SQL Quiz: Index Basics

Practice Index Basics in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Index Basics, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A transaction table is loaded continuously. After several indexes are added, read queries improve, but insert and update throughput decreases even though the rows have not become substantially larger.

Which factor most directly explains the decrease in write throughput?

  1. Each data change may also require changes to one or more index structures and their pages. (correct answer)
  2. Each index forces the DBMS to execute every insert once for each existing table row.
  3. Each index converts inserted values to text before the values can be stored in the table.
  4. Each data change must rerun every read query that previously benefited from an index.
Explanation: When you see a question about index performance, think about the hidden work that indexes create behind the scenes. An index isn't a passive lookup table — it's a data structure (like a B-tree) that must stay synchronized with the actual table data at all times. Every time a row is inserted or updated, the database engine doesn't just write to the table — it must also locate the correct position in each index and update those structures accordingly. If you add five indexes to a heavily written table, each write operation now triggers up to five additional index page modifications. This multiplied I/O and structural maintenance is precisely why A is correct: data changes require changes to one or more index structures, directly reducing write throughput. B describes something that doesn't happen at all — indexes don't cause the engine to re-execute the insert for every existing row. That would be O(n) behavior per insert, which would make indexes completely unusable in practice. C is equally fictional; indexes store the actual column values (or derived keys) in their native data types, not converted text. There's no text-conversion step triggered by indexing. D confuses the direction of the relationship entirely — inserting new data doesn't cause the database to rerun old read queries. Read queries benefit from indexes at query time, not in reverse when writes occur. A useful mental model: think of each index as a separate "shadow copy" of certain columns that must be kept up to date. More indexes = more maintenance work per write. When diagnosing index tradeoffs on an exam, always ask yourself what write-side overhead each index introduces.

Question 2

A B-tree index exists on order_date. One query applies a year-extraction function to order_date in the predicate. A revised query instead compares order_date with lower and upper date boundaries representing the same year.

Why is the revised predicate often more likely to benefit from the index?

  1. A date range causes the DBMS to replace the existing B-tree with a temporary hash index optimized for that specific query.
  2. Applying a function to the column transforms all stored date values into a single unified search key, simplifying lookups.
  3. Direct boundaries can identify a contiguous key range in the index, while a function applied to the column may obscure the stored key values and prevent an efficient seek. (correct answer)
  4. Boundary comparisons force the optimizer to avoid a table scan entirely, regardless of how many rows satisfy the predicate.
Explanation: Whenever you see a question about index usage and query predicates, focus on how the database engine navigates a B-tree: it seeks by comparing stored key values directly. A B-tree stores order_date values in sorted order, so the engine can efficiently jump to a starting key and scan a contiguous range — but only when the predicate exposes those raw stored values. When you wrap a column in a function like YEAR(order_date) = 2023, the database must evaluate that function for every row before comparing the result. The stored keys are no longer directly comparable to your predicate, so the optimizer typically cannot perform an index seek — it may resort to a full index or table scan instead. The revised approach, using order_date >= '2023-01-01' AND order_date < '2024-01-01', speaks directly in terms of stored values. The optimizer can locate '2023-01-01' in the B-tree and scan forward to '2024-01-01' — a classic, efficient range seek. This is why C is correct: direct boundaries expose a contiguous key range, while a function obscures stored values and may prevent an efficient seek. A is wrong because DBMSs do not swap a B-tree for a temporary hash index per query — that's not how indexing works. B is wrong and backwards: applying a function doesn't simplify lookups; it complicates them by hiding the original key values. D is wrong because boundary comparisons don't force the optimizer to avoid a table scan regardless of selectivity — if a predicate matches most rows, a table scan may still be chosen. Remember this pattern: functions on indexed columns break index seeks. Rewrite predicates to isolate the raw column on one side of the comparison whenever possible.

Question 3

A report joins Customers to Orders using customer_id. The report first identifies a small set of customers, and an index exists on Orders.customer_id.

How can the index improve the join without changing the report's logical result?

  1. It can let the DBMS locate orders for each selected customer without repeatedly scanning all orders. (correct answer)
  2. It can cause customers without orders to be removed from the Customers table before the join runs.
  3. It can store the completed join permanently, so later reports need not evaluate the join condition.
  4. It can guarantee that each selected customer has exactly one order matching the indexed value.
Explanation: When a query joins two tables, the DBMS needs a strategy to find matching rows efficiently. Questions like this test whether you understand that indexes are access path optimizers — they change how data is retrieved, never what data is logically returned. Here's the core idea behind A: when your query first narrows down to a small set of customers, the DBMS then needs to find each customer's matching orders. Without an index on Orders.customer_id, the engine must scan every row in Orders for each customer — potentially millions of reads. With the index, it can jump directly to only the relevant rows for each customer, like using a book's index instead of reading every page. The logical result — which customers match which orders — is identical either way. A is correct because it accurately describes this index lookup (or nested-loop index join) behavior. B is wrong because indexes never modify or delete data from tables — they can't remove customers from Customers before a join runs. That would change the logical result, which the question explicitly rules out. C describes materialized views, not indexes. An index doesn't store a completed join result; it stores a sorted pointer structure to speed up lookups within a single table. D confuses an index with a constraint. An index on Orders.customer_id doesn't enforce a one-order-per-customer rule — only a UNIQUE constraint would do that, and even then it wouldn't guarantee any order exists. Study tip: When evaluating index questions, always ask: does this change performance (access path) or results (data returned)? Indexes only ever affect the former.

Question 4

A query retrieves customer_id and status while filtering on customer_id. An index contains both columns, and the optimizer completes the query without reading the base table.

What property of the index most directly permits this behavior?

  1. The index contains all columns needed for filtering and output, so it can cover the query. (correct answer)
  2. The index permanently caches every result for that customer, so the table is no longer consulted.
  3. The index guarantees that only one status exists for each customer, eliminating duplicate checks.
  4. The index converts the filter into a table constraint, causing unqualified rows to be deleted.
Explanation: When a query can be satisfied entirely from an index — without ever touching the base table — you're dealing with a covering index. Any time you see a scenario where the optimizer skips the base table, ask yourself: does the index contain every column the query needs, both for filtering and for output? That's exactly what's happening here. The index includes customer_id (used in the WHERE clause) and status (returned in the SELECT). Because the index holds all the data the query requires, the database engine never needs to follow a pointer back to the actual table rows. This is the definition of a covering index, and A correctly identifies this property — the index covers the query by containing all referenced columns. B is wrong because indexes don't cache query results at all. Result caching is a separate database feature; an index is a sorted data structure, not a result store. C is wrong because a covering index says nothing about uniqueness or duplicate elimination — a single customer could have multiple statuses, and the index would still cover the query just fine. D is a complete fabrication: indexes don't convert filters into constraints, and they certainly don't delete rows from a table. That confuses index behavior with something like a CHECK constraint or a DELETE statement. A practical tip: when you see "optimizer skips the base table" or "index-only scan," immediately think covering index — an index that includes every column touched by the query. On SQL exams, this concept is often tested by asking why the table isn't read, and the answer is always about column coverage, not caching or constraints.

Question 5

A DBMS supports clustered indexes. A large log table is clustered on event_time, and analysts frequently request events between two timestamps. Inserts generally use increasing timestamps.

Which statement best explains why this design can help the range queries?

  1. Clustering stores rows with nearby time keys near one another, allowing a range to be read more sequentially. (correct answer)
  2. Clustering creates a saved result set for every possible time interval requested by analysts.
  3. Clustering guarantees that timestamp predicates return only one row, regardless of duplicate times.
  4. Clustering keeps all log rows permanently in memory once the first time range has been queried.
Explanation: When you see a question about clustered indexes and query performance, focus on the physical storage model: a clustered index dictates the actual order in which rows are stored on disk, not just how they're looked up. Because the log table is clustered on event_time, rows with similar timestamps are physically adjacent on disk. When an analyst requests events between two timestamps, the database engine can locate the starting point via the index and then read consecutive disk pages sequentially until it reaches the end of the range. This sequential I/O is dramatically faster than random I/O, which would be required if matching rows were scattered across the disk. That's exactly what A describes — and it's the correct answer. B is wrong because clustered indexes don't precompute or cache result sets for queries. That would describe something closer to a materialized view, which is an entirely different structure that must be explicitly created. C is wrong because clustering has no effect on row uniqueness or duplicate elimination. A clustered index can absolutely contain duplicate key values (unless a unique constraint is added separately). Returning one row per timestamp is a uniqueness concern, not a storage-order concern. D is wrong because clustered indexes involve disk organization, not memory persistence. Keeping rows "permanently in memory" describes a caching or in-memory database concept, not how clustered indexes function. Pages may be cached temporarily by the buffer pool, but nothing is guaranteed to stay in memory indefinitely. As a study tip, always distinguish between how data is physically stored (clustered index), how it's looked up (non-clustered index), and how results are cached (materialized views, buffer pools) — exam questions frequently mix these concepts as distractors.

Question 6

A table named Orders contains several million rows. Most queries retrieve a small number of orders by customer_id. After a nonclustered index is created on customer_id, these queries usually perform fewer data-page reads.

Which explanation best accounts for the reduction in page reads?

  1. The index stores the complete table in memory, so qualifying rows no longer require disk access.
  2. The index rearranges every table row by customer, so unrelated rows are physically removed from each page.
  3. The index provides a searchable key structure with references to rows, reducing the need to scan unrelated pages. (correct answer)
  4. The index saves the results of earlier customer searches, allowing later queries to reuse the same result set.
Explanation: When a question asks why an index reduces page reads, you should think about what an index actually is: a separate, ordered data structure that maps key values to row locations, letting the database engine jump directly to relevant rows instead of scanning everything. A nonclustered index on customer_id builds a balanced tree (B-tree) of customer ID values, each paired with a pointer to the actual row's location in the table. When a query filters by customer_id, the engine traverses this compact structure, finds only the matching row pointers, and fetches just those pages — skipping millions of unrelated rows entirely. That's exactly what C describes, and it's the correct answer. A is wrong because an index does not store the full table in memory, nor does it eliminate disk access. It simply makes disk access more targeted. Memory caching is a separate mechanism (like a buffer pool) and isn't what the index itself provides. B is wrong because a nonclustered index does not physically reorder table rows — that's what a clustered index does. The underlying table pages remain in their original order; only the index structure is sorted by customer_id. This is a classic trap: confusing clustered and nonclustered behavior. D is wrong because indexes are not query result caches. They don't store previous search results or reuse them across queries. That describes a caching or memoization layer, not an index. As a study tip: always distinguish between clustered indexes (change physical row order), nonclustered indexes (separate lookup structure with row pointers), and caches (store results). Exam questions frequently blur these three concepts.

Question 7

An Events table has an index on is_processed. A query filters for is_processed = TRUE, but approximately 90 percent of the rows satisfy that condition. The optimizer chooses a full table scan.

Why can the table scan be less expensive even though an applicable index exists?

  1. An index can be used only when the predicate matches exactly one row in the table.
  2. Retrieving most rows through index entries can require more work than reading the table sequentially. (correct answer)
  3. Boolean columns cannot be indexed because their values do not establish a meaningful key order.
  4. Creating an index disables scanning for predicates that return more than half of the table.
Explanation: Whenever you see a question about query optimization, think about cost, not just capability. The optimizer doesn't ask "can I use this index?" — it asks "should I?" An index speeds up lookups by letting the database jump directly to matching rows instead of reading everything. But here's the catch: index-based retrieval isn't free. For each matching entry, the database must follow a pointer from the index to the actual data page on disk — a process called a random I/O. When 90% of rows match your filter, you're essentially jumping to 90% of the data pages anyway, but in a scattered, random order. Sequential reads (a full table scan) are far more efficient because the database reads pages in order, minimizing disk seeks. At high selectivity like 90%, the index actually creates more work, not less. That's why the optimizer correctly chooses the full scan — answer B. A is wrong because indexes are frequently used for ranges, patterns, and multi-row matches. The idea that an index only works for single-row lookups is a misconception with no basis in how indexes function. C is wrong because Boolean columns can be indexed — there's no rule against it. The issue here is selectivity, not data type. An index on a Boolean column is simply low-selectivity when one value dominates. D is wrong because no such rule exists. The optimizer makes cost-based decisions dynamically; creating an index never "disables" scanning behavior. As a study tip, remember: low selectivity kills index usefulness. When a predicate matches a large percentage of rows, always suspect the optimizer will prefer a full scan.

Question 8

A regular, non-unique index is created on the email_address column of a customer table. The application assumes this change will reject any future duplicate email addresses.

Which assessment of the application's assumption is correct?

  1. The assumption is correct because all indexes require every stored key value to be distinct.
  2. The assumption is correct only when duplicate rows would be placed on different data pages.
  3. The assumption is incorrect because indexes improve lookup speed but never support uniqueness rules.
  4. The assumption is incorrect because a regular index can contain duplicates; uniqueness must be enforced separately. (correct answer)
Explanation: When working with database indexes, you need to clearly separate two distinct concepts: query performance and data integrity enforcement. A regular index is a performance tool — it creates a sorted data structure that speeds up lookups, but it makes no promises about the uniqueness of the values it indexes. A standard (non-unique) index happily stores duplicate key values. If two customers register with the same email address, the index will index both rows without complaint. The database engine simply records both entries and points to their respective rows. Because the index itself has no uniqueness constraint, no error is raised and the duplicate is silently accepted. That's exactly why D is correct — the application's assumption is broken, and uniqueness must be enforced through a separate mechanism, either a UNIQUE index or a UNIQUE constraint on the column. A reflects a common misconception — confusing a regular index with a unique index. Only a UNIQUE index requires every key value to be distinct; the word "unique" must be explicitly specified. B is nonsense from a database standpoint; where rows are physically stored on data pages has no bearing on whether duplicates are allowed. C goes too far in the opposite direction. It's true that a regular index doesn't enforce uniqueness, but the claim that indexes never support uniqueness rules is false — UNIQUE indexes do exactly that. Your study tip: whenever a question mentions an index, ask yourself what type. A plain index = performance only. A UNIQUE index = performance plus uniqueness enforcement. That distinction is a frequent exam trap.

Question 9

A B-tree index is defined on Sales(customer_id, sale_date) in that order. A query filters only on sale_date and does not restrict customer_id.

Which statement most accurately describes the likely usefulness of this index for locating the qualifying rows?

  1. It guarantees a direct seek by date because every indexed column can independently serve as the leading key.
  2. It is unusable for every purpose because all composite-index columns must appear in every query predicate.
  3. It automatically behaves as a separate date-only index whenever the first key column is omitted.
  4. It may not support an efficient date seek because entries are ordered first by customer_id, then by sale_date. (correct answer)
Explanation: When you see a question about composite indexes, the key concept to recall is the leftmost prefix rule: a B-tree index on (customer_id, sale_date) sorts rows first by customer_id, and only within each customer_id group are rows sorted by sale_date. This physical ordering is what determines whether the index can efficiently locate rows matching a given predicate. Because the index entries are interleaved across all customers before being ordered by date, filtering only on sale_date means the matching rows are scattered throughout the entire index structure — there's no contiguous range the database can seek to. The optimizer will typically skip the index entirely and perform a full table scan instead. This makes D the correct answer: the index may not support an efficient date seek precisely because customer_id is the leading key. A is wrong because columns in a composite index do not independently serve as leading keys — only the leftmost column(s) do. B goes too far in the opposite direction; composite indexes aren't universally useless when some columns are omitted. If you filter on customer_id alone (the leading column), the index remains useful. The flaw is requiring all columns in the predicate — that's simply not the rule. C describes behavior that doesn't exist; B-tree indexes don't reorganize themselves or create alternate entry points when the leading column is absent. Study tip: Remember "leftmost prefix" as your mental model for composite indexes. Ask yourself: "Does my query predicate include the first column of the index?" If not, expect the index to be skipped or used only inefficiently.

Question 10

A query returns all employees ordered by last_name, first_name. A B-tree index is defined on last_name, first_name in the same key order.

What performance benefit can this index provide even though the query does not filter any rows?

  1. It can eliminate the need to read employee rows because ordering supplies all unindexed output columns.
  2. It can make sorting unnecessary if the optimizer reads entries in the required index order. (correct answer)
  3. It can reduce the result to one employee per last name because adjacent keys are duplicates.
  4. It can force the query to use parallel execution because ordered indexes require multiple workers.
Explanation: When thinking about index performance, don't limit yourself to filtering — indexes also help the database avoid work it would otherwise do. A B-tree index stores rows in sorted key order, which means the database can walk the index leaf pages sequentially and retrieve rows already arranged the way the query needs them. This is exactly what makes B correct. When your query orders by last_name, first_name and the index is defined on those same columns in that same order, the optimizer can perform an index scan and return rows in sorted sequence without ever invoking a separate sort step. Sorting is expensive — it often requires writing data to temporary storage — so eliminating it is a genuine performance gain even when zero rows are filtered out. A is wrong because an index on last_name, first_name only stores those two columns plus the row pointer. If the SELECT clause includes other columns (like salary or email), the database must still fetch the full employee rows — the index alone cannot supply all the output data. A covering index would need to include every selected column. C describes something that simply doesn't happen. A B-tree index doesn't collapse or deduplicate rows; it preserves every row individually. Adjacent keys may share the same last name, but the database returns all of them. D is a fabrication. Index ordering has no inherent connection to parallel execution. Parallel plans are chosen based on cost estimates and optimizer settings, not whether the index is sorted. Study tip: Remember the two index superpowers — filtering (reducing rows) and ordering (avoiding sorts). Exam questions often isolate one to see if you recognize the other.