View Full Microsoft DP-800 Exam Dumps and Practice Test Dumps.

 

Question 241

Which SQL Server object is used to group multiple operations into a single unit of work that can be committed or rolled back?

1. View
2. Transaction
3. Index
4. Schema

Correct Answer: 2

Explanation

A transaction groups one or more database operations into a logical unit of work. The operations can be committed when everything succeeds or rolled back if an error occurs. Transactions help maintain data consistency when multiple related changes must succeed together. In T-SQL, administrators commonly use BEGIN TRANSACTION, COMMIT, and ROLLBACK to control transactions. Proper transaction design is especially important when applications modify multiple related tables.

Question 242

Which transaction isolation level provides the highest level of protection against dirty reads, non-repeatable reads, and phantom reads?

1. READ UNCOMMITTED
2. READ COMMITTED
3. SERIALIZABLE
4. SNAPSHOT

Correct Answer: 3

Explanation

The SERIALIZABLE isolation level provides the strictest standard transaction isolation. It prevents dirty reads, non-repeatable reads, and phantom reads by using locking behavior that can restrict other transactions from modifying or inserting qualifying rows during the transaction. This strong protection can reduce concurrency and may increase blocking. Administrators should use SERIALIZABLE only when the application’s consistency requirements justify its additional locking and concurrency impact.

Question 243

Which isolation level allows a transaction to read data that another transaction has not yet committed?

1. SNAPSHOT
2. SERIALIZABLE
3. READ COMMITTED
4. READ UNCOMMITTED

Correct Answer: 4

Explanation

READ UNCOMMITTED is the least restrictive standard transaction isolation level. It allows a transaction to read data that another transaction has modified but not yet committed. Such reads are called dirty reads. This isolation level can reduce locking and improve concurrency, but the returned data may be inconsistent or later rolled back. It should therefore be used carefully and only when the application can tolerate potentially uncommitted data.

Question 244

Which isolation level uses row versions to provide transactionally consistent reads without normally blocking writers?

1. SNAPSHOT
2. SERIALIZABLE
3. READ UNCOMMITTED
4. REPEATABLE READ

Correct Answer: 1

Explanation

The SNAPSHOT isolation level uses row versions to provide a consistent view of data as it existed when the transaction began. Readers generally do not block writers, which can improve concurrency compared with lock-based approaches. Row versions are maintained in the version store, which has storage and workload implications. Administrators should understand the application’s consistency requirements and monitor version-store usage when enabling row-versioning-based isolation.

Question 245

Which system database is primarily used for temporary objects, intermediate query results, and row-versioning information?

1. master
2. msdb
3. tempdb
4. model

Correct Answer: 3

Explanation

tempdb is a system database used for temporary tables, table variables, worktables, sorting, hashing, and other temporary operations. It can also store row versions for supported row-versioning features. Because many database operations depend on tempdb, insufficient storage or heavy tempdb contention can affect overall SQL Server performance. Administrators should monitor tempdb usage and configure its files appropriately for the workload.

Question 246

Which system database stores information used by SQL Server Agent jobs and schedules?

1. msdb
2. master
3. tempdb
4. model

Correct Answer: 1

Explanation

The msdb system database stores information used by SQL Server Agent, including jobs, schedules, operators, and job history. It also contains information related to tasks such as backup and restore history. Because SQL Server Agent depends on msdb, administrators should include msdb in their backup strategy when using SQL Server environments that require recovery of jobs and related configuration. Azure SQL Database does not provide SQL Server Agent in the same way.

Question 247

Which system database contains information about SQL Server instance configuration and databases?

1. tempdb
2. master
3. msdb
4. model

Correct Answer: 2

Explanation

The master database contains critical system-level information about the SQL Server instance. It stores information about databases, logins, endpoints, and other instance configuration details. If the master database is unavailable or damaged, the SQL Server instance can have serious startup and operational problems. Administrators should therefore protect the master database through appropriate backup and recovery procedures, especially on SQL Server installations where they manage the underlying instance.

Question 248

Which system database provides the template used when new databases are created?

1. model
2. master
3. msdb
4. tempdb

Correct Answer: 1

Explanation

The model database acts as a template for newly created databases. Settings and objects configured in model can influence the initial configuration of new databases. Administrators can use this behavior when they need consistent defaults across newly created databases. However, changes to model should be made carefully because they can affect future database creation. Existing databases are not automatically changed when the model database is modified.

Question 249

Which database object automatically executes when a specified data modification or database event occurs?

1. View
2. Trigger
3. Index
4. Function

Correct Answer: 2

Explanation

A trigger is a database object that automatically executes in response to specific events. DML triggers can respond to operations such as INSERT, UPDATE, or DELETE, while other trigger types can respond to supported database or server events. Triggers can be useful for enforcing rules or recording changes, but excessive trigger logic can make applications harder to troubleshoot and may affect performance. Administrators should keep trigger behavior simple and well documented.

Question 250

Which database object provides a stored query that can be used like a virtual table?

1. View
2. Trigger
3. Index
4. Constraint

Correct Answer: 1

Explanation

A view is a virtual table based on a SELECT query. It can present data from one or more underlying tables while hiding query complexity from users or applications. Views can also help provide a controlled interface to data by exposing only selected columns or rows. The underlying data is normally stored in the base tables rather than in the view itself. Administrators should consider indexing and query complexity when designing frequently used views.

Question 251

