Online Access Free 070-462 Exam Questions

This question is part of a series of questions that use the same set of answers choices. An answer choice may be correct for more than one question in the series.
You administer a Microsoft SQL Server database server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.
The recovery model and backup schedule are configured as shown in the following table:

One of the hard disk drives that stores the transactional database fails at 23:32 hours. Attempts to create a tail log backup are unsuccessful.
You need to ensure that the transactional database is restored. You also need to ensure that data loss is minimal.
What should you do?

• A.Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
• B.Perform a partial restore.
• C.Restore the latest full backup. Then, restore the latest differential backup.
• D.Restore the latest full backup.
• E.Perform a page restore.
• F.Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
• G.Perform a point-in-time restore.
• H.Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.

Which of the following SQL Server 2012 indexes stores data in a column-wise fashion instead of the traditional row-wise method?

• A.Function-based Index
• B.Columnstore Index
• C.Bitmap Index
• D.Clustered Index

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 'Customers/Name', Country AS 'Customers/Country', OrderId,OrderDate, AmountFROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML PATH ('Customers')
• B.SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, AmountFROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML PATH ('Customers')
• C.SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO
• D.SELECT Name, Country, Orderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS
• E.SELECT Name, Country, Crderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId =FOR XML AUTO, ELEMENTS
• F.SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN Customers ON Orders.CustomerId = Customers-CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW
• G.SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS
• H.SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS

You have a SQL Server database that contains all of the sales data for your company. Users of the database report the following issues:
* Connections to the database fail when the users first attempt to connect in the morning.
* Inserting new records in the database takes longer than expected.
You need to resolve the issues.
Which two database properties should you modify? To answer, select the appropriate properties in the answer area.

Correct Answer:

Explanation

When Auto Close is enabled, the database is automatically shut down when the last user connection is ended.
When a new connection is made to the database, the database automatically restarts. Auto Close should be disabled on production databases.
When Auto Shrink is enabled, SQL Server will check the database every 30 minutes, and shrink it if the database has too much unused space.
* The shrinking process is resource intensive and can hurt server performance.
* The shrinking process causes severe index fragmentation, which hurts performance.
References:
https://www.red-gate.com/simple-talk/sql/database-administration/database-properties-health-check/

Which of the following statements about the tempdb system database is FALSE?

• A.It is created every time the SQL Server instance is restarted.
• B.It is a globally available resource for all connected users.
• C.It must be sized in accordance with the smallest user-database on the machine.
• D.It can hold explicitly or implicitly created database objects.