Indexing Internals interview questions

1. A.the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
2. B.the ratio of distinct values to total rows for a column, where a high value makes the index more effective at filtering
3. C.an index that includes every column a query needs so the engine answers it from the index alone without touching the table heap
4. D.the default balanced-tree structure that keeps keys sorted and supports equality and range lookups in logarithmic time

Answer + AI explanation with Pro

1. A.Write-heavy index penalty
2. B.Index selectivity
3. C.B-tree index
4. D.Clustered index

Answer + AI explanation with Pro

1. A.B-tree index — the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
2. B.B-tree index — the default balanced-tree structure that keeps keys sorted and supports equality and range lookups in logarithmic time
3. C.B-tree index — an index whose key order defines the physical storage order of the table rows, so there can be only one per table
4. D.B-tree index — a separate structure that stores keys plus pointers to the rows, leaving the table data physically unordered and allowing many per table

Answer + AI explanation with Pro

1. A.an index whose key order defines the physical storage order of the table rows, so there can be only one per table
2. B.the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
3. C.a separate structure that stores keys plus pointers to the rows, leaving the table data physically unordered and allowing many per table
4. D.a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans

Answer + AI explanation with Pro

1. A.Clustered index
2. B.Hash index
3. C.Non-clustered index
4. D.B-tree index

Answer + AI explanation with Pro

1. A.Hash index — an index built over only the subset of rows matching a WHERE condition, keeping it smaller and cheaper for queries that share that filter
2. B.Hash index — the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
3. C.Hash index — the principle that a composite index can be used only for a query that filters on a contiguous prefix of its columns starting from the first one
4. D.Hash index — a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans

Answer + AI explanation with Pro

1. A.an index that includes every column a query needs so the engine answers it from the index alone without touching the table heap
2. B.a separate structure that stores keys plus pointers to the rows, leaving the table data physically unordered and allowing many per table
3. C.an index whose key order defines the physical storage order of the table rows, so there can be only one per table
4. D.an index built over only the subset of rows matching a WHERE condition, keeping it smaller and cheaper for queries that share that filter

Answer + AI explanation with Pro

1. A.Partial index
2. B.Clustered index
3. C.Write-heavy index penalty
4. D.B-tree index

Answer + AI explanation with Pro

1. A.Clustered index — a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans
2. B.Clustered index — an index built over only the subset of rows matching a WHERE condition, keeping it smaller and cheaper for queries that share that filter
3. C.Clustered index — an index whose key order defines the physical storage order of the table rows, so there can be only one per table
4. D.Clustered index — the ratio of distinct values to total rows for a column, where a high value makes the index more effective at filtering

Answer + AI explanation with Pro

1. A.a separate structure that stores keys plus pointers to the rows, leaving the table data physically unordered and allowing many per table
2. B.the principle that a composite index can be used only for a query that filters on a contiguous prefix of its columns starting from the first one
3. C.an index built over only the subset of rows matching a WHERE condition, keeping it smaller and cheaper for queries that share that filter
4. D.the default balanced-tree structure that keeps keys sorted and supports equality and range lookups in logarithmic time

Answer + AI explanation with Pro

1. A.Hash index
2. B.Covering index
3. C.B-tree index
4. D.Non-clustered index

Answer + AI explanation with Pro

1. A.Non-clustered index — a separate structure that stores keys plus pointers to the rows, leaving the table data physically unordered and allowing many per table
2. B.Non-clustered index — the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
3. C.Non-clustered index — the default balanced-tree structure that keeps keys sorted and supports equality and range lookups in logarithmic time
4. D.Non-clustered index — a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans

Answer + AI explanation with Pro

1. A.the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
2. B.an index whose key order defines the physical storage order of the table rows, so there can be only one per table
3. C.an index that includes every column a query needs so the engine answers it from the index alone without touching the table heap
4. D.a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans

Answer + AI explanation with Pro

1. A.Write-heavy index penalty
2. B.Covering index
3. C.Index selectivity
4. D.B-tree index

Answer + AI explanation with Pro

