Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 181:

  You are a database developer located in Seattle. You have a client in Melbourne, which is in a different time zone from Seattle. You have been using the datetimeoffset data type and storing data by using the Seattle offset. You need to display the dates in the Melbourne offset.

  Which function should you use?

  A. CONVERT
  B. DATEADD
  C. SWITCHOFFSET
  D. TODATETIMEOFFSET
  C. SWITCHOFFSET

• Question 182:

  Your company uses an application that passes XML to the database server by using stored procedures.

  The database server has a large number of XML handles that are currently active. You determine that the XML is not being flushed from SQL Server memory. You need to identify the system stored procedure to flush the XML from memory.

  Which Transact-SQL statement should you use?

  A. DBCC DROPCLEANBUFFERS
  B. DBCC FREEPROCACHE
  C. sp_xml_preparedocument
  D. sp_xml__removedocument
  D. sp_xml__removedocument

• Question 183:

  Your database contains sales information for millions of orders.

  You need to identify the orders with the highest average unit price and an order total greater than 10,000. The list should contain no more than 20 orders.

  Which query should you use?

  A. SELECT TOP (20)
  B. SalesOrderId,
  C. OrderDate,
  D. Total, SUM(od.QTY * od.UnitPrice) / SUM(od.Qty) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o JOIN SALES.SalesOrderDetail od ON o.SalesOrderId = od.SalesOrderId WHERE o.Total> 10000 GROUP BY o.SalesOrderId, o.OrderDate, o.Total ORDER BY AvgUnitPrice;
  E. SELECT TOP (20)
  F. SalesOrderId,
  G. OrderDate,
  H. Total, (SELECT SUM(od.Qty * od.UnitPrice) / SUM(od.QTY) FROM Sales.SalesOrderDetail od WHERE o.SalesOrderId = od.SalesOrderId) AS [AvgUnitPrice] FROM Sales.SalesOrderHeader o WHERE o.Total> 10000 ORDER BY AvgUnitPrice DESC;
  I. SELECT TOP (20)
  J. SalesOrderId,
  B. SalesOrderId,

• Question 184:

  You are troubleshooting query performance on SQL Server 2008. You are tasked to create an estimated execution plan by using Transact-SQL. You should be able to view the plan graphically in SQL Server Management Studio. You need to ensure that the execution plan can be saved as a .sqlplan file.

  Which Transact- SQL setting should you use?

  A. SET SHOWPLAN_ALL ON;
  B. SET SHOWPLAN_XML ON;
  C. SET STATISTICS XML ON;
  D. SET STATISTICS PROFILE ON;
  B. SET SHOWPLAN_XML ON;

  ---

  SET SHOWPLAN_XML ON

  This statement causes SQL Server not to execute Transact-SQL statements. Instead, Microsoft SQL Server returns execution plan information about how the statements are going to be executed in a well-formed XML document.

  SET SHOWPLAN_TEXT ON

  After this SET statement is executed, SQL Server returns the execution plan information for each query in text. The Transact-SQL statements or batches are not executed.

  SET SHOWPLAN_ALL ON

  This statement is similar to SET SHOWPLAN_TEXT, except that the output is in a format more verbose than that of SHOWPLAN_TEXT.

  SET STATISTICS XML ON

  Returns execution information for each statement after the statement executes in addition to the regular result set the statement returns. The output is a set of well-formed XML documents. SET STATISTICS XML ON produces an XML output

  document for each statement that executes. The difference between SET SHOWPLAN_XML ON and SET STATISTICS XML ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS XML ON

  output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.

  SET STATISTICS PROFILE ON

  Returns the execution information for each statement after the statement executes in addition to the regular result set the statement returns. Both SET statement options provide output in text. The difference between SET SHOWPLAN_ALL

  ON and SET STATISTICS PROFILE ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS PROFILE ON output also includes information about the actual number of rows processed by various

  operators and the actual number of executes of the operators.

• Question 185:

  You administer a Microsoft SQL Server 2008 instance that has two databases. The first database named AdventureWorks contains a table named Sales.SalesOrders. The Sales.SalesOrders table has the following definition:

  

  The second database named AdventureWorksDW contains a table named dbo.SalesOrderSummary. The dbo.SalesOrderSummary table has the following definition:

  

  You plan to migrate sales data for the year 2011 from the SalesOrderDetail table into the SalesOrderSummary table.

  You need to ensure that the following requirements are met:

  All data is removed from the SalesOrderSummary table before migrating data. A subset of data is migrated from the SalesOrderDetail table in the AdventureWorks

  database to the SalesOrderSummary table in the AdventureWorksDW database.

  Migrated data summarizes order quantity in one row per product for the year 2011.

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

  Select and Place:

  

  

• Question 186:

  You need to capture and record a workload for analysis by the Database Engine Tuning Advisor (DTA).

  Which tool should you use?

  A. DTA utility
  B. Activity Monitor
  C. SQL Server Profiler
  D. Performance Monitor
  C. SQL Server Profiler

