MCQs on Basic Indexing | SQL Server

Explore the fundamentals of basic indexing in SQL Server with these 30 MCQs. Covering clustered vs. non-clustered indexes, benefits, creation, and deletion, these questions optimize your learning and performance.

---

Topics Covered:

1. Introduction to Indexes
2. Clustered vs. Non-Clustered Indexes
3. Benefits of Indexing
4. Creating and Dropping Indexes

---

MCQs on Basic Indexing | SQL Server

Introduction to Indexes

1. Which SQL statement creates an index on the “Employee” table for the “LastName” column?
   • A) CREATE INDEX idx_lastname ON Employee (LastName);
   • B) CREATE INDEX Employee_idx ON Employee (LastName);
   • C) CREATE INDEX ON Employee (LastName);
   • D) CREATE INDEX idx_lastname (LastName) ON Employee;
2. What is the primary purpose of an index in SQL Server?
   • A) To maintain data integrity.
   • B) To enforce primary key constraints.
   • C) To speed up data retrieval.
   • D) To improve transaction concurrency.
3. Which type of index is created automatically when a primary key is defined on a table?
   • A) Non-clustered index
   • B) Clustered index
   • C) Full-text index
   • D) Unique index
4. In SQL Server, which catalog view lists all indexes in a database?
   • A) sys.tables
   • B) sys.indexes
   • C) sys.columns
   • D) sys.objects
5. What is an index made up of in SQL Server?
   • A) Key columns and data rows
   • B) Tables and constraints
   • C) Pages and extents
   • D) Data files and log files

---

Clustered vs. Non-Clustered Indexes

6. Which of the following is true about clustered indexes?
   • A) They create a separate copy of data.
   • B) They define the physical order of data in the table.
   • C) They can only be applied to non-primary key columns.
   • D) They are always faster than non-clustered indexes.
7. How many clustered indexes can a table have in SQL Server?
   • A) Unlimited
   • B) One
   • C) Two
   • D) Based on the table size
8. A non-clustered index contains:
   • A) All columns of the table.
   • B) Only the index key and a pointer to the table rows.
   • C) A physical copy of table data.
   • D) Only unique data values.
9. What happens if you create a clustered index on a column with duplicate values?
   • A) The index creation fails.
   • B) SQL Server adds a uniqueifier to duplicate rows.
   • C) Only the first instance is indexed.
   • D) Duplicate rows are excluded.
10. What is the primary difference between clustered and non-clustered indexes?
    • A) Non-clustered indexes cannot include unique constraints.
    • B) Clustered indexes define data order, while non-clustered indexes do not.
    • C) Non-clustered indexes take up more disk space.
    • D) Clustered indexes are slower than non-clustered ones.

---

Benefits of Indexing

11. Which operation benefits most from indexing in SQL Server?
    • A) INSERT operations
    • B) SELECT queries
    • C) DELETE operations
    • D) Table creation
12. How does indexing improve query performance?
    • A) By storing all data in memory.
    • B) By reducing the number of disk I/O operations.
    • C) By parallelizing query execution.
    • D) By enabling row-level locking.
13. An indexed column is most beneficial for queries involving:
    • A) Aggregate functions
    • B) Equality or range conditions
    • C) Data insertion
    • D) String concatenation
14. Why should an index not be created on every column in a table?
    • A) It increases storage requirements and slows down data modification operations.
    • B) SQL Server does not support indexing all columns.
    • C) Queries on indexed columns are always slower.
    • D) It makes the table schema more complex.
15. What is a potential drawback of indexing?
    • A) Decreases query performance.
    • B) Slows down data modification operations like INSERT, UPDATE, and DELETE.
    • C) Consumes additional CPU resources during SELECT operations.
    • D) Prevents the use of stored procedures.

---

Creating and Dropping Indexes

16. What is the correct syntax to drop an index in SQL Server?
    • A) DELETE INDEX idx_name ON table_name;
    • B) DROP INDEX table_name.idx_name;
    • C) ALTER TABLE table_name DROP INDEX idx_name;
    • D) REMOVE INDEX idx_name;
17. How can you create a unique index in SQL Server?
    • A) CREATE UNIQUE INDEX idx_name ON table_name (column_name);
    • B) CREATE INDEX UNIQUE idx_name ON table_name (column_name);
    • C) CREATE UNIQUE INDEX table_name.idx_name;
    • D) ALTER TABLE ADD UNIQUE INDEX (column_name);
18. Can an index be created on a computed column?
    • A) Yes, always.
    • B) No, it is not allowed.
    • C) Yes, if the column is deterministic and precise.
    • D) Only on indexed views.
19. Which tool can be used to create indexes in SQL Server?
    • A) SQL Server Management Studio (SSMS)
    • B) SQL Server Profiler
    • C) Database Engine Tuning Advisor
    • D) Both A and C
20. What keyword is used to specify the inclusion of non-key columns in a non-clustered index?
    • A) INCLUDE
    • B) INCLUDE_COLUMNS
    • C) INCLUDE_FIELDS
    • D) INCLUDE_KEYS
21. Which of the following can lead to index fragmentation?
    • A) Frequently updated indexed columns.
    • B) Large DELETE operations.
    • C) Adding new rows to a table.
    • D) All of the above.
22. What command can be used to rebuild a fragmented index?
    • A) ALTER INDEX idx_name REORGANIZE;
    • B) ALTER INDEX idx_name REBUILD;
    • C) UPDATE INDEX idx_name;
    • D) RESET INDEX idx_name;
23. What is a covering index?
    • A) An index that includes all columns used in a query.
    • B) A clustered index that covers all rows.
    • C) An index with unique values only.
    • D) A non-clustered index with key and non-key columns.
24. When dropping an index, which of the following is true?
    • A) Data in the table is deleted.
    • B) Queries that depended on the index may run slower.
    • C) All primary keys are removed.
    • D) The table is locked permanently.
25. How can index usage statistics be monitored in SQL Server?
    • A) By querying sys.dm_db_index_usage_stats.
    • B) Using the DBCC CHECKINDEX command.
    • C) By running sp_helpindex.
    • D) All of the above.

---

Answer Key

Qno	Answer
1	A) CREATE INDEX idx_lastname ON Employee (LastName);
2	C) To speed up data retrieval
3	B) Clustered index
4	B) sys.indexes
5	A) Key columns and data rows
6	B) They define the physical order of data in the table
7	B) One
8	B) Only the index key and a pointer to the table rows
9	B) SQL Server adds a uniqueifier to duplicate rows
10	B) Clustered indexes define data order, while non-clustered indexes do not
11	B) SELECT queries
12	B) By reducing the number of disk I/O operations
13	B) Equality or range conditions
14	A) It increases storage requirements and slows down data modification operations
15	B) Slows down data modification operations like INSERT, UPDATE, and DELETE
16	B) DROP INDEX table_name.idx_name;
17	A) CREATE UNIQUE INDEX idx_name ON table_name (column_name);
18	C) Yes, if the column is deterministic and precise
19	D) Both A and C
20	A) INCLUDE
21	D) All of the above
22	B) ALTER INDEX idx_name REBUILD;
23	D) A non-clustered index with key and non-key columns
24	B) Queries that depended on the index may run slower
25	A) By querying sys.dm_db_index_usage_stats