Storage Structures and Indexing

Introduction

A database management system (DBMS) must store, locate, modify, and recover data efficiently. Although users work with relations, tuples, and SQL statements, the database engine operates primarily on pages, records, files, buffers, and indexes. The design of these structures determines whether a query completes in milliseconds or requires scanning millions of records.

Storage design is especially important in operational systems such as banking, retail, healthcare, logistics, and online services. These systems process frequent inserts, updates, and point lookups while also supporting range queries and reporting. A suitable organization must therefore balance access speed, storage overhead, update cost, concurrency, and recovery requirements.

This chapter explains the storage hierarchy and the internal organization of database files. It compares heap, sequential, and clustered organization and examines B-trees, B+ trees, hash indexes, bitmap indexes, and composite indexes. The chapter also presents design calculations, an applied case, implementation guidance, and limitations relevant to modern data-intensive applications.

Learning Objectives

After studying this chapter, students should be able to:

1. Explain pages, records, files, buffers, and the storage hierarchy.

2. Calculate record blocking and estimate I/O requirements.

3. Compare heap, sequential, and clustered file organization.

4. Explain the structure and operation of B-tree and B+ tree indexes.

5. Select between tree, hash, bitmap, and composite indexes for operational workloads.

6. Analyze index benefits, maintenance costs, and workload trade-offs.

7. Apply storage and indexing principles to a realistic database design.

1. The Storage Hierarchy and the I/O Model

Computer storage is organized as a hierarchy. At the highest levels are CPU registers and cache, followed by main memory (RAM), solid-state drives (SSDs), and magnetic disks. Lower levels generally provide greater capacity but higher access latency. Database systems exploit this hierarchy by keeping frequently used pages in memory and transferring data between storage levels in relatively large blocks.

A simplified hierarchy is:

• CPU registers and processor cache: extremely fast, very small, volatile.
• Main memory: fast, moderate capacity, volatile.
• SSD or magnetic disk: persistent, large capacity, slower than RAM.
• archival and remote storage: persistent, high capacity, relatively slow access.

The basic unit transferred between secondary storage and memory is usually a page, also called a block. Common database page sizes include 4 KiB, 8 KiB, 16 KiB, or larger values, although the exact size depends on the DBMS and workload. A query that reads one qualifying row may still require the entire page containing that row to be fetched.

For many database operations, the dominant cost is not CPU computation but page I/O. A useful simplified model is:

\[

T \approx N_{IO} \times t_{page}

\]

where \(T\) is approximate I/O time, \(N_{IO}\) is the number of page transfers, and \(t_{page}\) is the average time per transfer. This is an approximation: real systems also experience caching, parallelism, queueing, prefetching, compression, and varying SSD behavior.

The model is valuable because it explains why an index can be effective even when it adds CPU work. If an index reduces a query from thousands of page reads to a few page reads, the saving can be substantial.

2. Pages, Records, and Files

2.1 Records

A record is the physical representation of a tuple. It contains fields, metadata, and sometimes a record identifier. Records may be:

• Fixed-length, where every record occupies the same number of bytes.
• Variable-length, where fields such as names, descriptions, or JSON documents have different sizes.

Fixed-length records are easy to locate because the address of record \(i\) can be calculated from the page location and record size. Variable-length records require additional metadata, such as offsets, lengths, or a slot directory.

A record may contain a logical primary key, but its physical location is commonly represented by a record identifier (RID), often consisting of a page identifier and a slot number. An RID can remain stable even when records have variable lengths, provided that the DBMS manages movement through indirection or slot metadata.

2.2 Pages

A page contains a collection of records plus control information. A typical page may include:

• a page header;
• record data;
• free-space information;
• a slot directory or line-pointer array;
• transaction or recovery metadata.

A slotted page is particularly useful for variable-length records. The records are stored in one region and the slot directory in another. A slot points to a record's offset and length. If a record moves within the page, the slot can be updated while the record identifier remains unchanged.

Records may be stored in two broad ways:

• Unspanned records cannot cross page boundaries. This simplifies access but may waste unused space at the end of a page.
• Spanned records may cross page boundaries. This uses space more efficiently but requires additional logic to follow the record across pages.

Many systems use page-level locking, latching, logging, and buffering. Thus, page design affects not only storage efficiency but also concurrency and recovery.

2.3 Blocking Factor

