Prep and try our 070-457 valid and latest training questions & answers

You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected. You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer. What should you do?

• A. Add an INCLUDE clause to the index.
• B. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
• C. Add a LOOP hint to the query.
• D. Add a FORCESEEK hint to the query.
• E. Add a HASH hint to the query.
• F. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
• G. Include a SET STATISTICS PROFILE ON statement before you run the query.
• H. Add a columnstore index to cover the query.
• I. Include a SET FORCEPLAN ON statement before you run the query.
• J. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
• K. Add a FORCESCAN hint to the Attach query.
• L. Enable the optimize for ad hoc workloads option.
• M. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
• N. Cover the unique clustered index with a columnstore index.

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?

• A. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0
  GO
  GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser
• B. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
• C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
• D. GRANT DELETE ON Purchases.Suppliers TO ContosoUser

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())
  AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
• B. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = CONVERT(DATE, GETDATE())
• C. SELECT COUNT(*) FROM Purchases WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, GETDATE(), 112)
• D. SELECT COUNT(*) FROM Purchases WHERE PurchaseTime = GETDATE()

You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:

You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

• A. Implement page-level compression.
• B. Implement Unicode Compression.
• C. Convert all indexes to Column Store indexes.
• D. Implement row-level compression.

You administer a Microsoft SQL Server 2012 clustered instance that has two nodes named Node 1 and Node 2. Node 1 fails and the cluster fails over to Node 2. You need to replace Node 1 and add it to the cluster. Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Build List and Reorder: