NEW QUESTION NO: 70
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: B,E,G
Explanation/Reference:
Expalantion:
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

NEW QUESTION NO: 71
You have a database named DB1 that uses simple recovery mode.
Full backups of DB1 are taken daily and DB1 is checked for corruption before each backup.
There was no corruption when the last backup was complete.
You run the sys.columns catalog view and discover corrupt pages.
You need to recover the database. The solution must minimize data loss.
What should you do?
A. Run RESTORE DATABASE WITH RECOVERY.
B. Run RESTORE DATABASE WITH PAGE.
C. Run DBCC CHECKDBand specify the REPAIR_ALLOW_DATA_LOSSparameter.
D. Run DBCC CHECKDBand specify the REPAIT_REBUILDparameter.
Answer: B
Explanation/Reference:
Expalantion:
A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation.
RESTORE DATABASE WITH PAGE
Restores individual pages. Page restore is available only under the full and bulk-logged recovery models.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

NEW QUESTION NO: 72
HOTSPOT
You plan to deploy a Microsoft SQL Server database that will use FILESTREAM. The database will store 4 TB of FILESTREAM data on a single Windows partition.
You need to configure the hard disk that will support the FILESTREAM data. The solution must provide the fastest read and write access to the data.
How should you configure the disk? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer: 

Explanation/Reference:
Explanation:
File System: NTFS
8.3 filename support: Disabled
Indexing: Disabled
NTFS is required.
Disable generation of 8.3 names on all NTFS volumes used for FILESTREAM data storage.
Check that search indexing is not enabled on FILESTREAM volumes, under the Volume Properties window, unchecking the "Allow files on this drive to have contents indexed in addition to file properties" box.
References: https://blogs.msdn.microsoft.com/blogdoezequiel/2011/02/11/best-practices-on-filestream- implementations/

NEW QUESTION NO: 73
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 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')
B. SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= FOR XML AUTO
C. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId= 1
FOR XML AUTO, ELEMENTS
D. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML AUTO
E. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId = Customers-CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW
F. SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers
ON Orders.CustomerId = Customers.CustomerId
WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS
G. SELECT Name, Country, Crderld, OrderDate, Amount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerId= Customers.CustomerId
WHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS
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: A

NEW QUESTION NO: 74
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 UserA is disallowed to select from any of the tables in the Customers schema.
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: G
Explanation/Reference:
Explanation:
Use SQL Data Warehouse or Parallel Data WarehouseGRANT and DENY statements to grant or deny a permission (such as UPDATE) on a securable (such as a database, table, view, etc.) to a security principal (a login, a database user, or a database role).
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure- sql-data-warehouse-parallel-data-warehouse

NEW QUESTION NO: 75
You have a database named DB1.
You discover that DB1 is corrupt.
You run DBCC CHECKDB and receive an error message within a few seconds. No pages are listed in the error message.
You need to repair the database corruption as quickly as possible. The solution must minimize data loss.
What should you do?
A. Run DBCC CHECKDB ('db1', REPAIR_ALLOW_DATA_LOSS).
B. Run DBCC CHECKDB ('db1', REPAIR_FAST).
C. Delete the transaction logs and restart the Microsoft SQL Server instance.
D. Run DBCC CHECKDB ('db1', REPAIR_REBUILD).
E. Restore the database from a backup.
Answer: C
Explanation/Reference:
Expalantion:
REPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.
Incorrect Answers:
A: The REPAIR_ALLOW_DATA_LOSS option is a supported feature but it may not always be the best option for bringing a database to a physically consistent state. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup.
B: REPAIR_FAST
Maintains syntax for backward compatibility only. No repair actions are performed.
E: Restoring from backup is not the fastest solution.
References: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb- transact-sql

NEW QUESTION NO: 76
You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance.
The application is unable to connect to the database on the named instance.
You need to ensure that the application can connect to the named instance.
What should you do?
A. Configure the application as data-tiered.
B. Open port 1433 on the Windows firewall on the server.
C. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group.
D. Start the SQL Server Browser Service.
Answer: D
Explanation/Reference:
Explanation:
The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:
Browsing a list of available servers

Connecting to the correct server instance

Connecting to dedicated administrator connection (DAC) endpoints

References: https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx

NEW QUESTION NO: 77
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/Reference:
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 ).

NEW QUESTION NO: 78
DRAG DROP
Background
You manage a Microsoft SQL Server environment that includes the following databases: DB1, DB2, Reporting.
The environment also includes SQL Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). All SSRS and SSAS servers use named instances. You configure a firewall rule for SSAS.
Databases
Database Name:
DB1
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are inserted into DB1 or updated each second. Inserts are made by many different external applications that your company's developers do not control. You observe that transaction log write latency is a bottleneck in performance. Because of the transient nature of all the data in this database, the business can tolerate some data loss in the event of a server shutdown.
Database Name:
DB2
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are updated or inserted per second. You observe that the WRITELOG wait type is the highest aggregated wait type. Most writes must have no tolerance for data loss in the event of a server shutdown. The business has identified certain write queries where data loss is tolerable in the event of a server shutdown.
Database Name:
Reporting
Notes:
You create a SQL Server-authenticated login named BIAppUser on the SQL Server instance to support users of the Reporting database. The BIAppUser login is not a member of the sysadmin role.
You plan to configure performance-monitoring alerts for this instance by using SQL Agent Alerts.
You create a login named BIAppUser. The login must be able to access the Reporting database.
You need to grant access to the BIAppUser login in the database.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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.
Select and Place:

Answer: 

Explanation/Reference:
Explanation:
Box 1: Reporting
The user is to be created in the Reporting database.
Box 2: CREATE USER
Box 3: FOR LOGIN [BIAppUser]
Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin;
References: https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

NEW QUESTION NO: 79
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 stated goals.
Your company plans to use Microsoft Azure Resource Manager templates for all future deployments of SQL Server on Azure virtual machines.
You need to create the templates.
Solution: You use Visual Studio to create a XAML template that defines the deployment and configuration settings for the SQL Server environment.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation/Reference:
Azure Resource Manager template consists of JSON, not XAML, and expressions that you can use to construct values for your deployment.
A good JSON editor can simplify the task of creating templates.
Note: In its simplest structure, an Azure Resource Manager template contains the following elements:
{
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "",
"parameters": { },
"variables": { },
"resources": [ ],
"outputs": { }
}
References: https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-authoring- templates

NEW QUESTION NO: 80
You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!'
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate';
BACKUP CERTIFICATE TDE_Certificate TO FILE = ''d:\TDE_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key',
ENCRYPTION BY PASSWORD = 'MyPassword1!');
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?
A. ALTER DATABASE Master SET ENCRYPTION OFF;
B. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key', DECRYPTION BY PASSWORD = 'MyPassword1!');
C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'; USE Orders; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer';
Answer: B
Explanation/Reference:
Explanation:
The CREATE CERTIFICATE command adds a certificate to a database in SQL Server.
Creating a certificate from a file
The following example creates a certificate in the database, loading the key pair from files.
Code
Copy
USE AdventureWorks2012;
CREATE CERTIFICATE Shipping11
FROM FILE = 'c:\Shipping\Certs\Shipping11.cer'
WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk',
DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');
GO
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql