View Full Microsoft DP-300 Exam Dumps and Practice Test Dumps

 

Question 141. Which Azure SQL Database feature helps automatically detect and resolve common performance issues?

1) Azure Advisor
2) Automatic tuning
3) Azure Storage Explorer
4) Microsoft Sentinel

Answer: 2) Automatic tuning

Explanation:
Automatic tuning in Azure SQL Database continuously monitors database performance and can identify common issues such as missing indexes or inefficient indexes. Based on observed workload patterns, it can recommend or automatically apply certain corrective actions. This feature helps database administrators reduce the amount of manual performance monitoring required. Automatic tuning works by analyzing query performance and comparing current behavior with previous workload characteristics. Administrators can configure whether recommendations are only reported or automatically implemented. It is especially useful in environments where workloads change frequently and ongoing performance optimization is required.

Question 142. Which command is used to create a new database in SQL Server?

1) CREATE DATABASE
2) NEW DATABASE
3) ADD DATABASE
4) BUILD DATABASE

Answer: 1) CREATE DATABASE

Explanation:
The CREATE DATABASE statement is used to create a new database in SQL Server. It can be used with a simple syntax or extended to specify data files, log files, file sizes, growth settings, and other database properties. Database administrators commonly use this statement when creating databases manually or through deployment scripts. After creation, additional configuration can be performed according to application requirements. The other options are not valid Transact-SQL commands for creating a SQL Server database. Using CREATE DATABASE provides administrators with direct control over important database creation parameters.

Question 143. Which SQL Server component is primarily responsible for executing Transact-SQL queries?

1) SQL Server Database Engine
2) SQL Server Reporting Services
3) SQL Server Integration Services
4) SQL Server Analysis Services

Answer: 1) SQL Server Database Engine

Explanation:
The SQL Server Database Engine is the primary component responsible for storing, processing, and securing data in SQL Server databases. It receives Transact-SQL statements from applications or users, generates execution plans, and processes the requested operations. The Database Engine also manages transactions, indexes, security, and other core database functions. Reporting Services is designed for reporting, Integration Services focuses on data integration and transformation, and Analysis Services provides analytical capabilities. Therefore, when the requirement involves executing SQL queries against relational databases, the Database Engine is the appropriate SQL Server component.

Question 144. Which isolation level prevents dirty reads but still allows non-repeatable reads?

1) Read Uncommitted
2) Read Committed
3) Repeatable Read
4) Serializable

Answer: 2) Read Committed

Explanation:
Read Committed prevents a transaction from reading data that another transaction has modified but not yet committed. This means dirty reads are prevented. However, the same query executed more than once within a transaction can return different results if another transaction commits changes between the reads. This behavior is known as a non-repeatable read. Repeatable Read provides stronger protection by preventing changes to rows already read, while Serializable provides even stronger isolation. Read Uncommitted provides the weakest isolation and permits dirty reads, making Read Committed a suitable answer for this requirement.

Question 145. What is the primary purpose of a database transaction?

1) To permanently delete database objects
2) To group related operations into a single logical unit of work
3) To increase database storage capacity
4) To automatically create indexes

Answer: 2) To group related operations into a single logical unit of work

Explanation:
A database transaction groups one or more related operations into a logical unit of work. Transactions help maintain data consistency by ensuring that operations can either be committed together or rolled back when an error occurs. SQL Server transactions follow the principles associated with atomicity, consistency, isolation, and durability. For example, transferring money between two accounts may require both a debit and a credit operation to succeed together. If one operation fails, the transaction can be rolled back. Transactions therefore play an important role in maintaining reliable and consistent database operations.

Question 146. Which statement is used to permanently save the changes made during a transaction?

1) SAVE
2) APPLY
3) COMMIT
4) ACCEPT

Answer: 3) COMMIT

Explanation:
The COMMIT statement permanently saves the changes made by a transaction. When SQL Server reaches a COMMIT statement, the modifications performed within the transaction become durable according to the database’s transaction and recovery mechanisms. If an error occurs before the transaction is committed, the transaction can instead be rolled back using the ROLLBACK statement. This approach allows database administrators and developers to control groups of related changes as a single logical operation. SAVE is not the standard Transact-SQL command for permanently committing a transaction, making COMMIT the correct choice.

Question 147. Which command reverses the changes made during an active transaction?

1) CANCEL
2) ROLLBACK
3) REVERSE
4) UNDO

Answer: 2) ROLLBACK

