Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 61:

  You plan to add a new column named SmallKey to the Sales.Product table that will be used in a unique constraint. You are required to ensure that the following information is applied when adding the new column:

  'a1' and 'A1' are treated as different values 'a' and 'A' sort before 'b' and 'B' in an ORDER BY clause

  You need to select the collation that meets the requirements for the new column.

  Which collation should you select?

  A. Latin1_General_BIN
  B. SQL_Latin1_General_CP1_CI_AI
  C. SQL_Latin1_General_CP1_CI_AS
  D. SQL_Latin1_General_CP1_CS_AS
  D. SQL_Latin1_General_CP1_CS_AS

  ---

  SQL Server Collation Name consists of several parts, one of them is responsible for CaseSensitivity CI specifies case-insensitive, CS specifies case-sensitive.

  BIN specifies the binary sort order to be used.

  So, because we want case-sensitive location, B and C are not suitable. Latin1_General_BIN use binary sort order, but we want linguistical sort order (according to rules of language), not based on the code point values of characters.

• Question 62:

  You are tasked to create a table that has a column that must store the current time accurate to ten microseconds.

  You need to use a system function in conjunction with the DEFAULT option in the column definition.

  Which system function should you use?

  A. DATEADD
  B. GETUTCDATE
  C. SYSDATETIME
  D. CURRENT_TIMESTAMP
  C. SYSDATETIME

• Question 63:

  You have run a server side trace that created 45 trace files. You want to load the trace files on your workstation in a database table called PerfData for further analysis. You need to load three files starting at c:\my_trace_38.trc.

  Which Transact-SQL statement should you use?

  A. SELECT * INTO PerfData FROM ::fn_trace_gettable('c:\my_trace.trc', 3)
  B. SELECT * INTO PerfData FROM ::fn_trace_gettable('c:\my_trace_38.trc', 3)
  C. SELECT * INTO PerfData FROM ::fn_trace_gettable('c:\my_trace38.trc', default)
  D. SELECT * INTO PerfData FROM ( SELECT * FROM ::fn_trace_gettable ('c:\my_trace_38.trc', default) UNION ALL SELECT * FROM ::fn_trace_gettable ('c:\my_trace_39.trc', default) UNION ALL SELECT * FROM ::fn_trace_gettable ('c:\my_trace_40.trc', default) ) Trc
  B. SELECT * INTO PerfData FROM ::fn_trace_gettable('c:\my_trace_38.trc', 3)

  ---

  sys.fn_trace_gettable returns the content of one or more trace files in tabular form.

  fn_trace_gettable ( 'filename' , number_files )

  'filename'

  Specifies the initial trace file to be read. filename is nvarchar(256), with no default.

  number_files

  Specifies the number of rollover files to be read. This number includes the initial file specified in filename.

  number_files is an int.

  If number_files is specified as default, fn_trace_gettable reads all rollover files until it reaches the end of the trace.

• Question 64:

  You have two tables named SalesPerson and SalesTerritory.

  You need to create sample data by using a Cartesian product that contains the data from the SalesPerson and SalesTerritory tables.

  Which code segment should you use?

  A. SELECT p.SalesPersonId,
  B. Name AS [Territory] FROM Sales.SalesPerson p FULL JOIN Sales.SalesTerritory t ON p.TerritoryId = t.TerritoryId
  C. SELECT p.SalesPersonId,
  D. Name AS [Territory] FROM Sales.SalesPerson p INNER JOIN Sales.SalesTerritory t ON p.TerritoryId = t.TerritoryId
  E. SELECT p.SalesPersonId,
  F. Name AS [Territory] FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t WHERE p.TerritoryId = t.TerritoryId
  G. SELECT p.SalesPersonId,
  H. Name AS [Territory] FROM Sales.SalesPerson p CROSS JOIN Sales.SalesTerritory t;
  D. Name AS [Territory] FROM Sales.SalesPerson p INNER JOIN Sales.SalesTerritory t ON p.TerritoryId = t.TerritoryId

• Question 65:

  You are a database developer working on an application hosted on Microsoft SQL Server 2008 R2.

  The application regularly imports employee data from XML files. These files are bulk-loaded into the CompanyDoc column of the CompanyXML table.

  The table the following definition:

  

  You need to be able to return the data as shown in the following table:

  

  Which Transact-SQL statements should you use?

  

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

• Question 66:

  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. sp_xml_removedocument
  B. sp_xml_preparedocument
  C. sp_reserve_http_namespace
  D. sp_delete_http_namespace_reservation
  A. sp_xml_removedocument

  ---

  sp_xml_removedocument removes the internal representation of the XML document specified by the document handle and invalidates the document handle. sp_xml_preparedocument reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes in the XML document: elements, attributes, text, comments, and so on. A parsed document is stored in the internal cache of SQL Server. The MSXML parser uses one- eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

• Question 67:

  You have two tables named Customer and SalesOrder. You need to identify all customers that have not yet made any purchases and those that have only made orders with an OrderTotal less than 100.

  Which query should you use?

  A. SELECT * FROM Customer WHERE 100 > ALL (SELECT OrderTotal FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID)
  B. SELECT * FROM Customer WHERE 100 > SOME (SELECT OrderTotal FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID)
  C. SELECT * FROM Customer WHERE 100 > (SELECT MAX(OrderTotal) FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID)
  D. SELECT * FROM Customer WHERE EXISTS (SELECT SalesOrder.CustomerID FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID AND SalesOrder.OrderTotal
  A. SELECT * FROM Customer WHERE 100 > ALL (SELECT OrderTotal FROM SalesOrder WHERE Customer.CustomerID = SalesOrder.CustomerID)

• Question 68:

  You need to configure Full-Text Search to ignore specific words.

  Which Full-Text Search component should you use?

  A. iFilter
  B. Stoplist
  C. Thesaurus file
  D. Word breakers
  B. Stoplist

• Question 69:

  You are a developer for a Microsoft SQL Server 2008 R2 database instance. You create tables named order, customer, and product as follows: You need to write a query to sum the sales of all orders by the following entries: The product name The month the product was ordered The product name and the month the product was ordered The grand total of all sales

  

  Which SQL query should you use?

  

  A. B. C. D. E. F. G. H. I.
  G

• Question 70:

  You have two tables named Products and NewProducts that have identical structures. You have the following query (Line numbers are included for reference only):

  01 SELECT Product, Description 02 FROM dbo.Products 04 SELECT Product, Description 05 FROM dbo.NewProducts

  You need to choose the appropriate Transact-SQL operator to display rows that exist in both tables.

  Which Transact-SQL operator should you insert in line 03?

  A. UNION
  B. EXCEPT
  C. UNION ALL
  D. INTERSECT
  D. INTERSECT