For fixed-length records, the approximate number of records that fit in a page is called the blocking factor. If page size is \(P\) bytes and record size is \(R\) bytes, the unspanned blocking factor is:

\[

bfr = \left\lfloor \frac{P}{R} \right\rfloor

\]

This formula assumes that page and record headers are ignored or have already been included in the relevant sizes. If a page has header overhead \(H_p\), the more precise expression is:

\[

bfr = \left\lfloor \frac{P-H_p}{R} \right\rfloor

\]

If a file contains \(N\) records, the number of data pages for unspanned fixed-length records is approximately:

\[

B = \left\lceil \frac{N}{bfr} \right\rceil

\]

These equations do not directly apply to variable-length records unless an average record size and space-management assumptions are used.

Worked Example: Page Capacity

Suppose a table has 100,000 fixed-length records. Each record is 200 bytes, the page size is 8,192 bytes, and page-header overhead is 192 bytes.

Available data space is:

\[

8192 - 192 = 8000 \text{ bytes}

\]

The blocking factor is:

\[

bfr = \left\lfloor \frac{8000}{200} \right\rfloor = 40

\]

The number of data pages is:

\[

B = \left\lceil \frac{100000}{40} \right\rceil = 2500

\]

A complete scan of the table therefore requires approximately 2,500 data-page reads if no pages are already cached and no additional allocation or metadata pages are counted.

2.4 Files and Extents

A file is a persistent collection of pages representing a table, index, temporary result, or system object. The DBMS maintains metadata describing the file, including its size, allocated pages, free space, and storage location.

Pages are often allocated in groups called extents. Extent-based allocation reduces the overhead of requesting individual pages and can improve sequential access. However, fragmentation may arise when pages are repeatedly allocated and released.

A table can be organized as one or more files. An index is usually stored separately from its base table, although some systems support integrated or clustered structures in which data rows are stored with the index organization.

3. Buffer Management

Because secondary storage is slower than memory, a DBMS maintains a buffer pool in RAM. The buffer pool contains frames into which database pages are loaded. A buffer manager handles requests such as:

1. Check whether the requested page is already in a buffer frame.

2. If present, return the page without disk I/O.

3. If absent, select a frame for replacement.

4. Write the old page to storage if it is dirty and must be preserved.

5. Read the requested page into the frame.

6. Pin the page while an operation is using it.

A page is dirty when it has been modified in memory but the change has not yet been written to persistent storage. A page may be pinned to prevent replacement while an operator is using it. Once use is complete, the page is unpinned.

Replacement policies include least recently used (LRU), clock-based policies, and workload-aware strategies. LRU is intuitive but can perform poorly for large sequential scans because a scan can evict pages that are more useful to other transactions. Database systems therefore often distinguish scan-resistant access from frequently reused pages.

The buffer manager also interacts with recovery. Under write-ahead logging, relevant log records must reach stable storage before a dirty data page can be written. This ensures that recovery can redo or undo changes as required.

4. File Organization

File organization determines how records are placed in data pages. It should be selected according to the workload rather than by habit.

4.1 Heap Organization

A heap file stores records in no particular order. New records may be placed in any page with sufficient free space or appended to newly allocated pages.

Advantages include:

• fast insertion, especially when records are appended;
• simple implementation;
• good performance for full-table scans;
• flexibility for arbitrary record arrival order.

Disadvantages include:

• poor performance for searches without an index;
• expensive range retrieval because qualifying records may be scattered;
• possible fragmentation and uneven free-space distribution.

Heap organization is suitable for staging tables, event logs, small relations, and tables accessed primarily through secondary indexes.

4.2 Sequential or Sorted Organization

A sequential file stores records in the order of a search key, often a primary key. Sequential organization supports efficient ordered scans and range processing. If records are physically ordered by transaction date, for example, a date-range report can be processed with good locality.

The main disadvantage is insertion. Inserting a record into the correct position may require moving records or maintaining overflow pages. Deletions can also create gaps. Periodic reorganization may be necessary.

Sequential files are useful for read-heavy workloads, batch processing, archival data, and applications that process records in key order.

4.3 Clustered Organization

A clustered organization stores records physically near other records having similar values of a clustering key. The clustering key need not be unique. A table can be clustered by customer identifier, date, or geographic region.

Clustering is particularly useful for range queries and joins that access groups of related rows. For example, storing all orders for the same customer near one another reduces the number of pages needed to retrieve that customer's order history.

