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

 

Question 201. Which Azure SQL Database feature helps automatically improve query performance by forcing a previously successful execution plan when a regression is detected?

1) Automatic plan correction
2) Dynamic Data Masking
3) Row-Level Security
4) Azure Backup

Answer: 1) Automatic plan correction

Explanation:

Automatic plan correction can help address query performance regressions by identifying problematic execution plan changes and taking corrective action based on previously observed plans. It works with Query Store information to analyze query performance and determine when a plan change may have caused degradation. This capability can reduce the amount of manual intervention required from database administrators. Query Store provides the historical performance information that supports this process, while security features such as Dynamic Data Masking and Row-Level Security serve different purposes. Therefore, automatic plan correction is the appropriate feature for automatically addressing certain query plan regressions.

Question 202. Which Azure SQL Database feature allows a database administrator to configure automatic performance recommendations and actions?

1) Automatic tuning
2) Azure DNS
3) Azure Storage
4) Microsoft Entra Connect

Answer: 1) Automatic tuning

Explanation:

Automatic tuning helps database administrators monitor workload performance and apply supported performance recommendations automatically. Depending on the service and configuration, it can address issues involving indexes and query execution plans. The objective is to improve database performance while reducing the amount of manual performance monitoring required. Administrators can review the available configuration and actions and choose which automatic tuning capabilities should be enabled. Azure DNS manages domain name resolution, Azure Storage provides storage services, and Microsoft Entra Connect is used for identity synchronization scenarios. Therefore, Automatic tuning is the correct choice for configuring automated database performance improvements.

Question 203. Which SQL Server feature allows administrators to identify queries consuming significant CPU, memory, or execution time?

1) Query Store
2) Database Mail
3) Foreign Key
4) Transparent Data Encryption

Answer: 1) Query Store

Explanation:

Query Store captures query execution information and runtime statistics that can help administrators investigate resource-intensive queries. By reviewing query performance over time, administrators can identify queries that consume significant CPU, take longer to execute, or show performance regressions. This historical information is particularly useful when troubleshooting workload changes because administrators can compare query plans and runtime behavior. Database Mail is intended for email communication, foreign keys enforce relationships between tables, and Transparent Data Encryption protects data at rest. Therefore, Query Store is the appropriate feature for analyzing query workload and identifying resource-intensive queries.

Question 204. Which SQL Server tool provides a graphical interface for managing databases, executing queries, and configuring database objects?

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

Answer: 1) SQL Server Management Studio

Explanation:

SQL Server Management Studio (SSMS) is a graphical management environment used to connect to SQL Server and supported Azure SQL environments. Database administrators can use SSMS to create and manage databases, execute Transact-SQL queries, configure security, manage database objects, inspect performance information, and perform many administrative tasks. SQL Server Agent focuses on job scheduling, Query Store collects query performance information, and Database Mail handles email communication. SSMS provides a broad set of management and development capabilities in one interface. Therefore, SQL Server Management Studio is the correct choice when a graphical tool is required for administering SQL Server databases.

Question 205. Which command is used to change the definition of an existing SQL Server table?

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

Answer: 1) ALTER TABLE

Explanation:

The ALTER TABLE statement modifies the structure of an existing table. Administrators can use it to add, modify, or remove columns and to manage certain constraints and other table-level definitions. For example, an administrator might add a new column to an existing table without recreating the entire table. The UPDATE statement changes data values rather than the table structure. SQL Server does not use UPDATE TABLE, MODIFY DATABASE, or CHANGE TABLE as the standard syntax for modifying a table definition. Therefore, ALTER TABLE is the correct command for changing the structure of an existing SQL Server table.

Question 206. Which command is used to add a new column to an existing SQL Server table?

1) ALTER TABLE … ADD
2) UPDATE TABLE … ADD
3) CREATE COLUMN
4) INSERT COLUMN

Answer: 1) ALTER TABLE … ADD

Explanation:

The ALTER TABLE … ADD syntax is used to add a new column to an existing table. The administrator specifies the column name and its data type, along with any required properties such as nullability or a default value. This operation changes the table schema while preserving the existing table as an object. INSERT is used to add rows rather than columns, and SQL Server does not use CREATE COLUMN or INSERT COLUMN as standard commands for adding table columns. Therefore, ALTER TABLE … ADD is the appropriate syntax when a new column needs to be added to an existing table.

Question 207. Which SQL Server statement removes an existing database table and its data?

1) DELETE TABLE
2) DROP TABLE
3) REMOVE TABLE
4) CLEAR TABLE

Answer: 2) DROP TABLE

Explanation:

