MS SQL Server - 41 - Indices with Conditions and Joins MCQ

41 – Indices with Conditions and Joins MCQ

Best of luck!

1 / 25

1. What is the primary purpose of using an index in the WHERE clause of a SQL query?

To define table aliases

To enforce data constraints

To specify the join condition

To improve query performance by quickly locating matching rows

2 / 25

2. Which type of index is designed to cover all the columns required by a query, eliminating the need to access the base table?

Nonclustered index

Clustered index

Covering index

Special index

3 / 25

3. In SQL Server, which keyword is commonly used to specify join conditions in a query?

CONNECT BY

USING

MATCHES

4 / 25

4. What is the purpose of an index in the join operation of SQL queries?

To specify the result set columns

To enforce primary key constraints

To optimize data retrieval by reducing the number of comparisons

To define table aliases

5 / 25

5. In SQL Server, what is a “covering index”?

An index that is not associated with any table

An index that covers only a single column

An index used for clustering data

An index that covers all the columns in the base table

6 / 25

6. Which of the following is an advantage of using a covering index?

It speeds up data modification operations.

It reduces storage space requirements.

It enforces primary key constraints.

It eliminates the need for any other index on the table.

7 / 25

7. What is the primary purpose of missing index recommendations in SQL Server?

To suggest indices that could improve query performance

To identify indices that are no longer needed

To enforce primary key constraints

To list all available indices in the database

8 / 25

8. In SQL Server, where can you find missing index recommendations?

In the system catalog

In the query execution plan

In the transaction log

In the SQL Server Agent logs

9 / 25

9. Which type of index is specifically designed to improve the performance of full-text search queries?

Clustered index

Full-Text index

Nonclustered index

Bitmap index

10 / 25

10. In SQL Server, what is the primary purpose of a spatial index?

To enforce primary key constraints

To optimize data retrieval for spatial data types

To store binary data efficiently

To index large text columns

11 / 25

11. What is the primary advantage of using a filtered index in SQL Server?

It covers all columns in the base table.

It indexes only a subset of rows in a table based on a filter condition.

It speeds up data modification operations.

It enforces data constraints.

12 / 25

12. In SQL Server, what is the primary purpose of a XML index?

To index XML columns for efficient querying

To enforce primary key constraints on XML data

To store XML data in a separate table

To create a clustered index on XML data

13 / 25

13. Which type of index is specifically used for efficiently querying columns with binary large object (BLOdata types?

Nonclustered index

BLOB index

Clustered index

Bitmap index

14 / 25

14. What is the primary advantage of using a columnstore index in SQL Server?

It enforces data constraints.

It speeds up data modification operations.

It covers all columns in the base table.

It optimizes data retrieval for analytical queries on large datasets.

15 / 25

15. Which SQL statement is used to create a filtered index in SQL Server?

CREATE FILTERED INDEX IX_IndexName ON TableName (ColumnName) WHERE FilterCondition

CREATE FILTERED INDEX IX_IndexName ON TableName (ColumnName)

CREATE INDEX IX_IndexName ON TableName (ColumnName) WHERE FilterCondition

CREATE INDEX IX_IndexName ON TableName (ColumnName) WITH (FILTER = FilterCondition)

16 / 25

16. What is the primary purpose of a spatial index in SQL Server?

To index columns with binary data

To enforce primary key constraints on spatial data

To optimize data retrieval for full-text search queries

To index columns with spatial data types

17 / 25

17. In SQL Server, what is the primary purpose of a full-text index?

To index large text columns

To optimize data retrieval for spatial data types

To enforce primary key constraints on full-text data

To index columns with binary data

18 / 25

18. Which type of index is specifically used for columns with geospatial data types in SQL Server?

Nonclustered index

Full-Text index

Spatial index

Clustered index

19 / 25

19. In SQL Server, what is the primary purpose of a filtered index?

To index a subset of rows based on a filter condition

To cover all columns in the base table

To index only numeric columns

To enforce primary key constraints

20 / 25

20. What is the primary advantage of using a columnstore index in SQL Server?

It optimizes data retrieval for analytical queries on large datasets.

It enforces primary key constraints.

It eliminates the need for any other index on the table.

It speeds up data modification operations.

21 / 25

21. Which SQL statement is used to create a columnstore index in SQL Server?

CREATE CLUSTERED INDEX IX_IndexName ON TableName (ColumnName)

CREATE INDEX IX_IndexName ON TableName (ColumnName)

CREATE UNIQUE INDEX IX_IndexName ON TableName (ColumnName)

CREATE COLUMNSTORE INDEX IX_IndexName ON TableName (ColumnName)

22 / 25

22. In SQL Server, what is the primary purpose of a bitmap index?

To index columns with binary data

To index columns with high cardinality (many distinct values)

To optimize data retrieval for spatial data types

To enforce primary key constraints

23 / 25

23. Which type of index is specifically used for efficiently querying columns with a low number of distinct values in SQL Server?

Clustered index

Full-Text index

Nonclustered index

Bitmap index

24 / 25

24. What is the primary advantage of using a bitmap index in SQL Server?

It reduces storage space requirements.

It covers all columns in the base table.

It enforces primary key constraints.

It optimizes data retrieval for analytical queries.

25 / 25

25. In SQL Server, what is the primary purpose of a full-text index?

To optimize data retrieval for geospatial data types

To index columns with binary data

To index large text columns for efficient querying

To enforce primary key constraints on full-text data

Your score is

MS SQL Server – 41 – Indices with Conditions and Joins MCQ