Page: 1
/ 14
Total 172 questions
Microsoft Developing SQL Databases 70-762 Exam Questions
Question 1
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?
Answer : D
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
Question 2
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 add a unique clustered index to the Employees table.
Does the solution meet the goal?
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 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?
Answer : A
SQL Server 2016 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
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 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?
Answer : D
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
Question 5
Note: This question is part of a series of questions that use the same 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 are developing an application to track customer sales. You create tables to support the application. You need to create a database object that meets the following data entry requirements:
What should you create?
Question 6
You are developing a database reporting solution for a table that contains 900 million rows and is 103 GB.
The table is updated thousands of times a day, but data is not deleted.
The SELECT statements vary in the number of columns used and the amount of rows retrieved.
You need to reduce the amount of time it takes to retrieve data from the table. The must prevent data duplication.
Which indexing strategy should you use?
Answer : B
Columnstore indexes are the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage.
A clustered columnstore index is the physical storage for the entire table.
Generally, you should define the clustered index key with as few columns as possible.
A nonclustered index contains the index key values and row locators that point to the storage location of the table data. You can create multiple nonclustered indexes on a table or indexed view. Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.
Question 7
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?
Answer : A
You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
All Official Question Types