Download 70-762 Lab 2021

NEW QUESTION 1
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 2
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. Exhibit

Both of the stored procedures experience blocking issues. UspB must not abort if UspA commits changes to a row before UspB commits changes to the same row. UspA must not abort if UspB commits changes to a row before UspA commits changes to the same row.
You need to specify the transaction isolation levels to enable row versioning.
How should you complete the Transact-SQL statements? To answer, drag the Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
References: https://technet.microsoft.com/en-us/library/ms175095(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx

NEW QUESTION 3
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 4
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 5
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. DDL trigger
• F. scalar-valued function
• G. table-valued function

Answer: B

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.
A CLR trigger is a type of DDL trigger. A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger. Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.
References: https://msdn.microsoft.com/en-us/library/ms178110.aspx

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 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 8
Note: The 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 question in the series. Information and details provided in a question apply only to that question.
You have a database named DB1. The database does not use a memory-optimized filegroup. The database contains a table named Table1. The table must support the following workloads:

You need to add the most efficient index to support the new OLTP workload, while not deteriorating the existing Reporting query performance.
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 clusteredcolumnstore index on the table.
• E. Create a nonclustered columnstore index on the table.
• F. Create a hash index on the table.

Answer: C

Explanation:
A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed
filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
References: https://technet.microsoft.com/en-us/library/cc280372(v=sql.105).aspx

NEW QUESTION 9
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 independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
You need to ensure that the performance of each instance is consistent for the same queried and query plans. What should you do?

• A. Create a sys.dm_os_waiting_tasks query.
• B. Create a sys.dm_exec_sessions query.
• C. Create a Performance Monitor Data Collector Set.
• D. Create a sys.dm_os_memory_objects query.
• E. Create a sp_configure ‘max server memory’ query.
• F. Create aSQL Profiler trace.
• G. Create asys.dm_os_wait_stats query.
• H. Create an Extended Event.

Answer: H

Explanation:
Advanced Viewing of Target Data from Extended Events in SQL Server
When your event session is currently active, you might want to watch the event data in real time, as it is received by the target.
Management > Extended Events > Sessions > [your-session] > Watch Live Data.
The query_post_execution_showplan extended event enables you to see the actual query plan in the SQL Server Management Studio (SSMS) UI. When the Details pane is visible, you can see a graph of the query plan on the Query Plan tab. By hovering over a node on the query plan, you can see a list of property names and their values for the node.

References: https://msdn.microsoft.com/en-us/library/mt752502.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 Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data for the last order that the customer placed.
You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.
The Leads table must include the columns described in the following table.

The data types chosen must consume the least amount of storage possible. You need to select the appropriate data types for the Leads table.
In the table below, identify the data type that must be used for each table column. NOTE: Make only one selection in each column.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Bit is a Transact-SQL integer data type that can take a value of 1, 0, or NULL.
Smallint is aT ransact-SQL integer data type that can take a value in the range from -32,768 to 32,767. int, bigint, smallint, and tinyint (Transact-SQL)
Exact-number data types that use integer data.

References: https://msdn.microsoft.com/en-us/library/ms187745.aspx https://msdn.microsoft.com/en-us/library/ms177603.aspx

NEW QUESTION 11
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 12
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 cascading referential integrity constraints on the table. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

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

NEW QUESTION 13
You are designing a stored procedure for a database named obi.
The following requirements must be met during the entire execution of the stored procedure:
*The stored procedure must only read changes that are persisted to the database.
*select statements within the stored procedure should only show changes to the data that are made by the stored procedure
You need to configure the transaction isolation level for the stored procedure. Which Transact-SQL statement or statements should you run?
A)

B)

C)

D)

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: A

NEW QUESTION 14
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 Transact-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 need to create Sales.FemaleCustomers.
How should you complete the view definition? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact_SQL segment may be used once, more than once or not at all. You may need to drag the split bar between panes or scroll to view content.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1:WITH SCHEMABINDING:
SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.
Box 2:Box 2: WITH CHECK OPTION
CHECK OPTION forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
Note: Sales.Female.Customers must meet the following requirements: References: https://msdn.microsoft.com/en-us/library/ms187956.aspx

NEW QUESTION 15
You have a data warehouse fact table that has a clustered columnstore index. You have multiple CSV files that contain a total of 3 million rows of data.
You need to upload the data to the fact table. The solution must avoid the delta group when you import the data.
Which solution will achieve the goal in the least amount of time?