• Question 187:

  You administer a Microsoft SQL Server 2008 database that contains a stored procedure named dbo.SalesOrderDetails. The stored procedure has following definition:

  

  Parameter values passed to the stored procedure largely vary. You discover that the stored procedure executes quickly for some parameters but slowly for other parameters. You need to ensure that the query plan generated is optimized to provide the most consistent execution times for any set of parameters passed to the stored procedure. Which query hint should you use?

  A. OPTION (FAST 25)
  B. OPTION (ROBUST PLAN)
  C. OPTION (KEEP PLAN)
  D. OPTION (OPTIMIZE FOR UNKNOWN)
  C. OPTION (KEEP PLAN)

• Question 188:

  You have a table named Customers that has an XML column named CustomerData. There are currently no indexes on the table. You use the following WHERE clause in a query:

  WHERE CustomerData.exist ('/CustomerDemographic/@Age[.>="21"]') = 1

  You need to create indexes for the query.

  Which Transact-SQL statements should you use?

  A. CREATE CLUSTERED INDEX CL_IDX_Customer ON Customers(CustomerID); CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData); CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData) USING XML INDEX PXML_IDX_Customer FOR PATH;
  B. CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData); CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData) USING XML INDEX PXML_IDX_Customer FOR VALUE;
  C. CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData); CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData) USING XML INDEX PXML_IDX_Customer FOR PATH;
  D. CREATE CLUSTERED INDEX CL_IDX_Customer ON Customers(CustomerID); CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData); CREATE XML INDEX SXML_IDX_Customer_Property ON Customer(CustomerData) USING XML INDEX PXML_IDX_Customer FOR VALUE;
  A. CREATE CLUSTERED INDEX CL_IDX_Customer ON Customers(CustomerID); CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData); CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData) USING XML INDEX PXML_IDX_Customer FOR PATH;

  ---

  The primary XML index indexes all tags, values, and paths within the XML instances in an XML column. To create a primary XML index, the table in which the XML column occurs must have a clustered index on the primary key of the table.

  SQL Server uses this primary key to correlate rows in the primary XML index with rows in the table that contains the XML column.

  The first index on the xml type column must be the primary XML index. Using the primary XML index, the following types of secondary indexes are supported:

  PATH, VALUE, and PROPERTY.

  Following are some guidelines for creating one or more secondary indexes:

  If your workload uses path expressions significantly on XML columns, the PATH secondary XML index is likely to speed up your workload. The most common case is the use of the exist() method on XML columns in the WHERE clause of

  Transact-SQL.

  If your workload retrieves multiple values from individual XML instances by using path expressions, clustering paths within each XML instance in the PROPERTY index may be helpful. This scenario typically occurs in a property bag scenario

  when properties of an object are fetched and its primary key value is known.

  If your workload involves querying for values within XML instances without knowing the element or attribute names that contain those values, you may want to create the VALUE index. This typically occurs with descendant axes lookups, such

  as //author[last-name="Howard"], where elements can occur at any level of the hierarchy. It also occurs in wildcard queries, such as /book [@* = "novel"], where the query looks for elements that have some attribute having

  the value "novel".

• Question 189:

  You are using TRY...CATCH error handling.

  You need to raise an error that will pass control to the CATCH block.

  Which severity level should you use?

  B. 9
  C. 10
  D. 16
  D. 16

  ---

  A TRY...CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client

  connection is terminated after receiving the message, and the error is logged in the error and application logs.

  Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 25.

• Question 190:

  You have a table named Stores that has an XML column named OpenHours. This column contains the opening and closing times.

  ...

  You need to write a query that returns a list of stores and their opening time for a specified day.

  Which code segment should you use?

  A. DECLARE @Day VARCHAR(10) = 'Tuesday' SELECT StoreName, OpenHours.value('/hours[1]/@open','time') FROM Stores WHERE OpenHours.value('/hours[1]/@dayofWeek','varchar(20)') = @Day
  B. DECLARE @Day VARCHAR(10) = 'Tuesday' SELECT StoreName, OpenHours.value('/hours[1]/@open','time') FROM Stores WHERE OpenHours.exist('/hours[@dayofWeek=sql:variable("@Day")]') = 1
  C. DECLARE @Day VARCHAR(10) = 'Tuesday' SELECT Storename, OpenHours.query('data(/hours[@dayofWeek=sql:variable("@Day")]/@open)') FROM Stores
  D. DECLARE @Day VARCHAR(10) = 'Tuesday' SELECT StoreName, OpenHours.value('/hours[1][@dayofWeek=sql:variable("@Day")]/@open','time') FROM Stores
  C. DECLARE @Day VARCHAR(10) = 'Tuesday' SELECT Storename, OpenHours.query('data(/hours[@dayofWeek=sql:variable("@Day")]/@open)') FROM Stores

  ---

  CREATE TABLE Stores(

  StoreName VARCHAR(10)NOT NULL,

  OpenHours [xml] NULL,

  CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED (StoreName)) GO

  INSERT INTO Stores (StoreName, OpenHours)

  VALUES

  (

  'Store1',

  ' '), ('Store2',

  ' ') DECLARE @Day VARCHAR

  (10) = 'Tuesday' SELECT Storename, OpenHours.query('data(/hours[@dayofWeek=sql:variable("@Day")]/ @open)') FROM Stores GO