The DROP TABLE statement removes a table from the database, including the table definition and its stored data. Because the operation removes the database object itself, administrators should use it carefully and verify dependencies before execution. DELETE removes rows while keeping the table structure available, so it does not remove the table object. SQL Server does not use DELETE TABLE, REMOVE TABLE, or CLEAR TABLE as the standard syntax for dropping a table. Therefore, DROP TABLE is the correct statement when the requirement is to remove an existing table and its data from the database.

Question 208. Which SQL Server statement removes selected rows from a table while preserving the table structure?

1) DROP
2) DELETE
3) REMOVE
4) DESTROY

Answer: 2) DELETE

Explanation:

The DELETE statement removes rows from a table while leaving the table structure intact. A WHERE clause can be used to identify specific rows that should be deleted. If a WHERE clause is omitted, all rows in the table may be removed, so administrators should use the command carefully. Unlike DROP TABLE, DELETE does not remove the table object itself. The table, columns, indexes, and other definitions remain available after the operation. Therefore, DELETE is the correct statement when selected records need to be removed while preserving the underlying table structure.

Question 209. Which SQL Server statement changes existing values stored in table rows?

1) UPDATE
2) ALTER
3) CREATE
4) DROP

Answer: 1) UPDATE

Explanation:

The UPDATE statement modifies existing data stored in one or more rows of a table. It can change one or multiple columns and can use a WHERE clause to restrict the operation to specific records. For example, an administrator could update an employee’s department or modify a customer’s contact information. The ALTER statement is used to change database object definitions, while CREATE creates objects and DROP removes objects. A carefully written WHERE clause is important because omitting it can cause the update to affect every row in the target table. Therefore, UPDATE is the correct statement for changing existing row values.

Question 210. Which SQL Server statement adds new rows to a table?

1) INSERT
2) ADD ROW
3) CREATE ROW
4) UPDATE ROW

Answer: 1) INSERT

Explanation:

The INSERT statement adds new rows to an existing table. It can be used to provide values for selected columns or for all appropriate columns in the target table. Administrators and applications commonly use INSERT operations when creating new records such as customers, orders, or employee information. UPDATE modifies existing rows rather than creating new ones, while ALTER TABLE changes the table structure. SQL Server does not use ADD ROW or CREATE ROW as standard commands for inserting records. Therefore, INSERT is the appropriate SQL statement when new rows need to be added to an existing table.

Question 211. Which constraint ensures that a foreign key value corresponds to a valid key value in another table?

1) Referential integrity
2) Domain integrity
3) Entity masking
4) Query integrity

Answer: 1) Referential integrity

Explanation:

Referential integrity ensures that relationships between related tables remain valid. A foreign key normally references a primary key or suitable unique key in another table, and the database engine uses the relationship to prevent invalid references according to the configured constraints. For example, an order record should not reference a customer ID that does not exist in the customer table. This helps maintain consistency between parent and child records. Domain integrity concerns valid values within a column, while entity integrity is associated with unique row identification. Therefore, referential integrity is the correct concept for ensuring valid foreign key relationships.

Question 212. Which constraint automatically provides a value when no value is supplied for a column during an INSERT operation?

1) CHECK
2) DEFAULT
3) FOREIGN KEY
4) UNIQUE

Answer: 2) DEFAULT

Explanation:

A DEFAULT constraint specifies a value that SQL Server can automatically use when an INSERT operation does not provide a value for the associated column, subject to the column and statement configuration. For example, a CreatedDate column can have a default expression that supplies the current date and time when a new row is inserted. CHECK constraints validate values against conditions, FOREIGN KEY constraints enforce relationships, and UNIQUE constraints prevent duplicate values. Defaults are useful for reducing application-side logic and ensuring commonly required values are consistently populated. Therefore, the DEFAULT constraint is the correct choice for automatically supplying a value when one is not explicitly provided.

Question 213. Which SQL Server object automatically executes a defined action when specified INSERT, UPDATE, or DELETE events occur?

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

Answer: 1) Trigger

Explanation:

A trigger is a database object that automatically executes when specified events occur. Depending on the trigger type, it can respond to operations such as INSERT, UPDATE, or DELETE. Triggers can be used to enforce business rules, maintain audit information, or perform related actions when data changes. They execute as part of the transaction associated with the triggering event, so their design should consider performance and transaction behavior. A view presents query results, an index supports data retrieval, and a login is a security principal. Therefore, a trigger is the correct object for automatically responding to specified data modification events.

Question 214. Which SQL Server feature allows administrators to create a reusable logical representation of data without necessarily storing a separate copy of the result set?

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

Answer: 1) View

Explanation:

