Microsoft SQL Server Database Quiz Questions

Indexes are used to speed up the retrieval of data by creating a quick lookup method for the database.

In SQL Server, a clustered index can only be created on columns that are not part of text, ntext, or image data types.

A unique index enforces the uniqueness of the values in the indexed column or columns.

Non-clustered indexes do not affect the physical order of rows; they create a separate structure from the data table.

Full-text indexes are specifically designed to optimize searching large text fields within SQL Server databases.

By default, a clustered index is created for primary keys unless specified otherwise, but it's not mandatory for all primary keys.

Filtered indexes improve query performance by indexing only the rows that meet a specific condition, rather than the entire table.

Indexes are less effective for columns with low cardinality as most values are repeated, leading to inefficiencies in query performance.

While indexes speed up data retrieval, they can slow down data modifications (insert, update, delete) because of the overhead required to maintain the indexes.

The INCLUDE clause in a non-clustered index adds non-key columns to the index, which helps improve query performance by allowing more columns to be covered by the index.

While both ensure uniqueness, a primary key constraint also enforces the uniqueness and non-nullability of the key, and it creates a unique clustered index by default unless specified otherwise.

A table can have only one clustered index, as the clustered index defines the physical order of the rows in the table.

Composite indexes are designed to improve performance for queries involving multiple columns by creating an index that includes these columns in a single index structure.

Indexes cannot be created on TEXT, NTEXT, or IMAGE data types in SQL Server. Instead, VARCHAR(MAX) or VARBINARY(MAX) should be used.

Full-text indexes are optimized for searching large text fields using complex queries, such as those involving word proximity, not just exact matches.

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

Non-clustered indexes are suitable for columns with high cardinality as they provide efficient access for queries.

A PRIMARY KEY constraint automatically creates a unique clustered index to enforce the uniqueness of the primary key column.

Full-text indexes are designed to optimize search operations on text-based data by providing advanced search capabilities.

Composite indexes improve performance by including multiple columns in a single index structure, thus optimizing multi-column searches.

The INCLUDE clause allows additional non-key columns to be included in a non-clustered index to improve query performance by covering more columns.

The main purpose of a unique index is to enforce the uniqueness of values in the indexed column or columns.

Non-clustered indexes do not affect the physical order of rows in a table but create a separate structure to reference the rows.

Having too many indexes can degrade performance on data modification operations due to the overhead required to maintain all the indexes.

Clustered indexes are beneficial for queries that filter on a range of values as they define the physical order of the rows.

Filtered indexes are used to quickly locate rows based on a specific condition, improving performance for queries that match the filter criteria.

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

The INCLUDE clause in non-clustered indexes allows you to add additional columns to the index that are not part of the index key.

Composite indexes are used to optimize queries that filter or sort based on multiple columns by creating an index that includes these columns.

Full-text indexes are specifically designed to optimize performance for full-text search operations in SQL Server.

Composite indexes and filtered indexes are ideal for optimizing queries that involve multiple columns and specific conditions.

Creating a unique index on a column is advantageous when you want to enforce the uniqueness of values and optimize query performance for that column.

Full-text indexes and composite indexes are beneficial for performance tuning with large data volumes and complex queries.

Indexes on columns with low cardinality are less effective; instead, consider composite or filtered indexes for better performance.

Non-clustered indexes and composite indexes are typically used to optimize queries on high-cardinality columns.

Filtered indexes are used to optimize queries that target a specific subset of rows, based on a defined condition.

Composite indexes are designed to index multiple columns and improve the performance of complex queries involving those columns.

Including additional columns in a non-clustered index helps improve performance by covering more columns in queries and avoiding extra lookups.

Clustered indexes primarily define the physical order of rows in a table and optimize retrieval based on the indexed columns.

Unique indexes ensure that no two rows have the same values in the specified column(s), enforcing data integrity.

A non-clustered index on a single column can optimize queries that filter or sort based on that column.

Full-text indexes are specifically designed to optimize performance for text searches in SQL Server.

Too many indexes can lead to increased overhead for data modifications and maintenance, which can impact overall performance.

By default, a PRIMARY KEY constraint creates a unique clustered index, unless specified otherwise.

When designing indexes, consider factors such as query patterns, index maintenance overhead, and the balance between read and write performance.