Explanation:
ROLLBACK is used to undo changes made during a transaction that has not yet been committed. It is commonly used when an operation encounters an error or when a validation condition fails. By rolling back the transaction, SQL Server returns affected data to the state it had before the transaction began, subject to the transaction scope and savepoints involved. ROLLBACK is especially useful for protecting data integrity when several related operations must succeed together. Unlike COMMIT, which makes transaction changes permanent, ROLLBACK cancels the uncommitted changes.

Question 148. Which SQL Server object provides a virtual representation of data from one or more tables?

1) View
2) Trigger
3) Stored procedure
4) Index

Answer: 1) View

Explanation:
A view is a database object that presents data through a virtual table-like structure based on a query. A view can retrieve data from one or multiple tables and can simplify complex queries for users and applications. Views can also help control access by exposing only selected columns or rows instead of giving users direct access to underlying tables. Unlike a traditional table, a standard view generally does not store a separate copy of the result data. Stored procedures execute programmable operations, triggers respond to specific database events, and indexes improve data access performance.

Question 149. Which database object is commonly used to improve the speed of data retrieval?

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

Answer: 1) Index

Explanation:
An index is a database structure designed to help SQL Server locate rows more efficiently when processing queries. Instead of scanning an entire table for every search, SQL Server can use an appropriate index to quickly identify relevant data. Indexes are particularly useful for columns frequently used in filtering, joining, sorting, or grouping operations. However, indexes also require storage and maintenance, and excessive indexing can increase the cost of data modification operations such as INSERT, UPDATE, and DELETE. Therefore, database administrators should create indexes based on workload requirements and query performance analysis.

Question 150. Which tool provides a graphical interface for managing SQL Server databases and executing queries?

1) SQL Server Management Studio
2) Windows Media Player
3) Paint
4) Task Scheduler

Answer: 1) SQL Server Management Studio

Explanation:
SQL Server Management Studio, commonly called SSMS, is a graphical management environment used to connect to SQL Server instances, Azure SQL resources, and other supported services. Database administrators can use SSMS to create and manage databases, execute Transact-SQL queries, configure security, inspect database objects, monitor activity, and troubleshoot performance issues. It provides Object Explorer and query editing capabilities that make database administration more accessible. The other options are Windows utilities that do not provide the specialized database management functionality required for administering SQL Server environments.

Question 151. Which Azure service provides centralized management of secrets, keys, and certificates?

1) Azure Key Vault
2) Azure Blob Storage
3) Azure Monitor
4) Azure Virtual Network

Answer: 1) Azure Key Vault

Explanation:
Azure Key Vault is designed to securely store and manage sensitive information such as secrets, cryptographic keys, and certificates. Applications and database services can retrieve required secrets from Key Vault instead of storing credentials directly in application code or configuration files. Key Vault also supports access control and integration with Microsoft Entra ID. This helps organizations improve security and manage sensitive values centrally. Azure Blob Storage is primarily used for object storage, Azure Monitor provides monitoring capabilities, and Azure Virtual Network provides network connectivity and isolation rather than dedicated secret management.

Question 152. What is the purpose of a foreign key constraint?

1) To ensure values are unique in a table
2) To enforce a relationship between tables
3) To encrypt table data
4) To automatically create a backup

Answer: 2) To enforce a relationship between tables

Explanation:
A foreign key constraint establishes and enforces a relationship between columns in related tables. It typically references a primary key or another unique key in the parent table. The constraint helps maintain referential integrity by preventing invalid references, such as inserting a child row that points to a nonexistent parent row. Foreign keys are commonly used when modeling relationships such as customers and orders or departments and employees. A unique constraint is used to enforce uniqueness, encryption protects data, and backups provide recovery capabilities, so none of those functions describes a foreign key.

Question 153. Which constraint ensures that a column cannot contain NULL values?

1) UNIQUE
2) FOREIGN KEY
3) NOT NULL
4) CHECK

Answer: 3) NOT NULL

Explanation:
The NOT NULL constraint requires a column to contain a value for every inserted or updated row. It is commonly used for fields that are mandatory, such as customer names, account identifiers, or transaction dates. When a column is defined as NOT NULL, SQL Server rejects attempts to insert NULL into that column unless the operation otherwise supplies a valid value. A UNIQUE constraint controls duplicate values, a FOREIGN KEY enforces relationships between tables, and a CHECK constraint validates whether values satisfy a specified condition. Therefore, NOT NULL directly addresses the requirement to prevent NULL values.

Question 154. Which feature can be used to automatically execute a predefined action when a data modification occurs?

1) Trigger
2) View
3) Index
4) Synonym

Answer: 1) Trigger