The term clustered index has different implementation details across DBMS products. In some systems, the clustered index determines the physical order of the table; in others, a table is stored as a B+ tree whose leaf pages contain complete rows. A table generally has only one principal physical clustering order, although multiple nonclustered indexes can exist.

Clustering is not automatically beneficial. Random inserts can cause page splits, and updates to the clustering key may move records. The best clustering key usually has high query relevance, reasonable insertion behavior, and sufficient locality.

5. Index Fundamentals

An index is an auxiliary access structure containing search-key values and references to records or pages. It is analogous to an index in a book: it avoids examining every item when only a subset is needed.

An index may be:

• Dense, with an entry for every search-key value or record;
• Sparse, with entries for only some values or pages;
• Primary, built on a primary or ordering key;
• Secondary, built on a nonordering attribute;
• Clustered, whose order corresponds to the physical organization;
• Nonclustered, whose order does not determine table storage.

An index typically has three components: a search key, a pointer or RID, and an access structure that organizes entries. A nonunique key may point to a list of RIDs or to a posting structure.

Indexes improve reads but impose costs. Every insert, delete, or relevant update may require index maintenance. Indexes also consume storage, memory, and backup time. An index should therefore support a significant workload rather than be created merely because an attribute appears in a table.

6. B-Trees and B+ Trees

6.1 B-Tree Structure

A B-tree is a balanced, multiway search tree. Each node contains multiple ordered keys and child pointers. All leaves occur at the same depth, so search cost is predictable. Nodes are usually sized to fit one or more pages, minimizing page I/O.

For a B-tree of order \(m\), a node has at most \(m\) child pointers and at most \(m-1\) keys. Exact minimum occupancy conventions vary by definition and implementation, but nonroot nodes are generally required to remain at least partly full. Splitting and merging preserve balance.

During insertion, if a node becomes full, it is split and a separator key is promoted to the parent. During deletion, an underfull node may borrow a key from a sibling or merge with a sibling.

6.2 B+ Tree Structure

A B+ tree is a variant in which internal nodes contain separator keys and child pointers, while actual data entries or record pointers are stored at the leaf level. Leaf nodes are commonly linked from left to right.

This design has important benefits:

• internal nodes hold more pointers because they do not store full data records;
• tree height is usually small;
• equality searches and range searches use a uniform leaf-level representation;
• linked leaves support efficient ordered traversal;
• internal nodes can be cached effectively.

For most relational database indexing, B+ trees are more common than traditional B-trees.

6.3 Search and Range Operations

To search a B+ tree, the DBMS follows separator keys from the root to the appropriate leaf. The leaf entry then identifies a record, page, or list of records. If the index is clustered, qualifying records may occupy relatively few data pages. If it is nonclustered and the query returns many rows, following many scattered pointers may be expensive.

For a range query, the DBMS finds the first qualifying leaf entry and follows leaf links until the upper bound is reached. This makes B+ trees suitable for predicates such as:

```sql

WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'

```

Worked Example: Approximate B+ Tree Height

Assume an index page is 8,192 bytes. Suppose each internal entry requires 16 bytes for a separator key and child pointer, and 256 bytes are reserved for page metadata. The approximate fan-out is:

\[

f = \left\lfloor \frac{8192-256}{16} \right\rfloor = 496

\]

This is an idealized estimate; real layouts include slot arrays, alignment, variable-length keys, and occupancy margins.

If the index has 1,000,000 leaf entries and each leaf page stores 100 entries, approximately 10,000 leaf pages are required. At a fan-out near 496, one internal level can address approximately 496 leaf pages, while two internal levels can address approximately:

\[

496^2 = 246016

\]

Thus, three levels of internal/leaf navigation may be sufficient for this scale, depending on root structure and actual occupancy. The example demonstrates why tree indexes often require only a small number of page reads.

7. Hash Indexes

A hash index applies a hash function to a search-key value and uses the result to identify a bucket. A bucket contains one or more pages of index entries or records. Hash indexes are optimized for equality predicates:

```sql

WHERE account_id = 784231

```

They are generally not appropriate for ordered operations such as:

```sql

WHERE account_id BETWEEN 700000 AND 800000

ORDER BY account_id

```

because hash values do not preserve the ordering of original keys.

Collisions occur when different keys map to the same bucket. They may be handled through overflow pages, chaining, or dynamic hashing. Static hashing can suffer from skew and growth problems. Extendible hashing and linear hashing adapt the bucket structure as the file expands, although they add implementation complexity.

