Microsoft 70-765 Exam Questions Instant Access

Question 1

You administer a Microsoft SQL Server 2014 database named Orders.

Orders contains a table named OrderShip that is defined as follows:

A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.

Customers require a procedure that will return orders for all customers from a specified country.

You create a new procedure:

Performance on this procedure is slow.

You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.

Which Transact-SQL statement should you use?

ACREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL

BCREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCodeIS NOT NULL

CCREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)

DCREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL

Answer : B

Here creating statistics is relevant. The CREATE STATISTICS command creates query optimization statistics on one or more columns of a table, an indexed view, or an external table. For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transact-sql

Question 2

You have a database named DB1 that is 3 TB. obi contains a fact table that is 1.2 TB.

You load 200 GB of new data to the fact table from a line-of-business application.

Users of DB1 notice that reports render more slowly since you loaded the data.

What are two possible causes of the performance issue? Each correct answer presents a complete solution.

NOTE: Each correct selection is worth one point.

Atransaction log growth

Bpage corruption

Cout-of-date statistics

Dindex fragmentation

Answer : C, D

References:

https://www.sqlshack.com/gathering-sql-server-indexes-statistics-and-usage-information/

Question 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?

ASELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY COUNT(OrderAmount) DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1

BSELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK()OVER (PARTITION BY CustomerIDORDER BY COUNT(o.OrderAmount) ASC) AS RnkFROM Customer cINNER JOIN Orders oON c.CustomerID = o.CustomerIDGROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1

CSELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY OrderAmount DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1

DSELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,COUNT(OrderAmount) DESC) AS OrderAmountFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDORDER BY OrderAmount DESC

Answer : A

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 4

You have Microsoft SQL Server on a Microsoft Azure Virtual machine. The virtual machine has a database named DB1.

You need to identify the fragmentation percentage for the indexes of DB1.

Which dynamic management view should you query?

Asys.dm_db_index_operational_stats

Bsys.dm_db_index_physical_stats

Csys.dm_db_xtp_hash_index_stats

Dsys.dm_db_index_usage_stats

Answer : B

References:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-2017

Question 5

You are a database administrator for a Microsoft SQL Server 2014 database named AdventureWorks2012.

You create an Availability Group defined by the following schem

a. (Line numbers are included for reference only.)

You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:

Production transactions should be minimally affected. The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.

Which Transact-SQL statement should you insert at line 06?

AAVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,READ_ONLY_ROUTING_LIST = NONE)

BAVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')

CAVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')

DAVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = YES,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')

Answer : C

As production transaction should be MINIMALLY affected we should use asynchronous-commit mode.

Question 6

You plan to deploy an on-premises SQL Server 2014 database to Azure SQL Database. You have the following requirements:

Maximum database size of 500 GB

A point-in-time-restore of 35 days

Maximum database transaction units (DTUs) of 500

You need to choose the correct service tier and performance level. Which service tier should you choose?

AStandard S3

BPremium P4

CStandard SO

DBasic

Answer : B

You should choose Premium P4. The Premium tier is the highest Azure SQL Database tier offered. This tier is used for databases and application that require the highest level of performance and recovery. The P4 level supports a maximum of 500 DTUs, a maximum database size of 500 GB, and a point-in-time-restore to anypoint in the last 35 days.

Incorrect Answers:

A: You should not choose Standard S3. The Standard tier with a performance level of S3 only supports a maximum database size of 250 GB. a maximum of 100 DTUs, and a point-in-timerestore of 35 days.

C: You should not choose Standard SO. The Standard tier with a performance level of SO only supports a maximum database size of 250 GB. a maximum of 10 DTUs, and a point-in-timerestore of 35 days.

D: You should not choose Basic. The Basic service tier only supports a maximum database size of 2 GB, a maximum of 5 DTUs, and a point-in-time-restore of 7 days.

References:

https://azure.microsoft.com/en-us/pricing/details/sql-database/

Question 7

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs).

You load data to the database by using Azure Data Factory.

You need to reduce the amount of time it takes to load the data.

Solution: You move the database to a Premium database that has 125 eDTUs.

Does this meet the goal?

AYes

BNo

Answer : B

We need at least 400 eDTUs.

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits

Microsoft Provisioning SQL Databases 70-765 Exam Questions