Explanation:
A trigger is a database object that automatically executes in response to specified events. In SQL Server, DML triggers can respond to INSERT, UPDATE, or DELETE operations. Triggers can be used for tasks such as auditing changes, enforcing certain business rules, or maintaining related information. They should be designed carefully because they execute as part of the transaction that caused the triggering event and can affect application performance. A view provides a virtual representation of data, an index supports query performance, and a synonym provides an alternative name for another database object.

Question 155. Which SQL Server feature allows administrators to schedule recurring database maintenance tasks?

1) SQL Server Agent
2) SQL Server Profiler
3) Database Engine Tuning Advisor
4) Query Store

Answer: 1) SQL Server Agent

Explanation:
SQL Server Agent is a SQL Server service designed to automate administrative and maintenance tasks. Administrators can create jobs that execute according to defined schedules or respond to specific conditions. Jobs can perform activities such as database backups, index maintenance, integrity checks, or execution of stored procedures. This automation reduces the need for administrators to perform repetitive tasks manually. SQL Server Profiler is used for tracing activity, Database Engine Tuning Advisor provides workload-based recommendations, and Query Store captures query performance information. SQL Server Agent is therefore the appropriate tool for scheduled maintenance jobs.

Question 156. Which command displays the execution plan for a query before it is executed?

1) SET SHOWPLAN_ALL ON
2) SET DISPLAYPLAN ON
3) SHOW QUERY PLAN
4) EXECUTE PLAN

Answer: 1) SET SHOWPLAN_ALL ON

Explanation:
SET SHOWPLAN_ALL ON instructs SQL Server to return detailed information about the execution plan generated for subsequent Transact-SQL statements rather than executing those statements normally. The output can help database administrators understand how SQL Server intends to process a query, including operators and estimated costs. This information can be useful when troubleshooting query performance and identifying inefficient access methods. SHOWPLAN settings require appropriate permissions. The other listed commands are not valid Transact-SQL statements for obtaining an execution plan in SQL Server.

Question 157. What does the term RPO represent in database disaster recovery?

1) Recovery Point Objective
2) Recovery Process Operation
3) Restore Performance Output
4) Recovery Protection Option

Answer: 1) Recovery Point Objective

Explanation:
Recovery Point Objective, or RPO, defines the maximum acceptable amount of data loss measured in time following a disruptive event. For example, an organization with an RPO of 15 minutes aims to limit potential data loss to approximately the previous 15 minutes of activity. RPO is an important consideration when designing backup and disaster recovery strategies because it influences how frequently data must be backed up or replicated. RTO, by contrast, refers to the target amount of time required to restore a service. RPO therefore focuses primarily on acceptable data-loss exposure.

Question 158. What does RTO represent in disaster recovery planning?

1) Recovery Transaction Order
2) Recovery Time Objective
3) Restore Table Operation
4) Recovery Transfer Output

Answer: 2) Recovery Time Objective

Explanation:
Recovery Time Objective, or RTO, represents the targeted maximum amount of time required to restore a system or service after a disruption. Organizations establish RTO values according to business requirements and the importance of the affected workload. A shorter RTO generally requires recovery processes and infrastructure capable of restoring services quickly. RTO is different from RPO, which focuses on the acceptable amount of data loss measured in time. Database administrators consider both values when designing backup, replication, failover, and disaster recovery strategies for SQL Server and Azure database environments.

Question 159. Which SQL Server feature records query execution information to help analyze historical query performance?

1) Query Store
2) SQL Server Browser
3) Database Mail
4) SQL Server Agent

Answer: 1) Query Store

Explanation:
Query Store captures query text, execution plans, runtime statistics, and related performance information over time. This historical information allows database administrators to investigate performance changes and identify queries whose behavior has changed. Query Store can be particularly useful when a query becomes slower after an execution plan change because administrators can compare different plans and performance statistics. SQL Server Agent is designed for automation, Database Mail supports email notifications, and SQL Server Browser assists with instance discovery. Query Store is therefore the SQL Server feature specifically designed for historical query performance analysis.

Question 160. Which command is used to modify the structure of an existing SQL Server table?

1) CHANGE TABLE
2) MODIFY TABLE
3) ALTER TABLE
4) UPDATE TABLE

Answer: 3) ALTER TABLE

Explanation:
The ALTER TABLE statement is used to modify the structure of an existing table in SQL Server. Depending on the requirement, it can be used to add, modify, or remove columns and constraints. For example, an administrator can use ALTER TABLE to add a new column or create a constraint associated with the table. UPDATE is different because it changes data stored in existing rows rather than changing the table structure. CHANGE TABLE and MODIFY TABLE are not standard SQL Server commands. ALTER TABLE is therefore the appropriate statement for structural table modifications.