Hash indexes are attractive for highly selective equality lookups, but their performance depends on a well-distributed hash function and appropriate capacity planning. They may be less useful when the workload also requires range searches, ordering, prefix matching, or minimum/maximum operations.

8. Bitmap Indexes

A bitmap index represents the presence of a value using bit vectors. For each distinct value, the index may contain a bitmap with one bit per row or row position. Bitwise operations such as AND, OR, and NOT can combine predicates efficiently.

Bitmap indexes are effective when:

• the indexed attribute has low or moderate cardinality;
• queries combine several filtering conditions;
• the workload is read-heavy;
• batch loading or controlled updates are common.

Examples include gender, region, status, or product category. They are often used in analytical systems rather than high-update transaction-processing systems.

Frequent row-level updates can make bitmap maintenance expensive, particularly when locking or compression characteristics cause many bits or bitmap segments to change. Therefore, bitmap indexes should be evaluated carefully for operational workloads.

9. Composite Indexes

A composite index uses two or more columns. Consider:

```sql

CREATE INDEX ix_orders_customer_date

ON Orders(customer_id, order_date);

```

This index is ordered first by `customer_id` and then by `order_date` within each customer. It efficiently supports queries that constrain the leading column, such as:

```sql

WHERE customer_id = 42

```

and queries that constrain both columns, such as:

```sql

WHERE customer_id = 42

AND order_date >= '2025-01-01'

```

The index may be less effective for a query that constrains only `order_date`, because the leading portion of the index is `customer_id`. This is often called the leftmost-prefix principle.

Column order should reflect common predicates, selectivity, join conditions, sorting requirements, and covering opportunities. Selectivity alone does not determine the best order. A less selective first column may be appropriate if queries nearly always constrain it and then perform a range search on the second column.

A composite index can be covering when it contains all columns needed by a query. In that case, the DBMS may answer the query using only the index, avoiding base-table lookups. Covering indexes can greatly reduce I/O but increase storage and update cost.

10. Choosing an Index for an Operational Workload

Index selection should begin with workload analysis:

1. Identify frequent and business-critical queries.

2. Record predicates, joins, grouping, ordering, and projected columns.

3. Estimate result cardinality and update frequency.

4. Examine execution plans and actual I/O.

5. Add or modify indexes incrementally.

6. Re-measure both read and write performance.

General guidance is as follows:

• Use a B+ tree for equality, range, ordering, and prefix-oriented access.
• Use a hash index mainly for equality lookups when range access is not required.
• Use a bitmap index for low-cardinality, read-heavy filtering, especially in analytical environments.
• Use a composite index when queries repeatedly use a combination of columns.
• Prefer a clustered organization for important range scans or locality-sensitive access.
• Avoid indexes on columns that change frequently unless the read benefit justifies maintenance.
• Avoid creating multiple indexes with nearly identical leading columns.

The optimizer considers selectivity, estimated cardinality, available statistics, clustering, page costs, and alternative access paths. Poor or outdated statistics can lead to a bad plan even when a suitable index exists.

11. Applied Case: Order Processing for an Online Retailer

Background

Consider an illustrative online retailer with an `Orders` table containing 80 million rows. The main operational queries retrieve orders by order identifier, retrieve recent orders for a customer, and locate unfulfilled orders for warehouse processing. Orders are inserted continuously, while status changes occur several times during the order lifecycle.

Problem

A heap-organized table with only an index on `order_id` provides fast point lookups but performs poorly for customer history and warehouse queues. A query for all unfulfilled orders in a warehouse scans a large portion of the table. Creating indexes on every column improves some reads but causes substantial insert and update overhead.

Analysis

The workload contains three distinct access patterns:

1. Exact lookup by unique `order_id`.

2. Equality plus range access by `customer_id` and `order_date`.

3. Filtering by `warehouse_id` and low-cardinality `status`, often ordered by `order_date`.

The third pattern is operational and update-heavy. A bitmap index on status could be expensive because status changes frequently. A B+ tree composite index is more appropriate.

Approach

A reasonable design is:

```sql

CREATE UNIQUE INDEX ix_orders_id

ON Orders(order_id);

CREATE INDEX ix_orders_customer_date

ON Orders(customer_id, order_date DESC);

CREATE INDEX ix_orders_warehouse_status_date

ON Orders(warehouse_id, status, order_date);

```