Which database object can contain multiple T-SQL statements and accept parameters for reusable database operations?

1. Stored procedure
2. Index
3. Constraint
4. Schema

Correct Answer: 1

Explanation

A stored procedure is a reusable database program that can contain multiple T-SQL statements and accept input or output parameters. Stored procedures are commonly used to centralize database operations, simplify application code, and control access to underlying data. Permissions can be granted to execute a procedure without necessarily granting direct access to every underlying object in suitable ownership-chain scenarios. Administrators should monitor procedure performance and maintain clear permission boundaries.

Question 252

Which type of function returns a table as its result?

1. Scalar function
2. Table-valued function
3. Trigger
4. Stored procedure

Correct Answer: 2

Explanation

A table-valued function returns a table result that can be queried similarly to a table expression. It can accept parameters and encapsulate reusable logic for returning rows. Table-valued functions can be useful when applications need reusable query logic that integrates into SELECT statements. Administrators should understand the performance characteristics of the specific function type and query design because poorly designed functions can introduce unnecessary overhead.

Question 253

Which database object is primarily used to improve the speed of data retrieval operations?

1. Trigger
2. Index
3. View
4. Constraint

Correct Answer: 2

Explanation

An index provides an additional data structure that can help the database engine locate rows more efficiently. Proper indexing can significantly improve SELECT, JOIN, filtering, and ordering operations. However, indexes also require storage and must be maintained when data changes. Too many indexes can increase INSERT, UPDATE, and DELETE costs. Administrators should use execution plans, workload information, and index usage statistics when deciding which indexes are beneficial.

Question 254

Which type of index determines the physical order of rows in a SQL Server table?

1. Nonclustered index
2. Filtered index
3. Clustered index
4. XML index

Correct Answer: 3

Explanation

A clustered index determines the logical order in which the table’s data rows are organized based on the clustering key. Because a table can have only one clustered index, administrators should choose its key carefully. Clustered indexes are often useful for columns commonly used for range searches, ordering, or joins. The exact physical storage behavior depends on the storage engine, but the clustered index provides the primary organization of the table’s data.

Question 255

How many clustered indexes can a SQL Server table have?

1. One
2. Two
3. Five
4. Unlimited

Correct Answer: 1

Explanation

A table can have only one clustered index because the clustered index defines the organization of the table’s data rows. A table can have multiple nonclustered indexes because these are separate structures that reference the underlying data. Administrators must therefore carefully select the clustered index key based on query patterns, uniqueness, size, and data modification behavior. Choosing an unsuitable clustered index can negatively affect both query performance and maintenance operations.

Question 256

Which feature divides a large table or index into smaller logical portions based on a partitioning key?

1. Compression
2. Partitioning
3. Encryption
4. Replication

Correct Answer: 2

Explanation

Table and index partitioning divides data into smaller partitions based on a partitioning column and partition function. Partitioning can make large tables easier to manage and can improve performance for queries that can eliminate unnecessary partitions. It can also simplify operations such as loading or archiving specific ranges of data. Partitioning does not automatically improve every workload, so administrators should design partitions around actual data distribution and query patterns.

Question 257

What is partition elimination?

1. Removing unused database users
2. Reading only the table partitions relevant to a query
3. Deleting old database backups
4. Removing unused indexes

Correct Answer: 2

Explanation

Partition elimination occurs when the query optimizer can determine that only certain partitions can contain the rows required by a query. The database engine can then avoid scanning irrelevant partitions, reducing the amount of data that must be processed. Effective partition elimination depends on query predicates matching the partitioning strategy. Administrators should design partition keys and queries carefully so that common filtering operations can take advantage of partition elimination.

Question 258

Which feature can reduce the amount of storage required by database data or indexes?

1. Data compression
2. Row-Level Security
3. Query Store
4. Database auditing

Correct Answer: 1

Explanation

Data compression reduces the storage space required by database data and, in supported scenarios, indexes. Compression can also reduce I/O because fewer pages may need to be read from storage. However, compression requires additional CPU resources for compression and decompression operations. Administrators should evaluate workload characteristics before enabling compression and should monitor CPU usage and performance after applying it. The best choice depends on the balance between storage, I/O, and CPU resources.

Question 259

Which feature can retain historical query plans and runtime statistics for performance troubleshooting?

1. Query Store
2. Azure Firewall
3. Resource Group
4. Key Vault

Correct Answer: 1

Explanation

Query Store maintains historical information about queries, execution plans, and runtime performance, depending on its configuration. This allows administrators to investigate how query performance changes over time rather than relying only on current execution information. Query Store is particularly useful for identifying plan regressions and understanding which queries consume significant resources. Administrators can use this information when troubleshooting slow applications and deciding whether plan forcing or query optimization is appropriate.

Question 260

Which SQL Server tool provides a graphical interface for viewing current sessions, processes, locks, and database activity?

1. Activity Monitor
2. SQL Server Configuration Manager
3. SQL Server Profiler
4. Database Engine Tuning Advisor

Correct Answer: 1

Explanation

Activity Monitor provides a graphical overview of current SQL Server activity. Administrators can use it to examine processes, resource usage, waits, and other operational information while troubleshooting database performance. It can help identify active sessions and potentially problematic workloads. For deeper investigation, administrators may combine Activity Monitor with dynamic management views, execution plans, Query Store, and Extended Events. Activity Monitor is mainly useful for real-time operational observation rather than long-term performance history.