SQL Server Indexes and Performance Tuning: Intermediate-Level Quiz

SQL Server Indexes and Performance Tuning: Intermediate Level Quiz

This quiz tests your knowledge of SQL Server indexes and performance tuning. It covers key concepts such as index creation, optimization techniques, and performance monitoring. Good luck!

1 / 20

What is the primary function of a composite index?

To index XML data types

To index multiple columns within a table

To improve query performance on large text columns

To create an index on a computed column

2 / 20

What is the recommended fill factor setting for a table with frequent inserts?

50%

80%

100%

3 / 20

Which of the following is one of the primary differences between a clustered index and a non-clustered index?

Clustered index is slower, non-clustered index is faster

Non-clustered indexes are sorted by the key column, clustered indexes are not.

Clustered index has the inherent ability to store data on the disk, non-clustered cannot.

Clustered indexes allow duplicates, non-clustered do not.

4 / 20

What is the maximum number of non-clustered indexes that can be created on a SQL Server table?

1024

128

999

5 / 20

What is a covering index?

A temporary index used during query execution

An index that can be used for sorting data

An index that satisfies a query without accessing the base table

An index that includes all columns of a table

6 / 20

What happens if a query does not have a suitable index?

The query will fail.

SQL Server will automatically create an index.

The query will use a clustered index by default.

The query will perform a table scan.

7 / 20

Which of the following is the main benefit of a filtered index?

It eliminates the need for a clustered index.

It reduces the size of the database.

It improves performance for queries that access specific subsets of data.

It increases the speed of joins.

8 / 20

What is the effect of using an index on a column with many NULL values?

Increases query performance significantly

Reduces the performance of queries

Has little to no impact on performance

Forces a clustered index scan

9 / 20

Which of the following is not an index type in SQL Server?

Temporary index

XML index

Filtered index

Full-text index

10 / 20

Which query is most likely to benefit from a covering index?

A query that retrieves both key and non-key columns.

A query that performs an aggregate function.

A query with a high number of join operations.

A query that selects only non-key columns.

11 / 20

How does SQL Server determine the selectivity of an index?

The number of joins in a query

The size of the indexed column

The uniqueness of the indexed column

The number of rows in the table

12 / 20

Which SQL Server feature helps in tracking the performance of queries over time?

Query Store

Execution Plan

Index Tuning Wizard

SQL Server Profiler

13 / 20

Which of the following can cause high CPU usage during query execution?

Index rebuilds during peak hours

Overuse of indexes

Table scans due to missing indexes

Well-optimized queries

14 / 20

What kind of index is best for columns with a small range of values, such as status flags?

Bitmap index

Non-clustered index

Clustered index

Filtered index

15 / 20

What index type is created by default when a primary key is defined on a table?

Unique index

Non-clustered index

Full-text index

Clustered index

16 / 20

Which of the following is the primary purpose of an index in SQL Server?

To manage user permissions

To store data

To improve query performance

To create backups

17 / 20

Which of the following is a potential downside of over-indexing a table?

Faster insert and update operations

Improved write performance

Reduced query performance

Increased disk space usage

18 / 20

In which situation would a non-clustered index be more efficient than a clustered index?

When most queries return all columns

When the table has a large number of rows

When data is frequently updated

When specific columns are queried

19 / 20

Which statement about statistics in SQL Server is true?

Statistics are updated automatically after every query.

Outdated statistics can lead to poor query performance.

Statistics do not affect query optimization.

Statistics are created only for clustered indexes.

20 / 20

What is the purpose of the SQL Server Database Engine Tuning Advisor?

To backup the database

To analyze server memory usage

To create indexes automatically

To identify missing indexes and optimize queries

Your score is

The average score is 40%

Do you feel you are past the beginner level in your SQL Server proficiency? This intermediate-level quiz will test you on SQL Server indexes and performance tuning.

Efficient database management starts with mastering SQL Server indexes and performance-tuning techniques. Also, indexes like clustered and non-clustered can drastically improve query speed, while proper maintenance and optimization strategies help prevent slowdowns.

In this quiz, you will answer questions covering essential indexing methods and common pitfalls. The quiz will also test your knowledge of the best practices to enhance your SQL Server’s performance, ensuring faster data retrieval and smoother operations.

All the best.