SQL Indexes MCQ Questions & Answers
Indexes MCQs : This section focuses on the "Indexes" in SQL. These Multiple Choice Questions (MCQ) should be practiced to improve the SQL skills required for various interviews (campus interview, walk-in interview, company interview), placements and other competitive examinations.
1. An index helps to speed up?
A. SELECT queries
B. WHERE clauses
C. Both A and B
D. UPDATE Query
View Answer
Ans : C
Explanation: An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements.
2. What is true about index?
A. Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
B. Indexes are special lookup tables that the database search engine can use to speed up data deletion.
C. Indexes can be created or dropped with an effect on the data.
D. An index helps to speed up insert statement.
View Answer
Ans : A
Explanation: Indexes are special lookup tables that the database search engine can use to speed up data retrieval is true.
3. Indexes can also be unique?
A. Yes
B. No
C. Maybe
D. Can't say
View Answer
Ans : A
Explanation: Indexes can also be unique, like the UNIQUE constraint.
4. Which of the following is correct CREATE INDEX Command?
A. INSERT INDEX index_name ON table_name;
B. INSERT INDEX index_name ON database_name;
C. CREATE INDEX index_name ON database_name;
D. CREATE INDEX index_name ON table_name;
View Answer
Ans : D
Explanation: The basic syntax of a CREATE INDEX is as follows : CREATE INDEX index_name ON table_name;
5. A ______ index is created based on only one table column.
A. Implicit
B. Unique
C. single-column
D. Composite
View Answer
Ans : C
Explanation: A single-column index is created based on only one table column.
6. A ______ index does not allow any duplicate values to be inserted into the table.
A. Implicit
B. Unique
C. single-column
D. Composite
View Answer
Ans : B
Explanation: A unique index does not allow any duplicate values to be inserted into the table.
7. A _______ index is an index on two or more columns of a table.
A. Implicit
B. Unique
C. single-column
D. Composite
View Answer
Ans : D
Explanation: A composite index is an index on two or more columns of a table.
8. Which of the following is correct DROP INDEX Command?
A. DROP INDEX table_name;
B. DROP INDEX index_name;
C. DROP INDEX index_name or table_name;
D. DELETE INDEX index_name;
View Answer
Ans : B
Explanation: The basic syntax is as follows : DROP INDEX index_name;
9. Which of the following is condition where indexes be avoided?
A. Indexes should not be used on small tables
B. Tables that have frequent, large batch updates or insert operations
C. Columns that are frequently manipulated should not be indexed.
D. All of the above
View Answer
Ans : D
Explanation: All the statement are condition where indexes be avoided.
10. Can we use index on columns that contain a high number of NULL values?
A. YES
B. NO
C. Only Implicit Indexes can be used
D. Only Composite Indexes can be used.
View Answer
Ans : B
Explanation: Indexes should not be used on columns that contain a high number of NULL values.
11. What is the syntax for Single-Column Indexes?
A. CREATE INDEX index_name ON table_name (column_name);
B. CREATE SINGLE-COLUMN INDEX index_name ON table_name (column_name);
C. CREATE INDEX SINGLE-COLUMN index_name ON table_name (column_name);
D. ALTER SINGLE-COLUMN INDEX index_name ON table_name (column_name);
View Answer
Ans : A
Explanation: The basic syntax is as follows : CREATE INDEX index_name ON table_name (column_name);
12. What is the syntax for UNIQUE Indexes?
A. CREATE INDEX index_name ON table_name (column_name);
B. CREATE UNIQUE INDEX index_name on table_name (column_name);
C. CREATE INDEX UNIQUE index_name on table_name (column_name);
D. CREATE INDEX index_name on UNIQUE table_name (column_name);
View Answer
Ans : B
Explanation: The basic syntax is as follows : CREATE UNIQUE INDEX index_name
on table_name (column_name);
13. Which of the following index are automatically created by the database server when an object is created?
A. Implicit
B. Unique
C. single-column
D. Composite
View Answer
Ans : A
Explanation: Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.
14. How many types of indexes are there in sql server?
A. 1
B. 2
C. 3
D. 4
View Answer
Ans : B
Explanation: They are clustered index and non clustered index.
15. A _________ query is a query where all the columns in the query’s result set are pulled from non-clustered indexes.
A. B-Tree
B. Non Clustered
C. Covered
D. Clustered
View Answer
Ans : C
Explanation: A covered query is a query where all the columns in the query’s result set are pulled from non-clustered indexes.
16. If an index is _________________ the metadata and statistics continue to exists.
A. Dropping
B. Inserting
C. Altering
D. Disabling
View Answer
Ans : D
Explanation: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes.
17. _______________ have a structure separate from the data rows?
A. B-Tree
B. Non Clustered
C. Covered
D. Clustered
View Answer
Ans : B
Explanation: Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
18. Does index take space in the disk?
A. Yes
B. No
C. It stores memory as and when required
D. Indexes take no space
View Answer
Ans : A
Explanation: Indexes take memory slots which are located on the disk.
19. How non clustered index point to the data?
A. It never points to anything
B. It points to a data row
C. It is used for pointing data rows containing key values
D. All of the above
View Answer
Ans : C
Explanation: Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
20. Indexes used to improve the performance.
A. YES
B. NO
C. Maybe
D. Can't say
View Answer
Ans : A
Explanation: Indexes tend to improve the performance.
Discussion