1. A.Covering index — the default balanced-tree structure that keeps keys sorted and supports equality and range lookups in logarithmic time
2. B.Covering index — a separate structure that stores keys plus pointers to the rows, leaving the table data physically unordered and allowing many per table
3. C.Covering index — an index that includes every column a query needs so the engine answers it from the index alone without touching the table heap
4. D.Covering index — a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans

Answer + AI explanation with Pro

1. A.an index built over only the subset of rows matching a WHERE condition, keeping it smaller and cheaper for queries that share that filter
2. B.a separate structure that stores keys plus pointers to the rows, leaving the table data physically unordered and allowing many per table
3. C.the ratio of distinct values to total rows for a column, where a high value makes the index more effective at filtering
4. D.an index that includes every column a query needs so the engine answers it from the index alone without touching the table heap

Answer + AI explanation with Pro

1. A.Leftmost-prefix rule
2. B.Covering index
3. C.B-tree index
4. D.Index selectivity

Answer + AI explanation with Pro

1. A.Index selectivity — the principle that a composite index can be used only for a query that filters on a contiguous prefix of its columns starting from the first one
2. B.Index selectivity — an index that includes every column a query needs so the engine answers it from the index alone without touching the table heap
3. C.Index selectivity — the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
4. D.Index selectivity — the ratio of distinct values to total rows for a column, where a high value makes the index more effective at filtering

Answer + AI explanation with Pro

1. A.an index that includes every column a query needs so the engine answers it from the index alone without touching the table heap
2. B.the principle that a composite index can be used only for a query that filters on a contiguous prefix of its columns starting from the first one
3. C.the default balanced-tree structure that keeps keys sorted and supports equality and range lookups in logarithmic time
4. D.an index whose key order defines the physical storage order of the table rows, so there can be only one per table

Answer + AI explanation with Pro

1. A.Clustered index
2. B.Leftmost-prefix rule
3. C.Non-clustered index
4. D.Partial index

Answer + AI explanation with Pro

1. A.Leftmost-prefix rule — a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans
2. B.Leftmost-prefix rule — the default balanced-tree structure that keeps keys sorted and supports equality and range lookups in logarithmic time
3. C.Leftmost-prefix rule — a separate structure that stores keys plus pointers to the rows, leaving the table data physically unordered and allowing many per table
4. D.Leftmost-prefix rule — the principle that a composite index can be used only for a query that filters on a contiguous prefix of its columns starting from the first one

Answer + AI explanation with Pro

1. A.an index whose key order defines the physical storage order of the table rows, so there can be only one per table
2. B.the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
3. C.a separate structure that stores keys plus pointers to the rows, leaving the table data physically unordered and allowing many per table
4. D.a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans

Answer + AI explanation with Pro

1. A.Non-clustered index
2. B.Hash index
3. C.Partial index
4. D.Write-heavy index penalty

Answer + AI explanation with Pro

1. A.Write-heavy index penalty — the default balanced-tree structure that keeps keys sorted and supports equality and range lookups in logarithmic time
2. B.Write-heavy index penalty — an index whose key order defines the physical storage order of the table rows, so there can be only one per table
3. C.Write-heavy index penalty — the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
4. D.Write-heavy index penalty — a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans

Answer + AI explanation with Pro

1. A.an index that includes every column a query needs so the engine answers it from the index alone without touching the table heap
2. B.an index built over only the subset of rows matching a WHERE condition, keeping it smaller and cheaper for queries that share that filter
3. C.the situation where adding indexes slows inserts, updates, and deletes because every modification must also maintain each index structure
4. D.a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans

Answer + AI explanation with Pro

1. A.Partial index
2. B.Clustered index
3. C.Hash index
4. D.Non-clustered index

Answer + AI explanation with Pro

1. A.Partial index — the default balanced-tree structure that keeps keys sorted and supports equality and range lookups in logarithmic time
2. B.Partial index — an index built over only the subset of rows matching a WHERE condition, keeping it smaller and cheaper for queries that share that filter
3. C.Partial index — a structure that maps keys to buckets via a hash function, giving fast equality lookups but no support for range or ordered scans
4. D.Partial index — the ratio of distinct values to total rows for a column, where a high value makes the index more effective at filtering

Answer + AI explanation with Pro