View Full Microsoft DP-300 Exam Dumps and Practice Test Dumps
Q101. Which Azure SQL Database feature helps identify query performance regressions by retaining query execution information over time?
1) Azure Monitor
2) Query Store
3) Elastic pool
4) Microsoft Defender for SQL
Correct Answer: 2)
Explanation:
Query Store collects and retains information about queries, execution plans, and runtime performance statistics. Database administrators can use this information to compare query performance over time and identify performance regressions. For example, a query that previously executed quickly may begin taking significantly longer after a plan change. Query Store helps administrators investigate these changes and determine whether a different execution plan or another tuning action may be required. It is an important performance troubleshooting capability for Azure SQL Database and SQL Server because it provides historical query information rather than only showing the current execution state.
Q102. Which option is most appropriate for improving query performance when a frequently searched column does not have a suitable access path?
1) Creating an appropriate index
2) Increasing backup retention
3) Enabling auditing
4) Creating a private endpoint
Correct Answer: 1)
Explanation:
Creating an appropriate index can improve query performance when queries frequently search, join, sort, or filter data using a particular column. An index provides a structure that allows the database engine to locate qualifying rows more efficiently than scanning the entire table in many situations. However, indexes also consume storage and can increase the cost of insert, update, and delete operations because index structures may need to be maintained. Administrators should analyze the workload and execution plans before creating indexes. The goal is to create useful indexes that support important queries without introducing unnecessary maintenance overhead.
Q103. Which database object provides a reusable virtual representation of data based on a query?
1) Index
2) Trigger
3) View
4) Database role
Correct Answer: 3)
Explanation:
A view is a database object that presents the results of a query as a virtual table. It can simplify access to complex queries by allowing users and applications to reference the view instead of repeatedly writing the underlying query. Views can also help provide controlled access to selected columns or rows. Unlike a traditional table, a standard view does not normally store a separate copy of the underlying data. When the view is queried, the database engine processes the associated query against the underlying objects. Views can therefore improve query organization, abstraction, and security management.
Q104. Which SQL command is used to change the structure of an existing table?
1) SELECT
2) INSERT
3) ALTER
4) DELETE
Correct Answer: 3)
Explanation:
The ALTER statement is used to modify the structure of an existing database object. For a table, administrators can use ALTER TABLE to add, modify, or remove columns and to make other supported structural changes. SELECT retrieves data, INSERT adds new rows, and DELETE removes existing rows. Structural changes should be carefully planned because they can affect applications, stored procedures, views, indexes, and other database dependencies. Before modifying a production table, administrators should evaluate the potential impact and, when appropriate, test the change in a controlled environment.
Q105. Which Azure SQL Database service tier is designed for workloads requiring low latency and high availability with locally attached storage?
1) Basic
2) General Purpose
3) Business Critical
4) Serverless
Correct Answer: 3)
Explanation:
The Business Critical service tier is designed for workloads that require high performance, low-latency data access, and strong availability characteristics. It uses locally attached storage and provides features appropriate for demanding transactional workloads. Business Critical can also provide multiple replicas that support high availability and read-scale scenarios. General Purpose is designed for a broader range of workloads and uses remote storage architecture. Basic is intended for lighter workloads, while serverless is a compute option associated with specific workload patterns. Administrators should select the service tier according to performance, availability, storage, and workload requirements.
Q106. Which authentication method allows users to authenticate to Azure SQL Database using identities managed through Microsoft Entra ID?
1) SQL Server authentication only
2) Microsoft Entra authentication
3) Anonymous authentication
4) FTP authentication
Correct Answer: 2)
Explanation:
Microsoft Entra authentication allows users and applications to authenticate to supported Azure SQL services using identities managed through Microsoft Entra ID. This approach can integrate database access with an organization’s identity and access management system. Administrators can create Microsoft Entra-based database users or configure appropriate authentication mechanisms for applications. It can also support centralized identity management and security policies. SQL authentication uses SQL-specific usernames and passwords, while Microsoft Entra authentication can use organizational identities and supported service identities. Proper database permissions must still be assigned after authentication is successfully configured.
Q107. Which security principle requires users to receive only the permissions necessary to perform their assigned tasks?
1) High availability
2) Data redundancy
3) Least privilege
4) Load balancing
Correct Answer: 3)
Explanation:
The principle of least privilege requires users, applications, and services to receive only the permissions necessary to perform their required tasks. Applying this principle reduces unnecessary access and can limit the potential impact of compromised accounts or accidental operations. In an Azure SQL environment, administrators can implement least privilege by assigning users to appropriate database roles and granting only required permissions. Excessive permissions should be avoided, especially for administrative and application accounts. Regular permission reviews can help identify access that is no longer required and ensure that database security remains aligned with organizational responsibilities.
Q108. Which SQL permission statement explicitly prevents a principal from performing a specific database operation?
1) GRANT
2) DENY
3) REVOKE
4) CREATE
Correct Answer: 2)
Explanation:
The DENY statement explicitly prevents a database principal from receiving a specified permission through that permission assignment. It is different from REVOKE, which removes an explicit permission assignment, and GRANT, which provides a permission. DENY is useful when administrators need to explicitly block access to a particular database object or operation. However, SQL Server permission evaluation can involve multiple levels and role memberships, so administrators should understand the effective permission model when troubleshooting access. Proper use of DENY can help enforce security requirements when certain operations must be specifically prohibited.
Q109. Which feature protects database files at rest by encrypting the underlying data files?
1) Row-Level Security
2) Dynamic Data Masking
3) Transparent Data Encryption
4) Query Store
Correct Answer: 3)
Explanation:
Transparent Data Encryption, or TDE, protects data at rest by encrypting database files and related storage components. It helps protect the physical database files if the underlying storage is accessed without authorization. TDE operates transparently to applications, so applications generally do not need to be modified to use encrypted database storage. It is different from Always Encrypted, which is designed to protect selected column values from access by the database engine in supported configurations. TDE is therefore an important database security capability for protecting stored data without requiring application-level encryption changes.
Q110. Which Azure feature can send a notification when a monitored database metric exceeds a configured threshold?
1) Azure Monitor alert
2) Azure Private Link
3) Azure DNS
4) Query Store
Correct Answer: 1)
Explanation:
Azure Monitor alerts can notify administrators when monitored metrics or conditions meet configured alert rules. For Azure SQL resources, administrators can monitor relevant performance and availability metrics and create alert rules based on thresholds. For example, an alert could be configured when a monitored resource experiences unusually high utilization. Alerts help administrators respond to potential issues before they become more serious. Azure Private Link provides private connectivity, Azure DNS provides name resolution, and Query Store focuses on query performance information. Azure Monitor alerts therefore provide an important mechanism for proactive database monitoring.
Q111. Which database feature helps provide information about data distribution to the query optimizer?
1) Statistics
2) Firewall rules
3) Database roles
4) Audit logs
Correct Answer: 1)
Explanation:
Statistics contain information about the distribution of values in columns and help the query optimizer estimate how many rows a query will return. Accurate statistics allow the optimizer to make better decisions when selecting execution plans, including which indexes and join strategies to use. When statistics become outdated because data changes significantly, query performance may be affected. Administrators can monitor statistics and ensure they are updated appropriately. Automatic statistics maintenance can handle many common scenarios, but understanding statistics remains important when troubleshooting query performance and analyzing why the optimizer selected a particular execution strategy.
Q112. Which command removes an existing table and its definition from a database?
1) DELETE TABLE
2) DROP TABLE
3) REMOVE TABLE
4) CLEAR TABLE
Correct Answer: 2)
Explanation:
DROP TABLE removes the specified table definition from the database, along with the table’s stored data and associated table structure. It is a structural operation and should be used carefully because it removes the object itself rather than simply deleting selected rows. DELETE is used to remove rows from an existing table while keeping the table structure. Administrators should verify dependencies and recovery considerations before dropping a production table. If the table is needed again, recovery may require restoring it from an appropriate backup or recreating it from another source.
Q113. Which operation removes selected rows from a table while allowing a WHERE clause to identify the rows?
1) DROP
2) ALTER
3) DELETE
4) CREATE
Correct Answer: 3)
Explanation:
The DELETE statement removes rows from a table and can use a WHERE clause to identify which rows should be removed. This makes DELETE appropriate when administrators need to remove specific records while retaining the table and its structure. If a WHERE clause is omitted, DELETE can remove all rows from the table, so administrators should use the command carefully. DROP TABLE has a different purpose because it removes the entire table object. ALTER changes object structure, while CREATE creates new database objects. Testing the filtering condition before executing a large DELETE operation is a useful safety practice.
Q114. Which Azure SQL capability allows applications to continue using a secondary database when a planned or unplanned failover occurs between paired databases?
1) Failover group
2) Dynamic Data Masking
3) Query Store
4) Database role
Correct Answer: 1)
Explanation:
An Azure SQL failover group provides a mechanism for managing failover between databases hosted in different Azure regions. It can provide a listener endpoint that applications use instead of connecting directly to a specific database server. During a failover, the endpoint can direct connections to the appropriate primary database in the new region. This can simplify application connectivity during regional outages or planned maintenance. Failover groups are designed for business continuity and disaster recovery scenarios. Administrators should configure appropriate policies and test failover procedures to ensure applications can reconnect successfully when required.
Q115. Which Azure SQL capability provides a read-only copy of a database that can be used to distribute read workloads?
1) Read-scale replica
2) Database firewall
3) Azure Monitor alert
4) Long-term retention
Correct Answer: 1)
Explanation:
Read-scale replicas can help distribute read-only workloads away from the primary database. This can be useful for applications that perform many reporting or analytical read operations while the primary database handles transactional activity. By directing appropriate read workloads to a readable replica, organizations can reduce pressure on the primary workload and improve overall resource utilization. Read-scale capabilities depend on the selected Azure SQL service and configuration. Administrators should ensure that applications are designed to direct suitable read operations to the appropriate replica and understand that replicas may have synchronization characteristics.
Q116. Which Azure SQL feature provides a logical server endpoint that applications can use to connect to the current primary database in a failover group?
1) Failover group listener
2) Query Store
3) Elastic pool
4) Private DNS only
Correct Answer: 1)
Explanation:
A failover group listener provides an endpoint that applications can use to connect to the current primary database without directly referencing a specific server. When a failover occurs, the listener can direct application connections to the appropriate primary database in the secondary region. This helps reduce the need to modify application connection strings during a failover event. Using a listener is an important part of designing applications for regional disaster recovery with Azure SQL failover groups. Administrators should test application connectivity and DNS behavior to verify that clients can reconnect correctly after failover.
Q117. Which tool is most appropriate for reviewing historical query execution plans and runtime statistics?
1) Azure DNS
2) Query Store
3) Private endpoint
4) Database firewall
Correct Answer: 2)
Explanation:
Query Store is designed to retain query text, execution plans, and runtime performance information over time. This historical information allows administrators to compare different execution plans and investigate changes in query performance. It is particularly useful when a query becomes slower after a plan change because administrators can review previous plans and runtime statistics. Azure DNS, private endpoints, and firewall rules are related to networking and connectivity rather than query performance analysis. Query Store therefore provides the historical workload information required for detailed database performance troubleshooting.
Q118. Which index operation is generally used to reduce logical fragmentation while keeping the existing index structure in place?
1) ALTER INDEX REORGANIZE
2) DROP DATABASE
3) CREATE LOGIN
4) UPDATE TABLE
Correct Answer: 1)
Explanation:
ALTER INDEX REORGANIZE is used to reduce fragmentation by reorganizing the leaf level of an existing index. It is generally an online operation and can be useful for addressing moderate levels of fragmentation without rebuilding the entire index. ALTER INDEX REBUILD is a more intensive operation that creates a new version of the index structure and can be appropriate for higher fragmentation levels depending on the environment. Administrators should evaluate fragmentation, workload requirements, and maintenance windows before selecting an index maintenance operation. Proper index maintenance can help maintain efficient data access and query performance.
Q119. Which SQL Server feature allows administrators to control the amount of CPU and memory resources available to different workloads?
1) Resource Governor
2) Query Store
3) Azure SQL Auditing
4) Dynamic Data Masking
Correct Answer: 1)
Explanation:
Resource Governor allows administrators to manage and control resource consumption for different workloads in supported SQL Server environments. It can classify incoming requests and apply resource limits or controls based on workload groups and resource pools. This capability can help prevent one workload from consuming excessive resources and affecting other important workloads. Query Store focuses on query performance history, auditing records database activity, and Dynamic Data Masking controls how sensitive data is displayed. Resource Governor is therefore specifically associated with workload resource management and is useful when administrators need more control over resource allocation.
Q120. Which Azure SQL service tier is designed to support high-performance workloads with multiple replicas and low-latency storage?
1) Basic
2) General Purpose
3) Business Critical
4) Standard
Correct Answer: 3)
Explanation:
The Business Critical service tier is designed for workloads that require high performance, low-latency storage, and strong availability. It uses locally attached storage and supports multiple replicas, making it suitable for demanding transactional workloads that require fast response times and high availability. General Purpose is intended for a broader range of workloads and uses remote storage architecture. Basic is intended for smaller and less demanding workloads. Selecting the appropriate service tier depends on workload performance, availability, storage, and cost requirements. Administrators should evaluate actual workload characteristics before selecting a service tier.