Database Management & SQL Server Flashcards

What SQL Server feature enables you to enforce rules that span multiple tables, such as ensuring referential integrity across databases?

Answer: INSTEAD OF triggers

INSTEAD OF triggers intercept DML and allow custom logic, including cross-database integrity checks that CHECK constraints and FOREIGN KEY constraints cannot enforce.

Which recovery model in SQL Server allows bulk operations like BCP and SELECT INTO to be minimally logged?

Answer: BULK_LOGGED

BULK_LOGGED recovery minimally logs bulk operations (BCP, BULK INSERT, SELECT INTO), reducing log space usage while still allowing log backups.

What is the purpose of the SQL Server Resource Governor?

Answer: Classifies and limits CPU and memory resources for workload groups

Resource Governor classifies incoming sessions into workload groups and pools, capping CPU and memory usage to prevent one workload from starving others.

A table has no clustered index. What is this type of table called in SQL Server?

Answer: Heap

A heap is a table stored without a clustered index; rows are stored in no particular order and are located using an Index Allocation Map (IAM).

Which SQL Server feature allows queries against external data sources like Hadoop and Azure Blob Storage without importing the data?

Answer: PolyBase

PolyBase provides T-SQL access to external data in Hadoop, Azure Blob Storage, and other sources by creating external tables that abstract the data source.

What is the default port for SQL Server Database Engine instances?

Answer: 1433

SQL Server Database Engine listens on TCP port 1433 by default for the default instance; named instances use dynamic ports by default.

Which SQL Server windowing function returns the rank of each row within a partition, with no gaps in ranking values?

Answer: DENSE_RANK()

DENSE_RANK() assigns consecutive rank values with no gaps when there are ties, whereas RANK() skips rank values after ties.