Mobile Menu

[Free] 2019(Nov) EnsurePass Microsoft 70-765 Dumps with VCE and PDF 1-10

by admin, November 7, 2019

Get Full Version of the Exam
http://www.EnsurePass.com/70-765.html

Question No.1

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

image

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.

image

Which Transact-SQL query should you use?

  1. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers-CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW

  2. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS

  3. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO

  4. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId-Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML AUTO, ELEMENTS

  5. SELECT Name, Country, Orderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML AUTO, ELEMENTS

  6. SELECT Name, Country, Crderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS

  7. SELECT Name AS `@Name#39;, CountryAS `@Country#39;, OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML PATH (`Customers#39;)

  8. SELECT Name AS `Customers/Name#39;, CountryAS `Customers/Country#39;, OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML PATH (`Customers#39;

Correct Answer: E

Question No.2

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 UserA is disallowed to select from any of the tables in the Customers schema.

Which Transact-SQL statement should you use?

  1. REVOKE SELECT ON Schema::Customers FROM UserA

  2. DENY SELECT ON Object::Regions FROM UserA

  3. EXEC sp_addrolemember #39;Sales#39;, #39;UserA#39;

  4. DENY SELECT ON Object::Regions FROM Sales

  5. REVOKE SELECT ON Object::Regions FROM UserA

  6. DENY SELECT ON Schema::Customers FROM Sales

  7. DENY SELECT ON Schema::Customers FROM UserA

  8. EXEC sp_droprolemember #39;Sales#39;, #39;UserA#39;

  9. REVOKE SELECT ON Object::Regions FROM Sales

  10. REVOKE SELECT ON Schema::Customers FROM Sales

Correct Answer: G

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

Question No.3

You administer a Microsoft SQL Server 2014 server. The MSSQLSERVER service uses a domain account named CONTOSO\SQLService.

You plan to configure Instant File Initialization.

You need to ensure that Data File Autogrow operations use Instant File Initialization. What should you do? Choose all that apply.

  1. Restart the SQL Server Agent Service.

  2. Disable snapshot isolation.

  3. Restart the SQL Server Service.

  4. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security

    policy.

  5. Add the CONTOSO\SQLService account to the Server Operators fixed server role.

  6. Enable snapshot isolation.

Correct Answer: CD

Explanation:

How To Enable Instant File Initialization

References:

http://msdn.microsoft.com/en-us/library/ms175935.aspx

Question No.4

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?

  1. Excel Services

  2. FILESTREAM

  3. SQL Server Integration Services (SSIS)

  4. OpenXML

Correct Answer: B

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.5

You develop a Microsoft SQL Server 2014 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?

  1. HOLDLOCK

  2. ROWLOCK

  3. XLOCK

  4. UPDLOCK

  5. TABLOCK

Correct Answer: E

Explanation:

When importing data into a heap by using the INSERT INTO SELECT lt;columnsgt; FROM statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table.

References:

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

Question No.6

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?

  1. Express Edition with Tools

  2. Standard Edition

  3. Web Edition

  4. Express Edition with Advanced Services

Correct Answer: B

Question No.7

You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2014 Standard Edition. The server hosts a database named Orders.

Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.

You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.

Which Transact-SQL command should you use?

  1. ALTER INDEX IX_OrderDateON CustomerOrder DISABLE

  2. ALTER INDEX IX_OrderDateON CustomerOrder ENABLE

  3. ALTER INDEX IX_OrderDateON CustomerOrder REORGANIZE

  4. ALTER INDEX IX OrderDateON CustomerOrder REBUILD

Correct Answer: C

Explanation:

Reorganize: This option is more lightweight compared to rebuild. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it#39;s able

to just stop where it is (it doesn#39;t have a giant operation to rollback).

References:

https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

Question No.8

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?

  1. Back up the master database on the SQL2008 instance. Restore the master database on the SQL2012 instance

  2. Use the Transfer Logins task in a Microsoft SQL Server Integrated Services package

  3. Use sp_grantlogin

  4. Use xp_logininfo.

Correct Answer: C

Explanation:

sp_grantlogin creates a SQL Server login.

Question No.9

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 Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.

Which Transact-SQL statement should you use?

  1. REVOKE SELECT ON Schema::Customers FROM UserA

  2. DENY SELECT ON Object::Regions FROM UserA

  3. EXEC sp_addrolemember #39;Sales#39;, #39;UserA#39;

  4. DENY SELECT ON Object::Regions FROM Sales

  5. REVOKE SELECT ON Object::Regions FROM UserA

  6. DENY SELECT ON Schema::Customers FROM Sales

  7. DENY SELECT ON Schema::Customers FROM UserA

  8. EXEC sp_droprolemember #39;Sales#39;, #39;UserA#39;

  9. REVOKE SELECT ON Object::Regions FROM Sales

  10. REVOKE SELECT ON Schema::Customers FROM Sales

Correct Answer: F

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

Question No.10

You administer a Microsoft SQL Server 2014 database. The database is currently configured to log ship to a secondary server.

You are preparing to cut over to the secondary server by stopping log-shipping and bringing the secondary database online. You want to perform a tail-log backup.

You need to leave the primary database in a restoring state. Which option of the BACKUP LOG command should you use?

  1. NO_TRUNCATE

  2. NORECOVERY

  3. STANDBY

  4. FORMAT Correct Answer: B Explanation:

It is recommended that you take a tail-log backup in the following scenarios:

If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.

Note:

A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql- server

Get Full Version of the Exam
70-765 Dumps
70-765 VCE and PDF

Categories