Verified 70-762 Forum 2021

NEW QUESTION 1
You are a database developer for a company that delivers produce and other refrigerated goods to grocery stores. You capture the food storage temperature for delivery vehicles by using Internet of Things (loT) devices. You store the temperature data in a database table named vchicleTewperatures. The chillerSensorNumber column stores the identifier for the loT devices.
You need to create an indexed view that meets the following requirements:
• Persists the data on disk to reduce the amount of I/O.
• Provides the number of chillerSensorNumber items.
• Creates only a set sof summary rows.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 2
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a database that contains a table named Employees. The table stores information about the employees of your company.
You need to implement the following auditing rules for the Employees table:
- Record any changes that are made to the data in the Employees table.
- Customize the data recorded by the audit operations.
Solution: You implement a user-defined function on the Employees table. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation:
SQL Server 2021 provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.
Change data is made available to change data capture consumers through table-valued functions (TVFs). References: https://msdn.microsoft.com/en-us/library/cc645858.aspx

NEW QUESTION 3
You are creating a stored procedure which will insert data into the table shown in the Database schema exhibit. (Click the exhibit button.)

You need to insert a new customer record into the tables as a single unit of work.
In which order should you use the Transact-SQL segments to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange the, in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
The entities on the many side, of the 1-many relations, must be added before we add the entities on the 1-side.
We must insert new rows into BusinessEntityContact and BusinessEntityAddress tables, before we insert the corresponding rows into the BusinessEntity and AddressType tables.

NEW QUESTION 4
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1. There is no memory-optimized filegroup in the database.
You have a table and a stored procedure that were created by running the following Transact-SQL statements:

The Employee table is persisted on disk. You add 2,000 records to the Employee table. You need to create an index that meets the following requirements:
- Optimizes the performance of the stored procedure.
- Covers all the columns required from the Employee table.
- Uses FirstName and LastName as included columns.
- Minimizes index storage size and index key size. What should you do?

• A. Create a clustered index on the table.
• B. Create a nonclustered index on the table.
• C. Create a nonclustered filtered index on the table.
• D. Create a clustered columnstore index on the table.
• E. Create a nonclustered columnstore index on the table.
• F. Create a hash index on the table.

Answer: B

Explanation:
References: https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx

NEW QUESTION 5
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a database that contains a table named Employees. The table stores information about the employees of your company.
You need to implement and enforce the following business rules:
- Limit the values that are accepted by the Salary column.
- Prevent salaries less than $15,000 and greater than $300,000 from being entered.
- Determine valid values by using logical expressions.
- Do not validate data integrity when running DELETE statements. Solution: You implement a FOR UPDATE trigger on the table. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
References:
http://stackoverflow.com/questions/16081582/difference-between-for-update-of-and-for-update

NEW QUESTION 6
You have multiple stored procedures inside a transaction.
You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.
What should you do?

• A. Use the NOLOCK option.
• B. Execute the DBCC UPDATEUSAGE statement.
• C. Use the max worker threads Option.
• D. Use a table-valued parameter.
• E. Set SET ALLOW SNAPSHOT ISOLATION to ON,
• F. Set SET XACT ABORT to ON.
• G. Execute the alter table T1 set (lock escalation = auto); statement.
• H. Use the output parameters.

Answer: B

NEW QUESTION 7
You are developing an application that connects to a database. The application runs the following jobs:

The READ_COMMITTED_SNAPSHOT database option is set to OFF, and auto-content is set to ON. Within the stored procedures, no explicit transactions are defined.
If JobB starts before JobA, it can finish in seconds. If JobA starts first, JobB takes a long time to complete. You need to use Microsoft SQL Server Profiler to determine whether the blocking that you observe in JobB is caused by locks acquired by JobA.
Which trace event class in the Locks event category should you use?

• A. LockAcquired
• B. LockCancel
• C. LockDeadlock
• D. LockEscalation

Answer: A

Explanation:
The Lock:Acquired event class indicates that acquisition of a lock on a resource, such as a data page, has been achieved.
The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated.

NEW QUESTION 8
Note: this question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
- Return a value of 0 if data is inserted successfully into the Customers table.
- Return a value of 1 if data is not inserted successfully into the Customers table.
- Support logic that is written by using managed code.
- Support TRY…CATCH error handling. What should you create?

• A. extended procedure
• B. CLR procedure
• C. user-defined procedure
• D. DML trigger
• E. scalar-valued function
• F. table-valued function

Answer: D

Explanation:
DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.
References: https://msdn.microsoft.com/en-us/library/ms178110.aspx

NEW QUESTION 9
Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.
The Account table was created by using the following Transact-SQL statement:

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:

You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
Create a clustered index on the AccountNumber column as it is unique, not a non nonclustered one. References: https://msdn.microsoft.com/en-us/library/ms190457.aspx

