Database Management & SQL Flashcards

What is the primary purpose of a database index?

Answer: To speed up data retrieval at the cost of additional storage and write overhead

An index creates a data structure that allows the database engine to locate rows faster, trading additional disk space and slower writes for faster reads.

A subquery that is executed once and its result used by the outer query is called a:

Answer: Non-correlated (nested) subquery

A non-correlated subquery runs independently of the outer query and its result is passed in as a static value or set.

Which SQL function assigns a sequential integer to each row within a partition of a result set?

Answer: ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential integer to each row within its partition without gaps, regardless of ties.

In the context of SQL performance tuning, what does a 'full table scan' indicate?

Answer: The database engine read every row because no suitable index was used

A full table scan means the optimizer could not use an index and read every row sequentially, which is slow on large tables.

Which SQL construct allows you to define a temporary named result set that can be referenced multiple times within a single query?

Answer: Common Table Expression (CTE)

A CTE (WITH clause) defines a named temporary result set scoped to the query, improving readability and enabling recursive queries.

What distinguishes a clustered index from a non-clustered index?

Answer: A clustered index determines the physical order of data rows; a non-clustered index does not

A clustered index reorders the physical storage of the table rows to match the index key; only one clustered index can exist per table.

Which SQL set operator returns all rows from two queries combined, eliminating duplicates?

Answer: UNION

UNION combines result sets from two SELECT statements and removes duplicate rows, unlike UNION ALL which keeps all rows.