IT認証試験問題集
毎月、ITshikenは1500人以上の受験者が試験準備を助けて、試験に合格するために受験者にご協力します
 ホームページ / 70-764 問題集  / 70-764 問題練習

Microsoft 70-764 問題練習

Administering a SQL Database Infrastructure 試験

最新更新時間: 2021/01/16,合計174問。

2021新年のギフト:70-764 最新真題を買う時、日本語版と英語版両方を同時に獲得できます。

実際の問題集を練習し、試験のポイントを了解し、テストに申し込むするかどうかを決めることができます。

さらに試験準備時間の35%を節約するには、70-764 問題集を使用してください。

 / 12

Question No : 1
You need to recommend a solution to allow application users to perform UPDATE operations on the database tables. The solution must meet the business requirements.
What should you recommend?

正解:
Explanation:

Question No : 2
DRAG DROP
You need to assign security to dev_role1.
How should you complete the code? To answer, drag the appropriate elements to the correct locations. Each element 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:
* Scenario: Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables.

Question No : 3
DRAG DROP
You have an SQL Server 2016 server. You plan to create four stored procedures that will use transactions.
The stored procedures will be configured as shown in the following table.



You need to recommend an isolation level for each stored procedure. The solution must support the concurrency strategy of each stored procedure and must minimize locks.
What should you recommend? To answer, drag the appropriate isolation levels to the correct stored procedures. Each isolation level 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:
* SP1 SNAPSHOT
The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.
* SP2: REPEATABLE READ
Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
* SP3: READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
* SP4: SERIALIZABLE
SERIALIZABLE specification include:
/ No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Reference: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms173763.aspx

Question No : 4
You have an SQL Server 2016 server named SQL1.
You are designing a performance monitoring solution.
You need to monitor the following events on SQL1:
・ A deadlock graph
・ Missing column statistics
・ CPU performance statistics
・ A batch of completed Transact-SQL statements
Which two tools should you use? Each correct answer presents a complete solution.

正解:
Explanation:
B: Database Engine Tuning Advisor examines how queries are processed in the databases you specify.
When you run a Profiler Trace and feed it to the Database Engine Tuning Advisor, it also looks for missing column statistics, and it can automatically create them for you.
C: Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.
Reference: Analyze Deadlocks with SQL Server Profiler
https://msdn.microsoft.com/en-us/library/ms188246.aspx
Reference: Mastering SQL Server Profiler, page 245

Question No : 5
DRAG DROP
You have a database named DB1. DB1 contains a table named Sales.SalesPerson that has an index named AK_SalesPerson_rowguid. Queries that do not use the index take approximately 10 times longer to complete than queries that use the index.
You discover that AK_SalesPerson_rowguid has severe fragmentation.
You need to recommend a solution to defragment the index. The solution must ensure that the index can be used by queries during the defragmentation.
What statement should you use? To answer, drag the appropriate elements to the correct locations. Each element 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:
Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online. To achieve availability similar to the reorganize option, you should rebuild indexes online.
Use: ALTER INDEX REBUILD WITH (ONLINE = ON)
Reference: Reorganize and Rebuild Indexes
https://msdn.microsoft.com/en-us/library/ms189858.aspx

Question No : 6
You are using dynamic management views to monitor an SQL Server server named SQL1.
A database administrator named Dba1 must monitor the health of SQL1.
You need to ensure that Dba1 can access dynamic management views for SQL1. The solution must use the principle of least privilege.
Which permissions should you assign to Dba1?

