最新的Microsoft Developing SQL Databases - 70-762免費考試真題
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?
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
You create a database table named FactSales by running the following Transact-SQL statements:
You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index.
You need to implement a columnstore index for the table.
How should you complete the Transact-SQL code? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.
You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index.
You need to implement a columnstore index for the table.
How should you complete the Transact-SQL code? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.
正確答案:
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:
* Launch when table data is modified.
* Evaluate the state a table before and after a data modification and take action based on the difference.
* Prevent malicious or incorrect table data operations.
* Prevent changes that violate referential integrity by cancelling the attempted data modification.
* Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.
What should you create?
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
* Launch when table data is modified.
* Evaluate the state a table before and after a data modification and take action based on the difference.
* Prevent malicious or incorrect table data operations.
* Prevent changes that violate referential integrity by cancelling the attempted data modification.
* Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.
What should you create?
正確答案: C
說明：（僅 Fast2test 成員可見）
You suspect deadlocks on a database.
Which two trace flags in the Microsoft SQL Server error log should you locate? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
Which two trace flags in the Microsoft SQL Server error log should you locate? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
正確答案: A,C
說明：（僅 Fast2test 成員可見）
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that is 130 GB and contains 500 million rows of data.
Granular transactions and mass batch data imports change the database frequently throughout the day.
Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.
You discover that some reports time out before they complete.
You need to reduce the likelihood that the reports will time out.
Solution: You partition the largest tables.
Does this meet the goal?
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that is 130 GB and contains 500 million rows of data.
Granular transactions and mass batch data imports change the database frequently throughout the day.
Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.
You discover that some reports time out before they complete.
You need to reduce the likelihood that the reports will time out.
Solution: You partition the largest tables.
Does this meet the goal?
正確答案: A
You have two database tables. Table1 is a partitioned table and Table 2 is a non-partitioned table.
Users report that queries take a long time to complete. You monitor queries by using Microsoft SQL Server Profiler. You observe lock escalation for Table1 and Table 2.
You need to allow escalation of Table1 locks to the partition level and prevent all lock escalation for Table2.
Which Transact-SQL statement should you run for each table? To answer, drag the appropriate Transact-SQL statements to the correct tables. Each command 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.
Users report that queries take a long time to complete. You monitor queries by using Microsoft SQL Server Profiler. You observe lock escalation for Table1 and Table 2.
You need to allow escalation of Table1 locks to the partition level and prevent all lock escalation for Table2.
Which Transact-SQL statement should you run for each table? To answer, drag the appropriate Transact-SQL statements to the correct tables. Each command 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.
正確答案:
Explanation
Since SQL Server 2008 you can also control how SQL Server performs the Lock Escalation - through the ALTER TABLE statement and the property LOCK_ESCALATION. There are 3 different options available:
Box 1: Table1, Auto
The default option is TABLE, means that SQL Server *always* performs the Lock Escalation to the table level -even when the table is partitioned. If you have your table partitioned, and you want to have a Partition Level Lock Escalation (because you have tested your data access pattern, and you don't cause deadlocks with it), then you can change the option to AUTO. AUTO means that the Lock Escalation is performed to the partition level, if the table is partitioned, and otherwise to the table level.
Box 2: Table 2, DISABLE
With the option DISABLE you can completely disable the Lock Escalation for that specific table.
For partitioned tables, use the LOCK_ESCALATION option of ALTER TABLE to escalate locks to the HoBT level instead of the table or to disable lock escalation.
References:
http://www.sqlpassion.at/archive/2014/02/25/lock-escalations/
You have two databases with the following settings:
You run the following Transact -SQL statements:
You need to select data from DiskTable and insert the data into MemTable. You must complete the insertion operation into MemTable as an explicit transaction without immediate durability.
Which four Transact-SQL segments should you use? 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.
You run the following Transact -SQL statements:
You need to select data from DiskTable and insert the data into MemTable. You must complete the insertion operation into MemTable as an explicit transaction without immediate durability.
Which four Transact-SQL segments should you use? 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.
正確答案:
Explanation
Box 1: BEGIN TRANSACTION
Box 2: UPDATE ... #Disktable
Box 3: IF... SELECT INTO ...#Disktable
Box 4: .. DELAYED_DURABILITY = ON
The COMMIT syntax is extended so you can force delayed transaction durability. If DELAYED_DURABILITY is DISABLED or FORCED at the database level (see above) this COMMIT option is ignored.
Syntax:
COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] References:
https://docs.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability?view=sql-server-20
You have a view that includes an aggregate.
You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.
You need to ensure that you can update the view.
What should you create?
You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.
You need to ensure that you can update the view.
What should you create?
正確答案: D
說明：（僅 Fast2test 成員可見）
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.
You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.
You need to improve the application response time.
Solution: You replace the stored procedure with a user-defined function.
Does the solution meet the goal?
You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.
You need to improve the application response time.
Solution: You replace the stored procedure with a user-defined function.
Does the solution meet the goal?
正確答案: A
說明：（僅 Fast2test 成員可見）