70-762 Free Practice Test

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 collect query performance data while minimizing the performance impact on the SQL Server. What should you do?

1. A. Create a sys.dm_os_waiting_tasks query.
2. B. Create a sys.dm_exec_sessions query.
3. C. Create a Performance Monitor Data CollectorSet.
4. D. Create a sys.dm_os_memory_objects query.
5. E. Create a sp_configure ‘max server memory’ query.
6. F. Create a SQL Profiler trace.
7. G. Create a sys.dm_os_wait_stats query.
8. H. Create an Extended Event.

Correct Answer: C
SQL Server Data Collector is a feature for performance monitoring and tuning available in SQL Server Management Studio.
Integration Services packages transform and load the collected data into the Microsoft Data Warehouse database.
Collection sets are defined and deployed on a server instance and can be run independently of each other. Each collection set can be applied to a target that matches the target types of all the collector types that are part of a collection set. The collection set is run by a SQL Server Agent job or jobs, and data is uploaded to the management data warehouse on a predefined schedule.
Predefined data collection sets include:
* The Query Statistics data collection set collects information about query statistics, activity, execution plans and text on the SQL Server instance. It does not store all executed statements, only 10 worst performing ones.
* Disk Usage data collection set collects information about disk space used by both data and log files for all
databases on the SQL Server instance, growth trends, and average day growth. Etc.
References:
http://www.sqlshack.com/sql-server-performance-monitoring-data-collector/

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

What should you add at line 02?

1. A. with check_option
2. B. with recompile
3. C. with view metadata
4. D. with schenabinding

Correct Answer: D

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?

1. A. table-valued function
2. B. a schema-bound view
3. C. a partitioned view
4. D. a DML trigger

Correct Answer: B
When you use the SchemaBinding keyword while creating a view or function you bind the structure of any underlying tables or views. It means that as long as that schemabound object exists as a schemabound object (ie you don’t remove schemabinding) you are limited in changes that can be made to the tables or views that it refers to.
References: https://sqlstudies.com/2014/08/06/schemabinding-what-why/

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.

Solution:


Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

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?

1. A. Yes
2. B. No

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