Microsoft 70-461 Online Practice Questions and Exam Preparation

Microsoft 70-461 Online Questions & Answers

• Question 81:

  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 a large amount of memory is consumed by single-use dynamic queries. You need to reduce procedure cache usage from these statements without creating any additional indexes. What should you do?

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

  ---

  http://msdn.microsoft.com/en-us/library/cc645587.aspx

• Question 82:

  Which of the following is not a violation of the relational model?

  A. Using ordinal positions for columns
  B. Returning duplicate rows
  C. Not defining a key in a table
  D. Ensuring that all attributes in the result of a query have names
  D. Ensuring that all attributes in the result of a query have names

• Question 83:

  SIMULATION

  You have a business reporting database.

  Several power users generate reports from the database.

  You need to create an object in the database to meet the following requirements:

  1. Provide the power users with the ability to use simple queries.

  2. Prevent schema changes on objects related to the query.

  Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within code that has been provided as well as below it.

  

  

  Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

  Check the answer below

  1 Create View Customer_Orders WITH SCHEMABINDING AS SELECT CustomerName, 2 OrderDate FROM Customers INNER JOIN Orders on 3 Customers.CustomerID=Orders.CustomerID

  To Line 1, after WITH, add SCHEMABINDING SCHEMABINDING binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql

• Question 84:

  You use Microsoft SQL Server to develop a database application.

  You create a stored procedure named usp_calculategrowth. The stored procedure modifies rows and can result in several different exceptions.

  You need to ensure that when the stored procedure is executed, the following requirements are met:

  

  Which six Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

  ---

  Box 1: BEGIN TRY

  A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block. When an error condition is detected in a Transact-SQL statement that is inside a TRY block, control is passed to a CATCH block where the error can be processed.

  A TRY blockstarts with the BEGIN TRY statement and ends with the END TRY statement.

  Box 2: Exec usp_calculategrowth

  Box 3: END TRY

  Box 4: BEGIN CATCH

  A TRY block must be followed immediately by a CATCH block. A CATCH block starts with the BEGIN CATCH statement andends with the END CATCH statement.

  Box 5: EXEC xp_logevent - xp_logevent logs a user-defined message in the SQL Server log file and in the Windows Event Viewer. xp_logevent can be used to send an alert without sending a message to the client.

  Incorrect:

  Not RAISERROR: RAISERROR generates an error message and initiates error processing for the session.

  The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct. New applicationsshould use THROW instead.

  Box 6: END CATCH

  References:

  https://msdn.microsoft.com/en-us/library/ms186244.aspx

  https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

• Question 85:

  Which commands turn on an XML plan? (Choose all that apply.)

  A. SET EXECUTION_XML ON
  B. SET SHOWPLAN_XML ON
  C. SET XML PLAN ON
  D. SET STATISTICS XML ON
  B. SET SHOWPLAN_XML ON
  D. SET STATISTICS XML ON

  ---

  You use the SET SHOWPLAN_XML command to turn on the estimated XML plans. You use the SET STATISTICS XML command to turn on the actual XML plans.

• Question 86:

  You are maintaining a SQL Server database that uses the default settings. The database contains a table that is defined by the following Transact-SQL statement.

  

  You must write a query that returns the AddressLine1, AddressLine2, and Region fields separated by carriage returns. You must return an empty string for any null values.

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  A. Option A

  ---

  Char(13) is a carriage return. Use the IIF construct to return an empty string for NULL values of the Adressline2 column. IIF returns one of two values, depending on whether theBoolean expression evaluates to true or false in SQL Server. https://msdn.microsoft.com/en-us/library/hh213574.aspx

• Question 87:

  Which of the following is invalid? (Choose all that apply.)

  A. Referring to an attribute that you group by in the WHERE clause
  B. Referring to an expression in the GROUP BY clause; for example, GROUP BY YEAR(orderdate)
  C. In a grouped query, referring in the SELECT list to an attribute that is not part of
  D. the GROUP BY list and not within an aggregate function Referring to an alias defined in the SELECT clause in the HAVING clause
  C. In a grouped query, referring in the SELECT list to an attribute that is not part of
  D. the GROUP BY list and not within an aggregate function Referring to an alias defined in the SELECT clause in the HAVING clause

• Question 88:

  You need to create a query that calculates the total sales of each OrderID from a table named Sales.Details. The table contains two columns named OrderID and ExtendedAmount. The solution must meet the following requirements:

  1. Use one-part names to reference columns.

  2. Start the order of the results from OrderID.

  3. NOT depend on the default schema of a user.

  4. Use an alias of TotalSales for the calculated ExtendedAmount.

  5. Display only the OrderID column and the calculated TotalSales column. Provide the correct code in the answer area.

  Check the answer below

  SELECT OrderID, SUM(ExtendedAmount) AS TotalSales FROM Sales.Details GROUP BY OrderID ORDER BY OrderID

• Question 89:

  Can you have indexes on table variables?

  A. No
  B. Yes, by running the CREATE INDEX command
  C. Yes, indirectly by defining primary key and unique constraints
  D. Yes, by defining foreign keys
  C. Yes, indirectly by defining primary key and unique constraints

  ---

  You can get indexes indirectly by defining primary key and unique constraints.

• Question 90:

  Which of the following cases is suitable for using table variables? (Choose all that apply.)

  A. When the tables are very small and the plan is trivial
  B. When the tables are very small and the plan is nontrivial
  C. When the tables are large and the plan is trivial
  D. When the tables are large and the plan is nontrivial
  A. When the tables are very small and the plan is trivial
  B. When the tables are very small and the plan is nontrivial
  C. When the tables are large and the plan is trivial