Azure Backup<\/span><\/p>\nAnswer: 1) Automatic plan correction<\/b><\/p>\n
Explanation:<\/b><\/p>\n
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.<\/span><\/p>\nQuestion 202. Which Azure SQL Database feature allows a database administrator to configure automatic performance recommendations and actions?<\/b><\/h3>\n
1)<\/b> Automatic tuning<\/span>
\n<\/span>2)<\/b> Azure DNS<\/span>
\n<\/span>3)<\/b> Azure Storage<\/span>
\n<\/span>4)<\/b> Microsoft Entra Connect<\/span><\/p>\nAnswer: 1) Automatic tuning<\/b><\/p>\n
Explanation:<\/b><\/p>\n
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.<\/span><\/p>\nQuestion 203. Which SQL Server feature allows administrators to identify queries consuming significant CPU, memory, or execution time?<\/b><\/h3>\n
1)<\/b> Query Store<\/span>
\n<\/span>2)<\/b> Database Mail<\/span>
\n<\/span>3)<\/b> Foreign Key<\/span>
\n<\/span>4)<\/b> Transparent Data Encryption<\/span><\/p>\nAnswer: 1) Query Store<\/b><\/p>\n
Explanation:<\/b><\/p>\n
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.<\/span><\/p>\nQuestion 204. Which SQL Server tool provides a graphical interface for managing databases, executing queries, and configuring database objects?<\/b><\/h3>\n
1)<\/b> SQL Server Management Studio<\/span>
\n<\/span>2)<\/b> SQL Server Agent<\/span>
\n<\/span>3)<\/b> Query Store<\/span>
\n<\/span>4)<\/b> Database Mail<\/span><\/p>\nAnswer: 1) SQL Server Management Studio<\/b><\/p>\n
Explanation:<\/b><\/p>\n
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.<\/span><\/p>\nQuestion 205. Which command is used to change the definition of an existing SQL Server table?<\/b><\/h3>\n
1)<\/b> ALTER TABLE<\/span>
\n<\/span>2)<\/b> UPDATE TABLE<\/span>
\n<\/span>3)<\/b> MODIFY DATABASE<\/span>
\n<\/span>4)<\/b> CHANGE TABLE<\/span><\/p>\nAnswer: 1) ALTER TABLE<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>ALTER TABLE<\/span> 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 <\/span>UPDATE<\/span> statement changes data values rather than the table structure. SQL Server does not use <\/span>UPDATE TABLE<\/span>, <\/span>MODIFY DATABASE<\/span>, or <\/span>CHANGE TABLE<\/span> as the standard syntax for modifying a table definition. Therefore, <\/span>ALTER TABLE<\/span> is the correct command for changing the structure of an existing SQL Server table.<\/span><\/p>\nQuestion 206. Which command is used to add a new column to an existing SQL Server table?<\/b><\/h3>\n
1)<\/b> ALTER TABLE … ADD<\/span>
\n<\/span>2)<\/b> UPDATE TABLE … ADD<\/span>
\n<\/span>3)<\/b> CREATE COLUMN<\/span>
\n<\/span>4)<\/b> INSERT COLUMN<\/span><\/p>\nAnswer: 1) ALTER TABLE … ADD<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>ALTER TABLE … ADD<\/span> 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. <\/span>INSERT<\/span> is used to add rows rather than columns, and SQL Server does not use <\/span>CREATE COLUMN<\/span> or <\/span>INSERT COLUMN<\/span> as standard commands for adding table columns. Therefore, <\/span>ALTER TABLE … ADD<\/span> is the appropriate syntax when a new column needs to be added to an existing table.<\/span><\/p>\nQuestion 207. Which SQL Server statement removes an existing database table and its data?<\/b><\/h3>\n
1)<\/b> DELETE TABLE<\/span>
\n<\/span>2)<\/b> DROP TABLE<\/span>
\n<\/span>3)<\/b> REMOVE TABLE<\/span>
\n<\/span>4)<\/b> CLEAR TABLE<\/span><\/p>\nAnswer: 2) DROP TABLE<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>DROP TABLE<\/span> 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. <\/span>DELETE<\/span> removes rows while keeping the table structure available, so it does not remove the table object. SQL Server does not use <\/span>DELETE TABLE<\/span>, <\/span>REMOVE TABLE<\/span>, or <\/span>CLEAR TABLE<\/span> as the standard syntax for dropping a table. Therefore, <\/span>DROP TABLE<\/span> is the correct statement when the requirement is to remove an existing table and its data from the database.<\/span><\/p>\nQuestion 208. Which SQL Server statement removes selected rows from a table while preserving the table structure?<\/b><\/h3>\n
1)<\/b> DROP<\/span>
\n<\/span>2)<\/b> DELETE<\/span>
\n<\/span>3)<\/b> REMOVE<\/span>
\n<\/span>4)<\/b> DESTROY<\/span><\/p>\nAnswer: 2) DELETE<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>DELETE<\/span> statement removes rows from a table while leaving the table structure intact. A <\/span>WHERE<\/span> clause can be used to identify specific rows that should be deleted. If a <\/span>WHERE<\/span> clause is omitted, all rows in the table may be removed, so administrators should use the command carefully. Unlike <\/span>DROP TABLE<\/span>, 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.<\/span><\/p>\nQuestion 209. Which SQL Server statement changes existing values stored in table rows?<\/b><\/h3>\n
1)<\/b> UPDATE<\/span>
\n<\/span>2)<\/b> ALTER<\/span>
\n<\/span>3)<\/b> CREATE<\/span>
\n<\/span>4)<\/b> DROP<\/span><\/p>\nAnswer: 1) UPDATE<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>UPDATE<\/span> statement modifies existing data stored in one or more rows of a table. It can change one or multiple columns and can use a <\/span>WHERE<\/span> 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 <\/span>ALTER<\/span> statement is used to change database object definitions, while <\/span>CREATE<\/span> creates objects and <\/span>DROP<\/span> 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.<\/span><\/p>\nQuestion 210. Which SQL Server statement adds new rows to a table?<\/b><\/h3>\n
1)<\/b> INSERT<\/span>
\n<\/span>2)<\/b> ADD ROW<\/span>
\n<\/span>3)<\/b> CREATE ROW<\/span>
\n<\/span>4)<\/b> UPDATE ROW<\/span><\/p>\nAnswer: 1) INSERT<\/b><\/p>\n
Explanation:<\/b><\/p>\n
The <\/span>INSERT<\/span> 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. <\/span>UPDATE<\/span> modifies existing rows rather than creating new ones, while <\/span>ALTER TABLE<\/span> changes the table structure. SQL Server does not use <\/span>ADD ROW<\/span> or <\/span>CREATE ROW<\/span> as standard commands for inserting records. Therefore, INSERT is the appropriate SQL statement when new rows need to be added to an existing table.<\/span><\/p>\nQuestion 211. Which constraint ensures that a foreign key value corresponds to a valid key value in another table?<\/b><\/h3>\n
1)<\/b> Referential integrity<\/span>
\n<\/span>2)<\/b> Domain integrity<\/span>
\n<\/span>3)<\/b> Entity masking<\/span>
\n<\/span>4)<\/b> Query integrity<\/span><\/p>\nAnswer: 1) Referential integrity<\/b><\/p>\n
Explanation:<\/b><\/p>\n
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.<\/span><\/p>\nQuestion 212. Which constraint automatically provides a value when no value is supplied for a column during an INSERT operation?<\/b><\/h3>\n
1)<\/b> CHECK<\/span>
\n<\/span>2)<\/b> DEFAULT<\/span>
\n<\/span>3)<\/b> FOREIGN KEY<\/span>
\n<\/span>4)<\/b> UNIQUE<\/span><\/p>\nAnswer: 2) DEFAULT<\/b><\/p>\n
Explanation:<\/b><\/p>\n
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.<\/span><\/p>\nQuestion 213. Which SQL Server object automatically executes a defined action when specified INSERT, UPDATE, or DELETE events occur?<\/b><\/h3>\n
1)<\/b> Trigger<\/span>
\n<\/span>2)<\/b> View<\/span>
\n<\/span>3)<\/b> Index<\/span>
\n<\/span>4)<\/b> Login<\/span><\/p>\nAnswer: 1) Trigger<\/b><\/p>\n
Explanation:<\/b><\/p>\n
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.<\/span><\/p>\nQuestion 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?<\/b><\/h3>\n
1)<\/b> View<\/span>
\n<\/span>2)<\/b> Index<\/span>