• A. Load the source data to a staging tabl
• B. Load the data to the fact table by using the insert_select statement and specify the Tablock option on the staging table.
• C. Load the source data to a staging table that has a clustered index on the primary ke
• D. Copy the data to the fact table by using the insert_select statement.
• E. Load the source data to the fact table by running bcp.exe and specify the _ Tablock option.
• F. Load the source data to the fact table by using the bulk insert statement and specify the Tablock option.

Answer: D

NEW QUESTION 16
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:

Users frequently run the following query. The users report that the query takes a long time to return results.

You need to minimize the amount of time required for the query to return data. 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 nonclusteredcolumnstore index on TBL1. Create a nonclustered index on TBL2.
• G. Drop existing indexes on tbli and then create a clustered columnstore inde
• H. Create a nonclustered columnstore index on tbl
• I. Make no changes to TBL2.
• J. Create CHECK constraints on both TBL1 and TBL2.. Create a partitioned view that combines columns fromTBL1 and TBL2.
• K. Create an indexed view that combines columns from TBL1 and TBL2.

Answer: H

NEW QUESTION 17
You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.
Every evening, stores must transmit s5les data to the corporate office. The data must be inserted into the summary table that includes the StorelD, ProductID, Qtysold, Totprodsales, and Datesold Columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.
What should you include in your solution?

• A. Create a unique constraint.
• B. Create a foreign key constraint to the storeID column in each of the store tables.
• C. Create a rule and bind it to the storeID column.
• D. Create a check constraint.

Answer: B

NEW QUESTION 18
You have several real-time applications that constantly update data in a database. The applications run more than 400 transactions per second that insert and update new metrics from sensors.
A new web dashboard is released to present the data from the sensors. Engineers report that the applications take longer than expected to commit updates.
You need to change the dashboard queries to improve concurrency and to support reading uncommitted data. 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 XACTJVBORT to ON.
• G. Execute the alter table ti set (Lock_ESCALATION = auto); statement.
• H. Use the output parameters.

Answer: C

NEW QUESTION 19
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 and application to track customer sales.
You need to return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SQL statements.
You need to create a database object. 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: F

Explanation:
User-defined scalar functions return a single data value of the type defined in the RETURNS clause. References: https://technet.microsoft.com/en-us/library/ms177499(v=sql.105).aspx

NEW QUESTION 20
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 uspUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.
When you start uspUpdateCustomerSummary, there are no active transactions. The procedure fails at line 09 due to a CHECK constraint violation on the TotalDepositAccountCount column.
What is the impact of the stored procedure on the CustomerDetails table?

• A. The value of the TotalAccountCount column decreased.
• B. The value of the TotalDepositAccountCount column is not changed.
• C. The statement that modifies TotalDepositAccountCount is excluded from the transaction.
• D. The value of the TotalAccountCount column is not changed.

Answer: D

NEW QUESTION 21
You have a database that contains both disk-based and memory-optimized tables.
You need to create two modules. The modules must meet the requirements described in the following table.

Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Module 1: Interpreted stored procedure
An interpreted stored procedure can access both disk-based and memory-optimized tables. Module 2: Natively compiled stored procedure
Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures?

NEW QUESTION 22
You need to create a view that can be indexed. You write the following statement.

What should you add at line 02?

• A. with check_option
• B. with recompile
• C. with view metadata
• D. with schenabinding

Answer: D

NEW QUESTION 23
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.
Your company has employees in different regions around the world.
You need to create a database table that stores the following employee attendance information:
- Employee ID
- date and time employee checked in to work
- date and time employee checked out of work
Date and time information must be time zone aware and must not store fractional seconds. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation:
datetime2 defines adate that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, andoptional user-specified precision.
References: https://msdn.microsoft.com/en-us/library/bb677335.aspx

NEW QUESTION 24
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 Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data for the last order that the customer placed.
You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.
You need to implement a stored procedure that deletes a discontinued product from the Products table. You identify the following requirements:
* If an open order includes a discontinued product, the records for the product must not be deleted.
* The stored procedure must return a custom error message if a product record cannot be deleted. The message must identify the OrderID for the open order.
What should you do? To answer, select the appropriate Transact-SQL segments in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Using TRY...CATCH in Transact-SQL
Errors in Transact-SQL code can be processed by using a TRY…CATCH construct. TRY…CATCH can use the following error function to capture error information:
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
References: https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

NEW QUESTION 25
......