NEW QUESTION 10
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers. Website.Customer must meet the following requirements:
* Allow users access to the CustomerName and CustomerNumber columns for active customers.
* Allow changes to the columns that the view references. Modified data must be visible through the view.
* Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:
* Allow users access to the CustomerName, Address, City, State and PostalCode columns.
* Prevent changes to the columns that the view references.
* Only allow updates through the views that adhere to the view filter.
You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL statement:

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries.
Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.
You must update the design of the Customer table to meet the following requirements.
* You must be able to store up to 50 accounts for each customer.
* Users must be able to retrieve customer information by supplying an account number.
* Users must be able to retrieve an account number by supplying customer information. You need to implement the design changes while minimizing data redundancy.
What should you do?

• A. Split the table into three separate table
• B. Include the AccountNumber and CustomerID columns in the first tabl
• C. Include the CustomerName and Gender columns in the second tabl
• D. Include the AccountStatus column in the third table.
• E. Split the table into two separate table
• F. Include AccountNumber, CustomerID, CustomerName and Gender columns in the first tabl
• G. Include the AccountNumber and AccountStatus columns in the second table.
• H. Split the table into two separate tables, Include the CustomerID and AccountNumber columns in the first tabl
• I. Include the AccountNumber, AccountStatus, CustomerName and Gender columns in the second table.
• J. Split the table into two separate tables, Include the CustomerID, CustomerName and Gender columns in the first tabl
• K. IncludeAccountNumber, AccountStatus and CustomerID columns in the second table.

Answer: D

Explanation:
Two tables are enough. CustomerID must be in both tables.

NEW QUESTION 11
You are analyzing the performance of a database environment. You need to find all unused indexes in the current database.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Example: Following query helps you to find all unused indexes within database using sys.dm_db_index_usage_stats DMV.
-- Ensure a USE statement has been executed first. SELECT u.*
FROM [sys].[indexes] i
INNER JOIN[sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID) AND i.[index_id] = u.[index_id]
AND u.[database_id] = DB_ID() --returning the database ID of the current database WHERE o.[type] <>'S' --shouldn't be a system base table
AND i.[type_desc] <> 'HEAP' AND i.[name] NOT LIKE 'PK_%'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0 AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC
References: https://basitaalishan.com/2012/06/15/find-unused-indexes-using-sys-dm_db_index_usage_stats/

NEW QUESTION 12
You have the following stored procedure:

The Numbers table becomes unavailable when you run the stored procedure. The stored procedure obtains an exclusive lock on the table and does not release the lock.
What are two possible ways to resolve the issue? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

• A. Remove the implicit transaction and the SET ANSI_DEFAULTS ON statement.
• B. Set the ANSI_DEFAULT statement to OFF and add a COMMIT TRANSACTION statement after the INSERT statement.
• C. Add a COMMIT TRANSACTION statement after the INSERT statement.
• D. Remove the SET ANSI DEFAULTS ON statement.

Answer: B

NEW QUESTION 13
Case study Background
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
Employee Table
You use the following Transact-SQL statements to create, configure, and populate the Employee table:

Application
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
UspA: This stored procedure updates only the EmployeeStatus column.
UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data.
Views must meet the following requirements:
Allow user access to all columns in the tables that the view accesses.
Restrict updates to only the rows that the view returns.

You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using.
You need to create a single index to replace the existing indexes.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Scenario: You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using.
Current indexes:

NEW QUESTION 14
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that
question.

You have a Microsoft SQL Server database named DB1 that contains the following tables: You frequently run the following queries:

There are no foreign key relationships between TBL1 and TBL2.
You need to minimize the amount of time required for the two queries to return records from the tables. What should you do?

• A. Create clustered indexes on TBL1 and TBL2.
• B. Create a clustered index on TBL1 Create a nonclustered index on tbl2 and add the most frequently queried columns as included columns.
• C. Create a nonclustered index on tbl2 only.
• D. Create unique constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
• E. Drop existing indexes on TBL1 and then create a clustered columnstore inde
• F. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2.
• G. Drop existing indexes on TBL1 and then create a cluwered columnstore inde
• H. Create a nonclustered columnstore index on TBL1. Make no changes to TBL2.
• I. Create check constraints on both TBL1 and tbl2. Create a partitioned view that combines columns from TBL1 and tbl2.
• J. Create an indexed view that combines columns from TBL1 and TBL2.

Answer: F

NEW QUESTION 15
You run the following Transact-SQL following statement:

Customer records may be inserted individually or in bulk from an application. You observe that the application attempts to insert duplicate records.
You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications.
Which Transact-SQL statement should you run?

• A. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH(ONLINE = OFF)
• B. CREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY= ON)
• C. CREATE UNIQUE INDEX IX Customer Code ON Customer (Code) WITH (IGNORE DUP KEY=OFF)
• D. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code)
• E. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = ON)

Answer: B

Explanation:
IGNORE_DUP_KEY = { ON | OFF } specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

NEW QUESTION 16
You have an existing Microsoft SQL Trace script. You plan to convert the script to an Extended Events session.
You need to collect the trace ID and other required information. Which system table should you use?

