70-762 無料問題集「Microsoft Developing SQL Databases」
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?
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
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You need to build a function that meets the following requirements:
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL statements 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.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL statements 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.
正解：
Explanation
References: https://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx
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?
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?
正解：D
解答を投票する
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 reporting database that includes a non-partitioned fact table named Fact_Sales. The table is persisted on disk.
Users report that their queries take a long time to complete. The system administrator reports that the table takes too much space in the database. You observe that there are no indexes defined on the table, and many columns have repeating values.
You need to create the most efficient index on the table, minimize disk storage and improve reporting query performance.
What should you do?
You have a reporting database that includes a non-partitioned fact table named Fact_Sales. The table is persisted on disk.
Users report that their queries take a long time to complete. The system administrator reports that the table takes too much space in the database. You observe that there are no indexes defined on the table, and many columns have repeating values.
You need to create the most efficient index on the table, minimize disk storage and improve reporting query performance.
What should you do?
正解：A
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
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?
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?
正解：B
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
You have a table named Person.Address that includes the following columns:
* AddressID
* AddressLine1
* AddressLine2
* City
* StateProvinceID
* PostakCode
* RowGuid
* ModifiedDate
You need to create a nonclustered index on PostalCode named IX_Address_PostalCode that uses the following included columns:
* AddressLine1
* AddressLine2
* City
* StateProvinceID
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact_SQL segments in the answer are.
* AddressID
* AddressLine1
* AddressLine2
* City
* StateProvinceID
* PostakCode
* RowGuid
* ModifiedDate
You need to create a nonclustered index on PostalCode named IX_Address_PostalCode that uses the following included columns:
* AddressLine1
* AddressLine2
* City
* StateProvinceID
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact_SQL segments in the answer are.
正解：
Explanation
Box 1: INDEX
Box 2: ON
Box 3: INCLUDE
INCLUDE (column [ ,... n ] ) specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017
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?
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?
正解：E
解答を投票する
解説: (JPNTest メンバーにのみ表示されます)
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 modify the database design to meet the following requirements:
* Rows in the Orders table must always have a valid value for the ProductID column.
* Rows in the Products table must not be deleted if they are part of any rows in the Orders table.
* All rows in both tables must be unique.
In the table below, identify the constraint that must be configured for each table.
NOTE: Make only one selection in each column.
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 modify the database design to meet the following requirements:
* Rows in the Orders table must always have a valid value for the ProductID column.
* Rows in the Products table must not be deleted if they are part of any rows in the Orders table.
* All rows in both tables must be unique.
In the table below, identify the constraint that must be configured for each table.
NOTE: Make only one selection in each column.
正解：
Explanation
A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Here the foreign key constraint is put on the ProductID in the Orders, and points to the ProductID of the Products table.
With a check constraint on the ProductID we can ensure that the Products table contains only unique rows.
References:
http://www.w3schools.com/sql/sql_foreignkey.asp