The first index supports direct order retrieval. The second supports customer history and recent-order queries. The third supports warehouse queries that specify both warehouse and status and then process orders by date.

If warehouse queries almost always request only a small set of columns, included or covering columns may reduce base-table access, subject to the DBMS's syntax and storage costs. The table may also be partitioned by order date if retention, archival, and time-based maintenance are important. Partitioning is not a replacement for indexing; it reduces the search space while indexes improve access within partitions.

Outcome

The design reduces page reads for the dominant queries while avoiding a bitmap index on a frequently changing status column. However, the indexes increase insert cost and storage consumption. Monitoring is required to confirm that the warehouse composite index is used and that its maintenance cost remains acceptable as data grows.

Learning Points

• Indexes should be designed from actual workload patterns.
• Composite column order determines which predicates can use an index efficiently.
• Low cardinality does not automatically justify a bitmap index.
• Partitioning and indexing solve related but different problems.
• Read performance must be evaluated together with insert and update cost.

12. Challenges, Governance, and Contemporary Developments

Storage systems face several practical challenges. Data growth can increase index height, page splits, fragmentation, and backup duration. Skewed data can make optimizer estimates inaccurate. Variable-length attributes and large objects can reduce page occupancy. Cloud storage introduces additional concerns involving remote latency, throughput limits, elasticity, and cost-based tiering.

Security is also relevant. Indexes can expose access patterns or retain values that administrators did not expect to be duplicated. Sensitive columns should be protected through access control, encryption, masking, and appropriate key management. Index design must not bypass row-level security or create unauthorized inference channels.

Modern systems increasingly use NVMe storage, persistent-memory technologies, compressed indexes, adaptive caching, learned workload statistics, and automatic index recommendation tools. Machine-learning-based advisors can identify candidate indexes from query histories, but recommendations must be validated. An automated tool may overlook write amplification, storage budgets, data sensitivity, or future workload changes.

In distributed databases, local indexes, global indexes, partitioning, and data placement interact. An index that is efficient on one node may require expensive coordination across nodes. Consequently, indexing decisions should consider locality, replication, consistency, and failure recovery rather than only single-node query latency.

13. Best Practices

1. Measure query performance using execution plans and actual I/O rather than assumptions alone.

2. Keep statistics current, especially after major data distribution changes.

3. Design indexes around frequent predicates, joins, and ordering requirements.

4. Consider the leftmost-prefix behavior of composite indexes.

5. Monitor insert, update, delete, storage, and maintenance overhead.

6. Remove unused or redundant indexes after validating their impact.

7. Use clustering when physical locality provides a clear workload benefit.

8. Reserve bitmap indexes mainly for suitable read-heavy and low-update workloads.

9. Account for page splits, fragmentation, and fill-factor choices where supported.

10. Protect sensitive data and ensure that indexing complies with organizational and regulatory requirements.

Chapter Summary

Database storage is organized around pages, records, files, buffers, and a hierarchy ranging from CPU cache to persistent storage. Pages are the principal unit of database I/O, and blocking-factor calculations help estimate file size and scan cost. Buffer managers reduce physical I/O by caching pages and coordinating replacement, pinning, dirty-page handling, and recovery requirements.

Heap files provide simple and flexible insertion, sequential files support ordered processing, and clustered organizations improve locality for related records and range queries. Indexes provide alternative access paths but impose storage and maintenance costs. B+ trees are versatile structures for equality, range, and ordered access. Hash indexes are specialized for equality searches, while bitmap indexes are useful for low-cardinality, read-heavy filtering. Composite indexes support multi-column workloads, but their effectiveness depends strongly on column order and the leftmost-prefix principle.

Professional index design requires workload analysis, execution-plan examination, statistical awareness, and evaluation of both read and write behavior. Contemporary systems add cloud, distributed, security, and automated-advisor considerations. The central design principle is to select storage and indexing structures that match actual access patterns and operational constraints.

Key Terms

Page: The basic block transferred between database storage and memory.

Record: The physical representation of a tuple or row.

RID: A record identifier that typically refers to a page and slot.

Slotted page: A page format using a slot directory to locate variable-length records.

Blocking factor: The number of records that fit in one page under stated assumptions.

File organization: The method used to arrange records in database pages.

Heap file: An unordered collection of records.

