Free Microsoft 70-457 Exam Dumps Questions & Answers

You develop a database for a travel application. You need to design tables and other database objects. You create the Airline_Schedules table. You need to store the departure and arrival dates and times of flights along with time zone information. What should you do?

• A.  Add a FORCESEEK hint to the query.
• B.  Add a FORCESCAN hint to the Attach query.
• C.  Include a SET FORCEPLAN ON statement before you run the query.
• D.  Add a columnstore index to cover the query.
• E.  Enable the Optimize for ad hoc workloads option.
• F.  Add a HASH hint to the query.
• G.  Add a LOOP hint to the query.
• H.  Cover the unique clustered index with a columnstore index.
• I.  Add an INCLUDE clause to the index.
• J.  Include a SET STATISTICS PROFILE ON statement before you run the query.

You are a database developer of a Microsoft SQL Server 2012 database. The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table. Which Transact-SQL statement should you use?

• A.  ALTER TABLE Orders ADD CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)
• B.  ALTER TABLE Customer ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY (CustomerID) REFERENCES
  Orders (CustomerID)
• C.  ALTER TABLE Orders ADD CONSTRAINT PK_Orders_CustomerID PRIMARY KEY (CustomerID)
• D.  ALTER TABLE Orders ADD CONSTRAINT CK_Orders_CustomerID CHECK (CustomerID IN (SELECT CustomerId FROM Customer))
• E.  ALTER TABLE Customer ADD OrderID INT NOT NULL;
  ALTER TABLE Customer ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (OrderID) REFERENCES Orders (OrderID);

You administer a Microsoft SQL Server 2012 instance that contains a database of confidential data. You need to encrypt the database files at the page level. You also need to encrypt the transaction log files. 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:

Correct Answer:

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/bb510663.aspx Reference: http://msdn.microsoft.com/en-us/library/bb934049.aspx

You administer a Microsoft SQL Server database that supports a banking transaction management application. You need to retrieve a list of account holders who live in cities that do not have a branch location. Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)

• A.  SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
• B.  SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)
• C.  SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
• D.  SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)

You administer a Microsoft SQL Server 2012 server. One of the databases on the server supports a highly active OLTP application. Users report abnormally long wait times when they submit data into the application. You need to identify which queries are taking longer than 1 second to run over an extended period of time. What should you do?

• A.  Use sp_configure to set a value for blocked process threshold. Create an extended event session.
• B.  Use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000.
• C.  Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step.
• D.  Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
• E.  Run the sp_who command from a query window.