SQL Server Database Development Flashcards

What is the primary advantage of using table partitioning in SQL Server?

Answer: It improves manageability and query performance by distributing table data across multiple filegroups based on a partition function

Table partitioning divides large tables into smaller, manageable segments based on a partition function, enabling partition elimination and faster data archival.

Which T-SQL command deallocates a cursor and releases all resources associated with it?

Answer: DEALLOCATE cursor_name

DEALLOCATE removes the cursor definition and releases all resources; CLOSE only closes the cursor but keeps the definition available for re-opening.

What is SQL injection, and which approach BEST prevents it in SQL Server stored procedures?

Answer: A security attack prevented by using parameterized queries or sp_executesql with parameters instead of string concatenation

SQL injection occurs when user input is concatenated into dynamic SQL; using parameterized queries or sp_executesql with typed parameters prevents the attack.

In a SQL Server execution plan, what does a HASH MATCH JOIN indicate?

Answer: SQL Server built an in-memory hash table on the smaller input to probe with the larger input because no suitable indexes existed

Hash Match Join builds a hash table from one input (build phase) and probes it with the other input (probe phase), typically used when inputs are large and unsorted.

What is the difference between a DEFAULT constraint and a DEFAULT value set via ALTER TABLE ... ADD CONSTRAINT?

Answer: Inline DEFAULT in CREATE TABLE is unnamed and harder to drop; ALTER TABLE ADD CONSTRAINT creates a named constraint that is easier to manage

Defining a DEFAULT inline during CREATE TABLE results in a system-generated constraint name, making it difficult to reference for later modification or removal.

Which SQL Server feature allows you to automatically capture query execution statistics and plans without manual trace setup, replacing SQL Trace?

Answer: Extended Events

Extended Events is the lightweight, low-overhead successor to SQL Trace and SQL Server Profiler for capturing server events and query statistics.

What does the CROSS APPLY operator return compared to OUTER APPLY when the right table expression returns no rows?

Answer: CROSS APPLY excludes the left row; OUTER APPLY includes the left row with NULLs for the right side

CROSS APPLY works like INNER JOIN — it excludes left rows where the right expression returns nothing; OUTER APPLY works like LEFT JOIN — it preserves left rows with NULLs.