A view provides a reusable logical representation of data based on a stored query definition. Instead of repeatedly writing a complex SELECT statement, users or applications can query the view as a database object. Views can simplify access to data, expose only selected columns, and provide a consistent interface to underlying tables. A standard view does not normally store a separate physical copy of the query results. Indexes improve access paths, triggers respond to database events, and logins handle authentication. Therefore, a view is the appropriate database object when administrators need a reusable logical representation of query results.

Question 215. Which SQL Server feature can restrict access to selected columns or rows by exposing only the required data through a view?

1) View
2) Transaction log
3) Backup
4) Index rebuild

Answer: 1) View

Explanation:

A view can provide controlled access to database information by exposing only selected columns and rows from underlying tables. For example, an administrator can create a view that includes employee names and departments while excluding sensitive salary information. Permissions can then be granted on the view instead of directly on the underlying tables, depending on the security design. Views can therefore simplify access and support data abstraction. However, views should be combined with appropriate permission management because creating a view alone does not automatically provide complete security. Therefore, a view is the appropriate feature for presenting only the required subset of database information.

Question 216. Which Azure SQL capability allows administrators to use Microsoft Entra identities for database authentication?

1) Microsoft Entra authentication
2) SQL Server Agent
3) Query Store
4) Database Mail

Answer: 1) Microsoft Entra authentication

Explanation:

Microsoft Entra authentication allows supported Azure SQL services to authenticate users and applications using identities managed through Microsoft Entra ID. This can reduce reliance on traditional SQL authentication credentials and supports centralized identity and access management. Administrators can configure appropriate Microsoft Entra administrators, users, groups, and authentication methods based on organizational requirements. Query Store is designed for performance monitoring, SQL Server Agent handles scheduled jobs in supported environments, and Database Mail provides email functionality. Therefore, Microsoft Entra authentication is the correct capability when an organization wants to authenticate Azure SQL users through Microsoft Entra identities.

Question 217. Which SQL Server permission allows a principal to read data from a table?

1) SELECT
2) EXECUTE
3) ALTER
4) CONTROL SERVER

Answer: 1) SELECT

Explanation:

The SELECT permission allows a user, role, or other database principal to retrieve data from database objects such as tables and views, subject to the applicable permission hierarchy. Administrators can grant SELECT at different scopes to provide access appropriate to the user’s responsibilities. EXECUTE is used for running executable objects such as stored procedures, while ALTER allows certain object-definition changes. CONTROL SERVER is a much broader server-level permission and should not be used when simple read access is required. Therefore, SELECT is the appropriate permission when a principal needs to read data from a table.

Question 218. Which SQL Server permission allows a principal to add, modify, or remove data from a table when the corresponding permissions are granted?

1) SELECT
2) Data modification permissions
3) VIEW DEFINITION
4) CONNECT

Answer: 2) Data modification permissions

Explanation:

SQL Server provides separate permissions for modifying data, including INSERT, UPDATE, and DELETE. These permissions allow a principal to add new rows, change existing rows, or remove rows respectively. Administrators can grant only the specific modification permissions required instead of providing unnecessarily broad access. SELECT controls data retrieval, VIEW DEFINITION controls access to metadata definitions, and CONNECT allows a principal to establish applicable connections. Applying the principle of least privilege helps ensure that users receive only the permissions needed for their duties. Therefore, data modification permissions are the correct category for changing table data.

Question 219. Which SQL Server permission allows a user to view the definition of database objects when granted appropriately?

1) VIEW DEFINITION
2) SELECT DATA
3) READ OBJECT
4) VIEW TABLE

Answer: 1) VIEW DEFINITION

Explanation:

The VIEW DEFINITION permission allows a principal to view metadata and definitions of database objects within the applicable scope. This can include information about objects such as tables, views, procedures, and other database structures, depending on the permission scope. It is useful when developers or administrators need to inspect object definitions without necessarily receiving permission to modify or query the underlying data. SELECT is intended for reading data rather than object definitions. Therefore, VIEW DEFINITION is the appropriate permission when the requirement is to allow a user to inspect database object metadata and definitions.

Question 220. Which security principle recommends giving users only the permissions required to perform their assigned tasks?

1) Principle of least privilege
2) Maximum privilege principle
3) Shared credential principle
4) Unlimited access principle

Answer: 1) Principle of least privilege

Explanation:

The principle of least privilege recommends granting users and applications only the permissions necessary to perform their authorized tasks. This reduces the potential impact of accidental or unauthorized actions because accounts do not have unnecessary access to sensitive resources. In SQL Server, administrators can implement least privilege by assigning appropriate database roles and granting specific permissions such as SELECT, INSERT, UPDATE, DELETE, or EXECUTE according to job requirements. Granting excessive permissions can increase security risks and make access management more difficult. Therefore, the principle of least privilege is the correct security concept for limiting permissions to what is necessary.