Online Access Free 70-457 Practice Test

You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named AccountNumber of the nvarchar data type. You use a third table named Transactions that has columns named TransactionId, AccountNumber, Amount, and TransactionDate. You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or LoanAccounts are inserted. Which Transact-SQL statement should you use?

• A.CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN
  INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END
• B.CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN
  IF EXISTS (
  SELECT AccountNumber FROM inserted EXCEPT
  (SELECT AccountNumber FROM LoanAccounts
  UNION SELECT AccountNumber FROM SavingAccounts))
  BEGIN
  ROLLBACK TRAN
  END
  END
• C.CREATE TRIGGER TrgValidateAccountNumber ON Transactions INSTEAD OF INSERT AS BEGIN
  IF EXISTS (
  SELECT AccountNumber FROM inserted EXCEPT
  (SELECT AccountNumber FROM LoanAccounts
  UNION SELECT AccountNumber FROM SavingAccounts))
  BEGIN
  ROLLBACK TRAN
  END
  END
• D.CREATE TRIGGER TrgValidateAccountNumber ON Transactions FOR INSERT AS BEGIN
  INSERT INTO Transactions SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN (SELECT AccountNumber FROM LoanAccounts UNION SELECT AccountNumber FROM SavingAccounts)) END

You are developing a database application by using Microsoft SQL Server 2012. An application that uses a database begins to run slowly. You discover that during reads, the transaction experiences blocking from concurrent updates. You need to ensure that throughout the transaction the data maintains the original version. What should you do?

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

You create the following stored procedure. (Line numbers are included for reference only.)

You need to ensure that the stored procedure performs the following tasks: If a record exists, update the record. If no record exists, insert a new record.
Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:

Correct Answer:

Explanation:
--Burgos - NO
Reference: http://technet.microsoft.com/en-us/library/bb510625.aspx

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012. You
attempt to add the [Corpnet\User1] login to the database. However, you receive the following error
message:
"User already exists in current database."
You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the
original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?

• A.ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];
• B.DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];
• C.ALTER SERVER ROLE [sysadmin] ADD MEMBER [Corpnet\User1];
• D.ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];

You are a database administrator of a Microsoft SQL Server 2012 environment. The environment contains two servers named SQLServer01 and SQLServer02. The database Contoso exists on SQLServer01. You plan to mirror the Contoso database between SQLServer01 and SQLServer02 by using database mirroring. You need to prepare the Contoso database for database mirroring. 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:

Correct Answer:

Explanation:
--Burgos - YES