All Questions

Indexes – Types of indexes and their use for query optimization.

MCQ Question 1 of 10

Question 1: Index - Purpose

What is the primary purpose of using an index in a database?

Reduce storage sizeSpeed up query performanceEnsure data integrityBackup data

Question 2: Index - Clustered vs Non-clustered

How does a clustered index differ from a non-clustered index?

Clustered stores data in index order; non-clustered stores separatelyNon-clustered is always fasterClustered allows duplicates, non-clustered doesn’tBoth store data randomly

Question 3: Primary Key and Index

By default, what type of index is created on a Primary Key?

Non-clustered indexClustered indexComposite indexUnique non-clustered index

Question 4: Creating a Unique Index

You need to ensure that the combination of product_id and supplier_id in a product_suppliers table is always unique, while also improving query performance on these columns. Which statement achieves both goals?

CREATE INDEX idx_unique_supply ON product_suppliers (product_id, supplier_id) UNIQUE;CREATE UNIQUE INDEX idx_unique_supply ON product_suppliers (product_id, supplier_id);ADD CONSTRAINT UNIQUE INDEX idx_unique_supply FOR product_suppliers (product_id, supplier_id);ALTER TABLE product_suppliers INDEX UNIQUE (product_id, supplier_id);

Question 5: Composite Index

What does a composite index refer to?

Index on one columnIndex on multiple columnsIndex created automaticallyTemporary index

Question 6: Full-Text Index

Which index type is best for searching large text fields?

Clustered indexNon-clustered indexFull-text indexComposite index

Question 7: Bitmap Index

In which scenario is a bitmap index most effective?

High-cardinality columnsLow-cardinality columns (few distinct values)Random data updatesTemporary tables

Question 8: Index Overhead

What is a disadvantage of too many indexes?

Faster inserts and updatesMore storage and slower DML operationsImproved joinsElimination of redundancy

Question 9: Covering Index

What is a covering index?

An index covering multiple tablesIndex containing all columns required by a queryIndex used for backupClustered index with extra storage

Question 10: Index and Query Optimization

Which type of queries benefit most from indexes?

SELECT with WHERE and JOIN conditionsBulk INSERT statementsUPDATE with no conditionDELETE without filter