Microsoft SQL Server Database Quiz Questions

A clustered index defines the physical order of rows in a table based on the index key.

A table can have only one clustered index because it defines the physical order of data.

Unique indexes ensure that no two rows have the same values in the indexed columns.

Non-clustered indexes do not affect the physical order of rows but create a separate structure for indexing.

Full-text indexes are designed to enhance the performance of text-based searches in SQL Server.

Composite indexes include multiple columns, improving the performance of queries that filter or sort based on those columns.

A unique index enforces uniqueness, so it cannot be created on a column with duplicate values.

Filtered indexes help to improve performance by indexing only a specific subset of data based on a filter condition.

Index fragmentation can degrade query performance because the data is not stored in contiguous pages.

SQL Server does not automatically handle index maintenance tasks; they need to be managed through scheduled jobs or scripts.

Indexes improve read operations but can slow down insert, update, and delete operations due to the overhead of maintaining the index.

While indexes can improve query performance, creating too many indexes or poorly designed indexes can lead to performance issues.

Covering indexes include all columns required for a query, so SQL Server can retrieve the data directly from the index without accessing the table.

Index seeks are more efficient because they search for specific values, while scans read through all rows in the index.

SQL Server does not automatically choose index types. Database administrators and developers must design and implement indexes based on query requirements.

A clustered index determines the physical order of data in the table. A table can only have one clustered index.

Full-text indexes are used to optimize performance for text-based search queries.

Unique indexes ensure that all values in the indexed column(s) are unique across the table.

Covering indexes include all the columns required for a query, allowing SQL Server to fulfill the query using only the index.

XML indexes are used to optimize performance for queries involving XML data.

A primary key index enforces the uniqueness of a column or a set of columns and determines the physical order of rows if it is clustered.

Filtered indexes include only rows that meet a specified filter condition, improving performance for specific queries.

Spatial indexes are designed to optimize queries involving spatial data types.

Non-clustered indexes do not affect the physical order of data but provide a separate structure for fast data retrieval.

Full-text indexes are designed to speed up text search queries within string columns.

Composite indexes include multiple columns, optimizing queries that involve those columns in search or sorting operations.

Filtered indexes improve performance by including only a subset of rows that meet a specific condition.

Spatial indexes are used to optimize queries on spatial data types.

Filtered indexes can be created on any data type as long as the filter condition is valid for that type.

XML indexes are designed specifically to enhance performance for querying XML data.

Composite indexes can include multiple columns, which helps optimize queries that use those columns in search or sorting operations.

Filtered indexes are used to optimize queries by including only a subset of rows that match a filter condition.

Spatial indexes are designed to enhance performance for queries involving spatial data.

Covering indexes include all columns required for a query, improving performance by avoiding additional lookups.

Unique indexes enforce uniqueness, preventing duplicate values in the indexed columns.

Full-text indexes enhance performance for text search queries, including those with LIKE conditions.

Unique indexes ensure that all values in the indexed columns are unique, so they cannot be created on columns with duplicate values.

Filtered indexes improve performance by including only rows that meet a specific condition.

Composite indexes are used to optimize queries involving multiple columns in search or sorting operations.

Full-text indexes are designed for optimizing performance in large text searches.

Maintaining indexes through reorganizing or rebuilding can reduce fragmentation and improve query performance.

Spatial indexes are specifically designed for optimizing queries involving spatial data types.

Indexes like full-text indexes are used for performance optimization and not for enforcing data integrity constraints.

Index fragmentation can lead to slower query performance due to inefficient data storage and retrieval.

Index fragmentation can be managed by rebuilding or reorganizing indexes to maintain performance.