Sequential file: A file whose records are stored in search-key order.

Clustered organization: Physical arrangement that places related key values near one another.

Buffer pool: Main-memory area used to cache database pages.

Dirty page: A buffered page modified in memory but not yet written to storage.

Index: An auxiliary access structure that helps locate records.

B+ tree: A balanced multiway tree with data entries normally stored at leaf level.

Fan-out: The number of child pointers supported by an internal tree node.

Hash index: An index that maps key values to buckets using a hash function.

Bitmap index: An index representing value membership with bit vectors.

Composite index: An index built on multiple columns.

Covering index: An index containing all data needed by a particular query.

Selectivity: The degree to which a predicate reduces the number of qualifying rows.

Page split: The division of a full index or data page to accommodate additional entries.

Review Questions

Short Answer Questions

1. What is a database page, and why is it important for query performance?

2. Distinguish between a heap file and a sequential file.

3. What is the purpose of a buffer pool?

4. Why are B+ trees generally suitable for range queries?

5. State the leftmost-prefix principle for composite indexes.

Long Answer Questions

1. Explain the storage hierarchy and discuss how page I/O influences DBMS performance.

2. Compare heap, sequential, and clustered file organization, including suitable workloads and limitations.

3. Describe B+ tree search, insertion, and range scanning operations.

4. Compare B+ tree, hash, and bitmap indexes in terms of access patterns, update cost, and workload suitability.

5. Explain the factors that should be considered when designing composite indexes for an operational database.

Analytical / Application Questions

1. A table contains 240,000 fixed-length records of 160 bytes. Pages are 8,192 bytes with 192 bytes of header overhead. Calculate the blocking factor and approximate number of data pages.

2. A warehouse query filters by `warehouse_id` and `status`, then orders by `order_date`. Propose an index and explain how column order affects its use.

3. An application performs frequent equality lookups, range searches, and status updates on the same table. Recommend an indexing strategy and justify why a single index type may not be optimal for all predicates.

Multiple Choice Questions with Answers

Question 1

What is the principal unit typically transferred between secondary storage and the DBMS buffer pool?

A. Field

B. Page

C. Tuple value

D. SQL statement

Correct Answer: B. Page

Question 2

A page has 8,192 bytes, 192 bytes of header overhead, and fixed records of 200 bytes. What is the unspanned blocking factor?

A. 39

B. 40

C. 41

D. 42

Correct Answer: B. 40

Question 3

Which file organization generally provides the simplest and fastest insertion without maintaining key order?

A. Heap organization

B. Sequential organization

C. Clustered organization

D. Bitmap organization

Correct Answer: A. Heap organization

Question 4

Where are data entries or record pointers normally stored in a B+ tree?

A. Only in the root

B. Only in internal nodes

C. At the leaf level

D. In an unrelated heap file only

Correct Answer: C. At the leaf level

Question 5

Which operation is a hash index primarily designed to support?

A. Equality lookup

B. Full ordering by key

C. Arbitrary range scan

D. Minimum and maximum traversal

Correct Answer: A. Equality lookup

Question 6

Which workload is most suitable for a bitmap index?

A. Frequently updated high-cardinality transaction identifiers

B. Read-heavy filtering on low-cardinality attributes

C. Large-object storage and retrieval

D. Random text-prefix insertion

Correct Answer: B. Read-heavy filtering on low-cardinality attributes

Question 7

Given an index on (customer_id, order_date), which query is most directly supported by the leading-column structure?

A. A query filtering only order_date

B. A query filtering customer_id and an order_date range

C. A query filtering an unrelated product_id

D. A query with no predicate or ordering

Correct Answer: B. A query filtering customer_id and an order_date range

Question 8

What is a dirty page?

A. A page containing invalid SQL

B. A page modified in memory but not yet persisted

C. A page that has no free space

D. A page containing duplicate keys

Correct Answer: B. A page modified in memory but not yet persisted

Question 9

Why can a nonclustered index become expensive for a query returning many rows?

A. It cannot store keys

B. It may require many scattered base-table page accesses

C. It always prevents sorting

D. It eliminates buffer pooling

Correct Answer: B. It may require many scattered base-table page accesses

Question 10

Which statement best describes a covering index?

A. It contains all columns needed by a query

B. It physically covers an entire disk

C. It can only contain a primary key

D. It is always a bitmap index

Correct Answer: A. It contains all columns needed by a query