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

Microsoft 70-765 問題練習

Provisioning SQL Databases 試験

最新更新時間: 2019/07/07,合計281問。

平成から令和へ:70-765 最新真題を買う時、日本語版と英語版両方を同時に獲得できます。

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

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

 / 19

Question No : 1
You administer a SQL Server 2014 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the following requirements are met:
Which Transact-SQL statement should you use?

正解:
Explanation:
Use REVOKE to remove the grant or deny of a permission.
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-warehouse-parallel-data-warehouse

Question No : 2
You use Microsoft SQL Server 2014 to develop a database application. You need to create an object that meets the following requirements:
Which object should you use?

正解:
Explanation:
Stored procedures accept input parameters and return multiple values in the form of output parameters to the calling program. They cannot be used in views.
References:https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine

Question No : 3
You are a database developer for an application hosted on a Microsoft SQL Server 2014 server.
The database contains two tables that have the following definitions:



Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?

正解:
Explanation:
Use descending (DESC) ordering.
To order by the number of orders we use ORDER BY COUNT(OrderAmount).
Finally a WHERE close is needed: WHERE o.Rnk = 1

Question No : 4
You use a Microsoft SQL Server 2014 database that contains two tables named SalesOrderHeader and SalesOrderDetail.
The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)



You write the following Transact-SQL query:



You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query.
What should you do?

正解:
Explanation:
New statistics would be useful.
The UPDATE STATISTICS command updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
References: http://msdn.microsoft.com/en-us/library/ms187348.aspx

Question No : 5
You use Microsoft SQL Server 2014 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?

正解:
Explanation:
A common way to define a computed column is by using a user-defined function (UDF) to encapsulate the calculation logic.
References:https://blogs.msdn.microsoft.com/sqlcat/2011/11/28/a-computed-column-defined-with-a-user-defined-function-might-impact-query-performance/

Question No : 6
You administer a Microsoft SQL Server 2014 database named ContosoDb.
Tables are defined as shown in the exhibit. (Click the Exhibit button.)



You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.



Which Transact-SQL query should you use?

正解:

Question No : 7
You administer a Microsoft SQL Server 2014 database named ContosoDb.
Tables are defined as shown in the exhibit. (Click the Exhibit button.)



You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.



Which Transact-SQL query should you use?

正解:
Explanation:

Question No : 8
You are a database developer of a Microsoft SQL Server 2014 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.



You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?

正解:
Explanation:

Question No : 9
You have been hired as a Database Consultant by ABC.com to design a SQL Server 2014 database solution.
You are tasked with designing a scale-out and high-availability SQL Server 2014 Online Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances.
Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2014 nodes. The data in both SQL Server nodes needs to be indexed.
What should you include in your solution?

正解:
Explanation:
Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes.
Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.
References:https://msdn.microsoft.com/en-us/library/ms151196(v=sql.110)

Question No : 10
You administer a Microsoft SQL Server 2014 database.
You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges.
You need to ensure that the job step can run using a different user account.
What should you use?

正解:
Explanation:
A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.
References:https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx

Question No : 11
You administer a Microsoft SQL Server 2014 database instance. You create a new user named UserA.
You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server agent jobs owned by UserA
To which role should you add UserA?
A. DatabaseMailUserRole
B. ServerGroupAdministratorGroup
C. SQLAgentUserRole
D. Securityadmin

正解: C
Explanation:
SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. Members can create local jobs.
References:https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles

Question No : 12
You administer a Microsoft SQL Server 2014 instance named SQL2012. You are in the process of migrating a database from a SQL Server 2008 instance named SQL2008 to the SQL2012 instance.
You have upgraded a database from the SQL2008 instance by using the side-by-side migration technique.
You need to migrate the SQL Server logins from the SQL2008 instance to the SQL2012 instance.
What should you do?

正解:
Explanation:
sp_grantlogin creates a SQL Server login.

Question No : 13
You plan to install a Microsoft SQL Server 2014 instance.
The instance will support a database that has the following requirements:
Store Excel workbooks on the file system.
Access the workbooks through Transact-SQL.
Include the workbooks in database backups.
During installation, you need to ensure that the requirements will be met.
Which feature should you use?

正解:
Explanation:
FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.
References:https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server

Question No : 14
You plan to install Microsoft SQL Server 2014 for a web hosting company.
The company plans to host multiple web sites, each supported by a SQL Server database.
You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.
Which edition should you choose?

正解:
Explanation:

Question No : 15
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to collect data for a long period of time to troubleshoot wait statistics when querying Contoso. You also need to ensure minimum impact to the server.
What should you create?

正解:
Explanation:
SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.
Extended Events is a light weight performance monitoring system that uses very few performance resources.
A SQL Server Extended Events session is created in the SQL Server process hosting the Extended Events engine.
References:https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events

 / 19