MS SQL Server - 39 - Clustered and Nonclustered Indices (Index) MCQ

39 – Clustered and Nonclustered Indices (Index) MCQ

Best of luck!

1 / 25

1. What is a clustered index in SQL Server?

An index that determines the order of data rows in a table

An index that stores data in a separate clustered table

An index that cannot be created in SQL Server

An index used only for primary key columns

2 / 25

2. Which of the following statements about clustered indices is true?

A clustered index determines the physical order of data rows in a table.

A clustered index is not stored separately from the data.

A clustered index is always created automatically when a table is created.

A table can have multiple clustered indices.

3 / 25

3. In SQL Server, what is the default index type for a primary key constraint?

Bitmap index

Full-text index

Nonclustered index

Clustered index

4 / 25

4. Which of the following SQL statements creates a clustered index on an existing table?

CREATE UNIQUE INDEX IX_IndexName ON TableName (ColumnName)

CREATE INDEX IX_IndexName ON TableName (ColumnName) WITH (FILLFACTOR = 90)

CREATE NONCLUSTERED INDEX IX_IndexName ON TableName (ColumnName)

CREATE CLUSTERED INDEX IX_IndexName ON TableName (ColumnName)

5 / 25

5. What happens when a clustered index is created on a table that already has data?

The existing table data is physically reordered based on the clustered index.

The table data is deleted.

The table data is copied to a new location.

The table becomes read-only.

6 / 25

6. Which of the following is a primary advantage of using a clustered index in SQL Server?

Ability to have duplicate values

Reduced storage space

Faster write operations

Faster read operations

7 / 25

7. What is a nonclustered index in SQL Server?

An index used only for primary key columns

An index that is stored separately from the data

An index that determines the physical order of data rows in a table

An index that cannot be created in SQL Server

8 / 25

8. Which of the following statements about nonclustered indices is true?

A table can have only one nonclustered index.

Nonclustered indices determine the physical order of data rows.

Nonclustered indices are automatically created for primary key columns.

Nonclustered indices store data separately from the data rows.

9 / 25

9. In SQL Server, can a table have both a clustered index and one or more nonclustered indices?

No, a table can have neither type of index.

Yes, but it requires special configuration.

Yes, a table can have both types of indices.

No, a table can have only one type of index.

10 / 25

10. Which of the following SQL statements creates a nonclustered index on an existing table?

CREATE NONCLUSTERED INDEX IX_IndexName ON TableName (ColumnName)

CREATE INDEX IX_IndexName ON TableName (ColumnName) WITH (FILLFACTOR = 90)

CREATE CLUSTERED INDEX IX_IndexName ON TableName (ColumnName)

CREATE UNIQUE INDEX IX_IndexName ON TableName (ColumnName)

11 / 25

11. What is the primary advantage of using nonclustered indices in SQL Server?

Ability to have duplicate values

Faster write operations

Faster read operations

Reduced storage space

12 / 25

12. In SQL Server, can a table have multiple nonclustered indices?

No, nonclustered indices are not supported.

No, a table can have only one nonclustered index.

Yes, but each index must be on a different column.

Yes, a table can have multiple nonclustered indices.

13 / 25

13. Which type of index is often used to enforce the uniqueness of values in a column?

Clustered index

Bitmap index

Unique index

Nonclustered index

14 / 25

14. What is a key lookup operation (also known as a bookmark lookup) in SQL Server?

An operation that reorders the data rows physically

A type of clustered index

A type of nonclustered index

An operation that retrieves additional columns from the base table using the index key

15 / 25

15. In SQL Server, which of the following is a scenario where a nonclustered index is preferred over a clustered index?

When you need to enforce primary key constraints

When you want to reduce storage space

When you want to sort data physically

When you need to retrieve a small subset of columns quickly

16 / 25

16. What is the primary disadvantage of using clustered indices in SQL Server?

Inability to store data separately from the index

Slower write operations

Inability to enforce primary key constraints

Slower read operations

17 / 25

17. In SQL Server, which type of index is typically smaller in size, making it more space-efficient?

Unique index

Nonclustered index

Bitmap index

Clustered index

18 / 25

18. What is the purpose of the INCLUDE clause when creating a nonclustered index in SQL Server?

To define the clustered index

To remove duplicates from the index

To specify additional columns to be stored in the index leaf nodes

To specify the index key columns

19 / 25

19. Which of the following SQL statements is used to drop a clustered index in SQL Server?

DROP UNIQUE INDEX IX_IndexName ON TableName

DROP INDEX IX_IndexName ON TableName

DROP CLUSTERED INDEX IX_IndexName ON TableName

DROP NONCLUSTERED INDEX IX_IndexName ON TableName

20 / 25

20. In SQL Server, which type of index is typically used for columns with low cardinality (few distinct values)?

Unique index

Nonclustered index

Bitmap index

Clustered index

21 / 25

21. What is the purpose of the FILLFACTOR option when creating an index in SQL Server?

To specify the index key columns

To define the clustered index

To specify the percentage of space to be filled on each index page

To remove duplicates from the index

22 / 25

22. Which of the following is a primary disadvantage of using nonclustered indices in SQL Server?

Slower read operations

Inability to store data separately from the index

Inability to enforce primary key constraints

Slower write operations

23 / 25

23. In SQL Server, can a table have both a clustered and a nonclustered index on the same column?

Yes, but only for primary key columns.

Yes, it is allowed.

No, only one type of index can be created per column.

No, it is not allowed.

24 / 25

24. What is the primary purpose of the WITH (PAD_INDEX = ON) option when creating an index in SQL Server?

To define the clustered index

To specify the index key columns

To pad index pages with extra space for future inserts

To remove duplicates from the index

25 / 25

25. In SQL Server, which type of index is typically used for columns with high cardinality (many distinct values)?

Bitmap index

Unique index

Clustered index

Nonclustered index

Your score is

MS SQL Server – 39 – Clustered and Nonclustered Indices (Index) MCQ