正解:
Explanation:
To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.
Reference: Dynamic Management Views and Functions (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms188754.aspx

Question No : 7
DRAG DROP
You define a table by using the following statement.



The Teachers table contains one million records.
A sample record of the XML data output is as follows:



You plan to create queries against the table based on grade. You need to recommend which indexes should be applied to the XML fields.
The solution must meet the following requirements:
・ Reduce the amount of time required to query the table based on grade.
・ Minimize the size of the index.
What should you recommend? To answer, drag the appropriate index types to the correct fields. Each index type 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:
XML indexes fall into the following categories:
* Primary XML index
* Secondary XML index
The first index on the xml type column must be the primary XML index.
Reference: XML Indexes (SQL Server)
https://msdn.microsoft.com/en-us/library/ms191497.aspx

Question No : 8
You have a server named Server1 that has 2 processors.
You plan to deploy multiple instances of SQL Server 2016 to Server1. Each instance will have multiple databases.
You need to recommend a method to set limits on processor time for each database.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

正解:
Explanation:
SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.
Incorrect answers:
Not A: Processor affinity cannot be used to assign databases to processors.
Not B: The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. It is not used limit the processor time for a database.
Not C: You can use Windows System Resource Manager to allocate processor and memory resources to applications, users, Remote Desktop Services sessions, and Internet Information Services (IIS) application pools. You cannot use Windows System Resource Manager to allocate resources for a database.
Reference: Resource Governor
https://msdn.microsoft.com/en-us/library/bb933866(v=sql.120).aspx

Question No : 9
You have a customer who has several SQL Server 2012 database servers.
You are designing a data warehouse for the customer. The data warehouse will use columnstore indexes.
The customer identifies that the following must be supported for the column store indexes.
・ Data manipulation language (DML) statements
・ Nonclustered columnstore indexes
・ Clustered columnstore indexes
・ Partitioning
You need to identify which technology requires the customer to implement an SQL Server 2016 database.
What should you identify?

正解:
Explanation:
SQL Server 2016 has the features of SQL Server 2012 plus updateable clustered columnstore indexes. This feature is required here as DML statements must be supported in the warehouse.
Reference: Columnstore Indexes Described
https://msdn.microsoft.com/en-us/library/gg492088(v=sql.120).aspx

Question No : 10
You have a SQL Server 2016 environment that contains 20 servers. The corporate security policy states that all SQL Server 2016 instances must meet specific security standards. You need to recommend a management strategy for the SQL Server 2016 servers.
What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

正解:
Explanation:
Policy-Based Management is a system for managing one or more instances of SQL Server. When SQL Server policy administrators use Policy-Based Management, they use SQL Server Management Studio to create policies to manage entities on the server, such as the instance of SQL Server, databases, or other SQL Server objects.
Reference:
Policy-Based Management How-to Topics

Question No : 11
You are the administrator for a SQL Server 2016 instance that stores the data for an online transaction processing sales system.
The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups. These backups are stored on a backup server in the company's data center.
Every week, the company places the full backup on a tape and sends it to a third-party backup storage system.
The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders.
You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

正解:
Explanation:
SQL Server 2012 was the first version to provide the ability to back up databases to the Cloud, and SQL Server 2016 improves on the process.
Microsoft SQL Server Backup to Windows Azure Tool enables backup to Windows Azure Blob Storage and encrypts and compresses SQL Server backups stored locally or in the cloud.
Reference:
Smart, Secure, Cost-Effective: SQL Server Back Up to Windows Azure - SQL Server Team Blog - Site Home - TechNet Blogs

Question No : 12
You have a SQL Server 2016 environment that includes four servers.
The servers are configured as shown in the following table.



You plan to configure Policy-Based Management to enforce the following rules:
・ On Server1, enable SQL Server password policies and enable the default trace.
・ On Server3, ensure that the names of user-defined stored procedures begin with the prefix "usp_" and ensure that all databases use a case-sensitive collation.
You need to recommend which server you must configure as a Central Management Server.
Which server should you recommend? (Each correct answer presents a complete solution. Choose all that apply.)

正解:
Explanation:
Need Standard or Enterprise edition of SQL Server.

Question No : 13
You deploy a database by using SQL Server 2016. The database contains a table named Table1.
You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.
What should you recommend?

正解:
Explanation:
Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
Reference: About Change Data Capture (SQL Server)

Question No : 14
You are designing a SQL Server database for an order fulfillment system.
You create a table named Sales.Orders by using the following script:



Each order is tracked by using one of the following statuses:
・ Fulfilled
・ Shipped
・ Ordered
・ Received
You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

正解:

Question No : 15
DRAG DROP
You use SQL Server 2016 Enterprise Edition. Your database contains a partitioned table named AuditData. AuditData is partitioned by year. Partition 1 contains data from the year 2010 and prior.
Management has decided to archive all AUDITDATA records from 2010 and prior. Management wants the records to be removed from the database entirely and provided to the backup team as a zipped text file. The data must no longer reside in the database. There is very little tolerance for performance degradation in your environment.
You need to remove all 2010 and prior data from the AuditData table by using the least amount of system resources possible.
Develop the solution by selecting and arranging the required SQL actions in the correct order.
You may not need all of the actions.



正解:


Explanation:
Note:
* Create a new partitioned table with the partition function you want, and then insert the data from the old table into the new table by using an INSERT INTO...SELECT FROM statement.
* SPLIT RANGE ( boundary_value )
Adds one partition to the partition function. boundary_value determines the range of the new partition, and must differ from the existing boundary ranges of the partition function. Based on boundary_value, the Database Engine splits one of the existing ranges into two. Of these two, the one where the new boundary_value resides is considered the new partition.
* BCP can be used to produce the zipped text file.
* Example:
Splitting a partition of a partitioned table or index into two partitions
The following example creates a partition function to partition a table or index into four partitions. ALTER PARTITION FUNCTION splits one of the partitions into two to create a total of five partitions.
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO
--Split the partition between boundary_values 100 and 1000
--to create two partitions between boundary_values 100 and 500
--and between boundary_values 500 and 1000.
ALTER PARTITION FUNCTION myRangePF1 ()
SPLIT RANGE (500);
References:
http://technet.microsoft.com/en-us/library/ms186307(v=sql.110).aspx
http://technet.microsoft.com/en-us/library/ms162802(v=sql.120).aspx

 / 12