70-457 무료 덤프문제 온라인 액세스

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. Your investigation shows the root cause is a query against a read-only table that has a clustered index. The query returns the following six columns: * One column in its WHERE clause contained in a non-clustered index * Four additional columns * One COUNT (*) column based on a grouping of the four additional columns.
You need to optimize the statement. What should you do?

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

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.CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'; USE Orders; CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
• B.ALTER DATABASE Master SET ENCRYPTION OFF;
• C.CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key', DECRYPTION BY PASSWORD = 'MyPassword1!');
• D.CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer';

You administer a Microsoft SQL Server 2012 database. Your database is experiencing deadlock issues. You need to be able to monitor deadlocks. Which three 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:

정답:

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server. What should you create?

• A.a Server Audit Specification
• B.an Extended Event session
• C.a Policy
• D.a SQL Profiler Trace
• E.a Database Audit Specification
• F.an Alert
• G.a Resource Pool

You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo. ModifyData meets the following requirements:
Does not return an error
Closes all opened transactions
Which Transact-SQL statement should you use?

• A.BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@ERROR != 0
  ROLLBACK TRANSACTION;
  THROW;
  END CATCH
• B.BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@ERROR != 0
  ROLLBACK TRANSACTION;
  END CATCH
• C.BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@ TRANCOUNT = 0
  ROLLBACK TRANSACTION;
  END CATCH
• D.BEGIN TRANSACTION
  BEGIN TRY
  EXEC dbo.ModifyData
  COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
  IF @@TRANCOUNT = 0
  ROLLBACK TRANSACTION;
  THROW;
  END CATCH