• A. dbo.syssessions
• B. trace xenction_map
• C. db
• D. syinotifications
• E. sysdbmaintplan.

Answer: A

NEW QUESTION 17
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index.
Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

You need to resolve the performance issue. Solution: You drop the nonclustered index.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 18
You manage a Microsoft Azure SQL Database that has the Standard tier plan. The database size has increased and users experience slow performance.
You need to identify usage for the following resources:
• CPU utilization
• Disk storage
• Memory utilization
• Disk I/O
………………………………..

• A. Activity monitor
• B. sys.dm_exec_connections
• C. Azure portal
• D. sys.rtuource_usage
• E. sys . dm_db_resource_stats

Answer: BC

NEW QUESTION 19
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:
* The table must reference the ProductID column in the Product table
* Existing records in the ProductReview table must not be validated with the Product table.
* Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.
* Changes to records in the Product table must propagate to the ProductReview table.
You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.
You must modify the Orders table to meet the following requirements:
* Create new rows in the table without granting INSERT permissions to the table.
* Notify the sales person who places an order whether or not the order was completed.
You must add the following constraints to the SalesHistory table:
* a constraint on the SaleID column that allows the field to be used as a record identifier
* a constant that uses the ProductID column to reference the Product column of the ProductTypes table
* a constraint on the CategoryID column that allows one row with a null value in the column
* a constraint that limits the SalePrice column to values greater than four
Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.
You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:
* The table must hold 10 million unique sales orders.
* The table must use checkpoints to minimize I/O operations and must not use transaction logging.
* Data loss is acceptable.
Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.
You need to update the SalesHistory table
How should you complete the Transact_SQL statement? To answer? select the appropriate Transact-SQL, segments in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1:
SaleID must be the primary key, as a constraint on the SaleID column that allows the field to be used as a record identifier is required.
Box2:
A constraint that limits the SalePrice column to values greater than four. Box 3: UNIQUE
A constraint on the CategoryID column that allows one row with a null value in the column. Box 4:
A foreign key constraint must be put on the productID referencing the ProductTypes table, as a constraint that uses the ProductID column to reference the Product column of the ProductTypes table is required.
Note: Requirements are:
You must add the following constraints to the SalesHistory table:

NEW QUESTION 20
Note: this question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create an object that meets to following requirements:
Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
Run written a transaction and roll back if a failure occurs.
Run when a table is created or modified.
What should you create?

• A. extended procedure
• B. CLR procedure
• C. user-defined procedure
• D. DML trigger
• E. DDL trigger
• F. scalar-valued function
• G. table-valued function

Answer: B

Explanation:
The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code.
With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code.
References:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration

NEW QUESTION 21
You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues:
At each level of the query plan, a low total number of rows are processed.
The query uses many operations. This results in a high overall cost for the query. You need to identify the information that will be useful for the optimizer.
What should you do?

• A. Start a SQL Server Profiler trace for the event class Performance statistics in the Performance eventcategory.
• B. Create one Extended Events session with the sqlserver.missing_column_statistics event added.
• C. Start a SQL Server Profiler trace for the event class Soft Warnings in the Errors and Warnings event category.
• D. Create one Extended Events session with the sqlserver.error_reported event added.

Answer: A

Explanation:
The Performance Statistics event class can be used to monitor the performance of queries, stored procedures, and triggers that are executing. Each of the six event subclasses indicates an event in the lifetime of queries, stored procedures, and triggers within the system. Using the combination of these event subclasses and the associated sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats dynamic management views, you can reconstitute the performance history of any given query, stored procedure, or trigger.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/performance-statistics-event-class?view=

NEW QUESTION 22
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:
* The table must reference the ProductID column in the Product table
* Existing records in the ProductReview table must not be validated with the Product table.
* Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.
* Changes to records in the Product table must propagate to the ProductReview table.
You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.
You must modify the Orders table to meet the following requirements:
* Create new rows in the table without granting INSERT permissions to the table.
* Notify the sales person who places an order whether or not the order was completed.
You must add the following constraints to the SalesHistory table:
* a constraint on the SaleID column that allows the field to be used as a record identifier
* a constant that uses the ProductID column to reference the Product column of the ProductTypes table
* a constraint on the CategoryID column that allows one row with a null value in the column
* a constraint that limits the SalePrice column to values greater than four
Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.
You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:
* The table must hold 10 million unique sales orders.
* The table must use checkpoints to minimize I/O operations and must not use transaction logging.
* Data loss is acceptable.
Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.
You need to modify the design of the Orders table. What should you create?

• A. a stored procedure with the RETURN statement
• B. a FOR UPDATE trigger
• C. an AFTER UPDATE trigger
• D. a user defined function

Answer: D

Explanation:
Requirements: You must modify the Orders table to meet the following requirements:
1. Create new rows in the table without granting INSERT permissions to the table.
2. Notify the sales person who places an order whether or not the order was completed. References: https://msdn.microsoft.com/en-us/library/ms186755.aspx

NEW QUESTION 23
......