070-762無料問題集「Microsoft Developing SQL Databases」

質問 1

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）Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on 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）Drop existing indexes on tbli and then create a clustered columnstore index. Create a nonclustered columnstore index on tbli. Make no changes to TBL2.

（D）Create CHECK constraints on both TBL1 and TBL2.. Create a partitioned view that combines columns fromTBL1 and TBL2.

（E）Create unique constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.

（F）Create clustered indexes on TBL1 and TBL2.

（G）Create an indexed view that combines columns from TBL1 and TBL2.

（H）Create a nonclustered index on TBL2 only.

正解：D 解答を投票する

質問 2

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?

（A）extended procedure

（B）DML trigger

（C）table-valued function

（D）user-defined procedure

（E）CLR procedure

（F）scalar-valued function

（G）DDL trigger

正解：D 解答を投票する

解説: (JPNTest メンバーにのみ表示されます)

質問 3

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?

（A）Create a sys.dm_exec_sessions query.

（B）Create a sp_configure 'max server memory' query.

（C）Create an Extended Event.

（D）Create a SQL Profiler trace.

（E）Create a sys.dm_os_waiting_tasks query.

（F）Create a sys.dm_os_memory_objects query.

（G）Create a Performance Monitor Data CollectorSet.

（H）Create a sys.dm_os_wait_stats query.

正解：G 解答を投票する

解説: (JPNTest メンバーにのみ表示されます)

質問 4

You manage a database that supports an internal of Things (IOT) solution. The database records metrics from over 100 million devices every minute. The database requires 99.995 uptime.
The database uses a table named checkins that is 100 gigabytes (GB) in size. The checkins table stores metrics from the devices. The database also has a table named Archive that stores four terabytes (TB) of data. You use store produce for all access to the tables.
You observe that the wait type PAGELATCH_ to causes large amount of blocking.
You need to resolve the blocking issues while minimizing downtime for the database.
Which two actions should you perform? Each correct answer present part of the solution.

（A）Convert the checkins table to a cluster clounmstore indexes.

（B）Convert the checkins table to an in memory OLTP table.

（C）Convert all tables to clustered columnstore indexes.

（D）Convert all stored procedure that accesses the checkins table to natively compile procedures.

正解：D 解答を投票する

質問 5

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）LockEscalation

（C）LockCancel

（D）LockDeadlock

正解：A 解答を投票する

解説: (JPNTest メンバーにのみ表示されます)

質問 6

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 named DB1 that includes a table named Sales-Orders.
You grant a user named User1 select permissions on the sales schema.
You need to ensure that User1 can select data from the sales .orders table without specifying the schema name in any Transact-SQL statements.
Solution: You create a stored procedure in the sales schema that selects the data from the sales. orders table.
Does the solution meet the goal?

（A）Yes

（B）No

正解：A 解答を投票する

質問 7

You have tables with the following requirements:
* Some records in the table named OrderLines do not exist in the table named Order.
* The column named OrderNumber must be a unique value in the Order table.
* The OrderNumber column on the OrderLines table allows null values.
* The OrderNumber column on the OrderLines table must be validated with the Order table.
* Must not allow a new record in the OrderLines table that does not match a record in the Order table.
How should you complete the statements? To answer, select the appropriate transact-SQL segments from the drop-down menus in the answer area.
NOTE: Each correct selection is worth one point.

正解：

Explanation

Box 1: PRIMARY KEY
Box 2: NOCHECK
Need NOCHECK as some records in the table named OrderLines do not exist in the table named Order.
Box 3: FOREIGN KEY
FOREIGN KEY allows inserting NULL values if there is no NOT NULL constraint defined on this key, but the PRIMARY KEY does not accept NULLs.
References:
https://www.sqlshack.com/commonly-used-sql-server-constraints-foreign-key-check-default/

質問 8

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?

（A）Yes

（B）No

正解：B 解答を投票する

解説: (JPNTest メンバーにのみ表示されます)

質問 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 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 tables described in the following table:

There are no foreign key relationships between TBL1 and TBL2.
You need to minimize the amount of time required for queries that use data from TBL1 and TBL2 to return data.
What should you do?

（A）Create a nonclustered index on tbl2 only.

（B）Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create noncluster index on TBL2.

（C）Create a clustered index on TBL1. Create a nonclustered index on TBL2 and add the most frequently queried columns as included columns.

（D）Create unique constraints on both TBL1 and TBL2.Create a partitioned view that combines columns from TBL1 and TBL2.

（E）Create clustered indexes on TBL1 and TBL2.

（F）Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1 Make no changes to TBL2.

正解：C 解答を投票する

質問 10

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 tables described in the following table:

There are no foreign key relationships between TBL1 and TBL2.
You need to minimize the amount of time required for queries that use data from TBL1 and TBL2 to return data.
What should you do?

（A）Create a nonclustered index on tbl2 only.

（B）Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create noncluster index on TBL2.

（C）Create a clustered index on TBL1. Create a nonclustered index on TBL2 and add the most frequently queried columns as included columns.

（D）Create unique constraints on both TBL1 and TBL2.Create a partitioned view that combines columns from TBL1 and TBL2.

（E）Create clustered indexes on TBL1 and TBL2.

（F）Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1 Make no changes to TBL2.

正解：C 解答を投票する

070-762 無料問題集「Microsoft Developing SQL Databases」

弊社を連絡する

関連リンク

トップ試験