2018 May New Microsoft 70-765 Exam Dumps with PDF and VCE Just Updated Today! Following are some new 70-765 Real Exam Questions:
1.|2018 Latest 70-765 Exam Dumps (PDF & VCE) 155Q Download:
https://www.braindump2go.com/70-765.html
2.|2018 Latest 70-765 Exam Questions & Answers Download:
https://drive.google.com/drive/folders/0B75b5xYLjSSNTnR6dFR2U3A5cFk?usp=sharing
QUESTION 108
You administer a Microsoft SQL Server 2012 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?
A. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers-CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW
B. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS
C. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO
D. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTS
E. SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= FOR XML AUTO
F. SELECT Name, Country, Crderld, OrderDate, Amount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS
G. SELECT Name AS `@Name’, Country AS `@Country’, OrderId, OrderDate, Amount FROM Orders
INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML PATH (`Customers’)
H. SELECT Name AS `Customers/Name’, Country
AS `Customers/Country’, OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= 1
FOR XML PATH (`Customers’
Answer: G
QUESTION 109
You use Microsoft SQL Server 2012 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?
A. Reference a user-defined function within the computed column.
B. Create a BEFORE trigger that maintains the state of the computed column.
C. Add a default constraint to the computed column that implements hard-coded values.
D. Add a default constraint to the computed column that implements hard-coded CASE statements.
Answer: A
Explanation:
A common way to define a computed column is by using a user-defined function (UDF) to encapsulate the calculation logic.
https://blogs.msdn.microsoft.com/sqlcat/2011/11/28/a-computed-column-defined-with-a-user-defined-function-might-impact-query-performance/
QUESTION 110
You use a Microsoft SQL Server 2012 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?
A. Use a FORCESCAN hint in the query.
B. Add a clustered index on SalesOrderId in SalesOrderHeader.
C. Use a FORCESEEK hint in the query.
D. Update statistics on SalesOrderId on both tables.
Answer: D
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.
http://msdn.microsoft.com/en-us/library/ms187348.aspx
QUESTION 111
You are a database developer for an application hosted on a Microsoft SQL Server 2012 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?
A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(OrderAmount) DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM (SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK() OVER (PARTITION BY CustomerID
ORDER BY COUNT(o.OrderAmount) ASC) AS Rnk
FROM Customer c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
(SELECT CustomerID, ShippingCountry,
RANK() OVER (PARTITION BY CustomerID
ORDER BY OrderAmount DESC) AS Rnk
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
WHERE o.Rnk = 1
D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer c INNER JOIN
(SELECT CustomerID, ShippingCountry,
COUNT(OrderAmount) DESC) AS OrderAmount
FROM Orders
GROUP BY CustomerID, ShippingCountry) AS o
ON c.CustomerID = o.CustomerID
ORDER BY OrderAmount DESC
Answer: A
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
Incorrect Answers:
B: The ascending (ASC) sorting would produce the country from which each customer has placed the least orders.
C: We are interested in the number of the orders, not the amount of the orders.
We should use ORDER BY COUNT(OrderAmount), not ORDER BY OrderAmount.
D: We are only interested in one single post, only the country from which each customer has placed the most orders. Need to use a WHERE statement (here Where o.Rnk =1 ).
QUESTION 112
You use Microsoft SQL Server 2012 to develop a database application.
You need to create an object that meets the following requirements:
– Takes an input variable
– Returns a table of values
– Cannot be referenced within a view
Which object should you use?
A. Scalar-valued function
B. Inline function
C. User-defined data type
D. Stored procedure
Answer: D
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.
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine
QUESTION 113
You administer a SQL Server 2012 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:
– The Sales role does not have the Select permission on the Customers schema.
– UserA has the Select permission on the Regions table.
Which Transact-SQL statement should you use?
A. REVOKE SELECT ON Schema::Customers FROM UserA
B. DENY SELECT ON Object::Regions FROM UserA
C. EXEC sp_addrolemember ‘Sales’, ‘UserA’
D. DENY SELECT ON Object::Regions FROM Sales
E. REVOKE SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema::Customers FROM Sales
G. DENY SELECT ON Schema::Customers FROM UserA
H. EXEC sp_droprolemember ‘Sales’, ‘UserA’
I. REVOKE SELECT ON Object::Regions FROM Sales
J. REVOKE SELECT ON Schema::Customers FROM Sales
Answer: J
Explanation:
Use REVOKE to remove the grant or deny of a permission.
https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure- sql-data-warehouse-parallel-data-warehouse
QUESTION 114
You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical.
You write the following Transact-SQL query:
INSERT INTO OrdersHistorical
SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement.
Which table hint should you use?
A. HOLDLOCK
B. ROWLOCK
C. XLOCK
D. UPDLOCK
E. TABLOCK
Answer: E
Explanation:
When importing data into a heap by using the INSERT INTO SELECT <columns> FROM statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table.
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table
QUESTION 115
Your database contains a table named Purchases.
The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made.
There is a non- clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day.
You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?
A. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = GETDATE()
C. SELECT COUNT(*)
FROM Purchases
WHERE CONVERT(VARCHAR, PurchaseTime, 112) =
CONVERT(VARCHAR, GETDATE(), 112)
D. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime >= CONVERT(DATE, GETDATE())
AND PurchaseTime <DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
Answer: D
Explanation:
To compare a time with date we must use >= and > operators, and not the = operator.
Incorrect Answers:
A: The in WHERE clause there is an incorrect comparison between time and a date, as equality (=) is used.
http://technet.microsoft.com/en-us/library/ms181034.aspx
QUESTION 116
You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You suspect that the current SQL Server indexes cause queries to execute slowly.
You need to identify which indexes must be created to reduce the query execution time.
Which three dynamic management views should you use? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. sys.dm_db_index_physical_stats
B. sys.dm_db_missing_index_group_stats
C. sys.indexes
D. sys.dm_db_index_usage_stats
E. sys.dm_db_missing_index_groups
F. sys.dm_db_index_operational_stats
G. sys.dm_db_missing_index_details
H. sys.sysindexkeys
Answer: BEG
Explanation:
The missing indexes feature consists of the following components:
A set of dynamic management objects that can be queried to return information about missing indexes.
The MissingIndexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing.
Dynamic Management Objects
After running a typical workload on SQL Server, you can retrieve information about missing indexes by querying the dynamic management objects listed in the following table. These dynamic management objects are stored in the master database.
sys.dm_db_missing_index_group_stats
Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
sys.dm_db_missing_index_groups
Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.
sys.dm_db_missing_index_details
Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
sys.dm_db_missing_index_columns
Returns information about the database table columns that are missing an index.
References: https://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx
QUESTION 117
You use Microsoft Azure Resource Manager to deploy two new Microsoft SQL Server instances in an Azure virtual machine (VM). VM has 28 gigabytes (GB) of memory. The instances are named Instance1 and Instance2, respectively.
The various databases on the instances have the following characteristics:
You run the following Transact-SQL statements:
You need to configure each SQL Server instance to correctly allocate memory.
What should you do?
A. On Instance1, run the following Transact-SQL code:
On Instance2, run the following Transact-SQL code:
B. On Instance1, run the following Transact-SQL code:
On Instance2, run the following Transact-SQL code:
C. On Instance1, run the following Transact-SQL code:
On Instance2, run the following Transact-SQL code:
D. On Instance1, run the following Transact-SQL code:
On Instance2, run the following Transact-SQL code:
Answer: D
Explanation:
Incorrect Answers:
A, C: The awefeature will be removed in newer version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.
B: Automatic soft-NUMA is disabled by default, and this setting is not relevant here.
References: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-configuration-options-sql-server
QUESTION 118
You manage a Microsoft SQL Server environment in a Microsoft Azure virtual machine.
You must enable Always Encrypted for columns in a database.
You need to configure the key store provider.
What should you do?
A. Use the Randomized encryption type
B. Modify the connection string for applications.
C. Auto-generate a column master key.
D. Use the Azure Key Vault.
Answer: D
Explanation:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-and-store-column-master-keys-always-encrypted
!!!RECOMMEND!!!
1.|2018 Latest 70-765 Exam Dumps (PDF & VCE) 155Q Download:
https://www.braindump2go.com/70-765.html
2.|2018 Latest 70-765 Study Guide Video: