MCQs on Advanced Indexing | SQL Server
Here is a collection of 30 multiple-choice questions (MCQs) related to Advanced Indexing in SQL Server. These cover important topics such as Columnstore Indexes, Filtered Indexes, Full-Text Search and Indexing, and Index Maintenance and Performance Analysis. These questions will help you deepen your understanding of SQL Server indexing strategies and their performance optimizations.
Columnstore Indexes
- What is the main advantage of using Columnstore Indexes in SQL Server?
- a) Improved row-level locking
- b) Better performance for OLTP systems
- c) Improved performance for OLAP queries
- d) Reduces disk I/O
- Which type of queries benefit the most from Columnstore Indexes?
- a) OLTP queries with frequent updates
- b) OLAP queries with large datasets
- c) Simple SELECT queries
- d) INSERT and DELETE queries
- How does a Columnstore Index store data?
- a) In a row-based format
- b) In a columnar format
- c) In a hybrid format
- d) In an encrypted format
- Which of the following is true about a Clustered Columnstore Index (CCI)?
- a) It is used only for read-heavy workloads
- b) It supports efficient updates and deletes
- c) It can only be created on tables with no data
- d) It stores data in a columnar format
- What is the storage format of data in a Columnstore Index?
- a) Data is compressed
- b) Data is not compressed
- c) Data is encrypted
- d) Data is split into rows
Filtered Indexes
- Which of the following is a key benefit of a Filtered Index in SQL Server?
- a) Reduced query time for all rows
- b) Reduced storage by indexing only a subset of rows
- c) Increased complexity in query optimization
- d) Increased memory usage
- A Filtered Index can be applied to which of the following SQL clauses?
- a) SELECT
- b) WHERE
- c) JOIN
- d) GROUP BY
- What condition must be true to create a Filtered Index?
- a) The index must cover all columns
- b) The filtered condition must be a constant
- c) The indexed table must have fewer rows than the total table
- d) The filtered condition must reference the primary key
- Which SQL Server version introduced support for Filtered Indexes?
- a) SQL Server 2008
- b) SQL Server 2012
- c) SQL Server 2016
- d) SQL Server 2014
- Which scenario benefits most from using a Filtered Index?
- a) A table with many unused rows
- b) A table with few unique rows
- c) A table with frequently changing data
- d) A table with no rows
Full-Text Search and Indexing
- Full-Text Search in SQL Server allows searching based on which type of data?
- a) Structured data only
- b) Unstructured data only
- c) Both structured and unstructured data
- d) Only binary data
- Which type of index is required to enable Full-Text Search in SQL Server?
- a) Non-clustered index
- b) Columnstore index
- c) Full-text index
- d) Clustered index
- Which of the following is a valid operator in Full-Text Search queries?
- a) BETWEEN
- b) IN
- c) CONTAINS
- d) LIKE
- What is a Full-Text Index used for in SQL Server?
- a) Speeding up queries on numeric data
- b) Searching text-based data efficiently
- c) Enforcing referential integrity
- d) Indexing primary keys
- Full-Text Indexes in SQL Server can be applied to which types of columns?
- a) TEXT and CHAR columns
- b) Only VARCHAR columns
- c) Only NUMERIC columns
- d) Only CHAR and VARCHAR columns
Index Maintenance and Performance Analysis
- What does index fragmentation refer to in SQL Server?
- a) The index being too large for the disk
- b) The logical ordering of index entries being inefficient
- c) The physical corruption of index data
- d) The failure of index rebuilds
- Which command is used to rebuild an index in SQL Server?
- a) REBUILD INDEX
- b) ALTER INDEX REBUILD
- c) CREATE INDEX REBUILD
- d) DROP INDEX
- What is the main effect of high index fragmentation?
- a) Increased performance of queries
- b) Decreased performance due to inefficient data access
- c) Improved index storage utilization
- d) Reduced disk space usage
- How does SQL Server handle index statistics updates?
- a) Automatically on every query execution
- b) Through the UPDATE STATISTICS command
- c) Manually via user intervention only
- d) They are never updated
- Which of the following is a common cause of high index fragmentation?
- a) Frequent INSERT, UPDATE, and DELETE operations
- b) Simple SELECT queries
- c) No query execution
- d) Read-only databases
Advanced Indexing Techniques
- What is the primary purpose of a Composite Index in SQL Server?
- a) To index multiple columns for more efficient searching
- b) To increase the size of a table
- c) To index only the primary key
- d) To improve disk storage
- Which of the following is NOT a common performance analysis tool for indexing in SQL Server?
- a) SQL Profiler
- b) Query Store
- c) Database Engine Tuning Advisor
- d) Full-Text Search Manager
- What is the benefit of using an Indexed View in SQL Server?
- a) It speeds up query performance by storing precomputed results
- b) It reduces the need for normalization
- c) It avoids using joins in complex queries
- d) It prevents index fragmentation
- What is a “Covering Index” in SQL Server?
- a) An index that includes all columns required for a query
- b) An index that covers only unique values
- c) An index that includes only the primary key column
- d) An index that speeds up INSERT operations
- What is the most common way to assess the health of an index in SQL Server?
- a) Using the sp_helpindex stored procedure
- b) Using the sys.dm_db_index_physical_stats function
- c) Using the DBCC TRACEON command
- d) Using SQL Server Profiler
- Which of the following is an example of an inefficient index?
- a) Indexing frequently queried columns
- b) Indexing rarely queried columns
- c) Using a Clustered Index on the primary key
- d) Using a Filtered Index on specific rows
- How does SQL Server automatically manage index statistics?
- a) It refreshes them only when queries are executed
- b) It automatically updates them when the table is modified
- c) It does not update statistics without manual intervention
- d) It resets them every month
- What is the effect of too many indexes on SQL Server performance?
- a) Increased query performance for all queries
- b) Decreased query performance due to overhead in maintaining indexes
- c) No effect on query performance
- d) Improved data integrity
- What happens during an index rebuild operation in SQL Server?
- a) The index is dropped and recreated from scratch
- b) The index data is reorganized in memory
- c) The index is restructured without removing existing data
- d) Only the metadata of the index is updated
- Which SQL Server tool helps identify missing indexes?
- a) SQL Profiler
- b) Database Engine Tuning Advisor
- c) Query Store
- d) Index Designer
Answer Key
| Qno | Answer (Option with Text) |
|---|---|
| 1 | c) Improved performance for OLAP queries |
| 2 | b) OLAP queries with large datasets |
| 3 | b) In a columnar format |
| 4 | d) It stores data in a columnar format |
| 5 | a) Data is compressed |
| 6 | b) Reduced storage by indexing only a subset of rows |
| 7 | b) WHERE |
| 8 | c) The indexed table must have fewer rows than the total table |
| 9 | a) SQL Server 2008 |
| 10 | a) A table with many unused rows |
| 11 | c) Both structured and unstructured data |
| 12 | c) Full-text index |
| 13 | c) CONTAINS |
| 14 | b) Searching text-based data efficiently |
| 15 | d) Only CHAR and VARCHAR columns |
| 16 | b) The logical ordering of index entries being inefficient |
| 17 | b) ALTER INDEX REBUILD |
| 18 | b) Decreased performance due to inefficient data access |
| 19 | b) Through the UPDATE STATISTICS command |
| 20 | a) Frequent INSERT, UPDATE, and DELETE operations |
| 21 | a) To index multiple columns for more efficient searching |
| 22 | d) Full-Text Search Manager |
| 23 | a) It speeds up query performance by storing precomputed results |
| 24 | a) An index that includes all columns required for a query |
| 25 | b) Using the sys.dm_db_index_physical_stats function |
| 26 | b) Indexing rarely queried columns |
| 27 | b) It automatically updates them when the table is modified |
| 28 | b) Decreased query performance due to overhead in maintaining indexes |
| 29 | a) The index is dropped and recreated from scratch |
| 30 | b) Database Engine Tuning Advisor |