MCTS Database Management & SQL Server 4

Question 1

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

Accepted Answer

INSTEAD OF triggers

Answer

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

Question 2

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

Accepted Answer

BULK_LOGGED

Answer

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

Question 3

What is the purpose of the SQL Server Resource Governor?

Accepted Answer

Classifies and limits CPU and memory resources for workload groups

Answer

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

Question 4

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

Accepted Answer

Heap

Answer

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).

Question 5

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

Accepted Answer

PolyBase

Answer

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.

(MCTS) Microsoft Certified Technology Specialist Practice Test

(MCTS) Microsoft Certified Technology Specialist Practice Test

MCTS Database Management & SQL Server 4