Microsoft 70-433 Online Practice Questions and Exam Preparation

Microsoft 70-433 Online Questions & Answers

• Question 51:

  You have a table named Products.Product. The table has columns ProductID, Name, Size, and Category. You have a variable named @XML with following XML value:

  You are tasked to write a query that lists the products in Products.Product that match the categories listed in the XML document.

  You need to write a query to accomplish the task.

  Which query should you write?

  A. SELECT p.ProductID, p.Name, p.Size, p.Category FROM Production.Product p CROSS APPLY @XML.nodes('//Category') as x(s)
  B. SELECT p.ProductID, p.Name, p.Size, p.Category FROM Production.Product p OUTER APPLY @XML.nodes('//Category') as x(s)
  C. WITH XMLTable AS ( SELECT s.value('@Name','varchar(20)') as Category FROM @XML.nodes('//Category') as x(s) ) SELECT p.ProductID, p.Name, p.Size, p.Category FROM Production.Product p INNER JOIN XMLTable x ON p.Category = x.Category
  D. WITH XMLTable AS ( SELECT s.value('@Category','varchar(20)') as Category FROM @XML.nodes('//Category') as x(s) ) SELECT p.ProductID, p.Name, p.Size, p.Category FROM Production.Product p INNER JOIN XMLTable x ON p.Category = x.Category
  C. WITH XMLTable AS ( SELECT s.value('@Name','varchar(20)') as Category FROM @XML.nodes('//Category') as x(s) ) SELECT p.ProductID, p.Name, p.Size, p.Category FROM Production.Product p INNER JOIN XMLTable x ON p.Category = x.Category

• Question 52:

  Your database is 5GB and contains a table named SalesHistory. Sales information is frequently inserted and updated. You discover that excessive page splitting is occurring. You need to reduce the occurrence of page splitting in the SalesHistory table.

  Which code segment should you use?.

  A. ALTER DATABASE Sales MODIFY FILE (NAME = Salesdat3, SIZE = 10GB);
  B. ALTER INDEX ALL ON Sales.SalesHistory REBUILD WITH (FILLFACTOR = 60);
  C. EXEC sys.sp_configure 'fill factor (%)', '60';
  D. UPDATE STATISTICS Sales.SalesHistory(Products) WITH FULLSCAN, NORECOMPUTE;
  B. ALTER INDEX ALL ON Sales.SalesHistory REBUILD WITH (FILLFACTOR = 60);

  ---

  Fill Factor

  The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf- level page to be filled with data, reserving the

  remainder on each page as free space for future growth. For example, specifying a fill-factor value of 60 means that 40 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying

  table.

  Page Splits

  A correctly chosen fill-factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table.When a new row is added to a full index page, the Database Engine moves

  approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can

  cause fragmentation that causes increased I/O operations.

  Fragmentation

  Fragmentation breaks down to physical and logical fragmentation (or, internal and external fragmentation). Physical/Internal fragmentation is caused when there is wasted space in the index caused by page splits, deletes, FILLFACTOR and

  PAD_INDEX. Logical/ External fragmentation is when the pages that make up the leaf levels of the index are not in good order. This is usually caused by page splits. Both cause performance problems. Internal fragmentation causes problems

  because the indexes get bigger and bigger requiring more and more pages to store the data, which means that reads from the index slow things down. External fragmentation causes problems because when the data needs to be read from

  the index it has to skip all over the place following the links between pages, again, slowing things down.

  The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in

  the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. You can remedy index fragmentation by either

  reorganizing an index or by rebuilding an index. To reorganize one or more indexes, use the ALTER INDEX statement with the REORGANIZE clause.

  Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order

  improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated.

  Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in

  contiguous pages (allocating new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.

  You can use the CREATE INDEX with the DROP_EXISTING clause or ALTER INDEX with the REBUILD clause statements to set the fill-factor value for individual indexes. EXEC sys.sp_configure 'fill factor (%)', '60'; will modify the server

  default fill factor value.

• Question 53:

  Your server collation is SQL_Latin1_General_CP1_CI_AS. You have a database named Contoso that has a collation setting of SQL_Scandinavian_Cp850_CI_AS. You create and populate a temporary table #Person from table dbo.Person in

  Contoso using the following statements:

  use MyDB;

  CREATE TABLE #Person (LastName nchar(128));

  INSERT INTO #Person SELECT LastName FROM dbo.Person; You then run the following command:

  SELECT * FROM dbo.Person a JOIN #Person b

  ON a.LastName = b.LastName;

  This command returns the following error:

  Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Scandinavian_Cp850_CI_AS" in the equal to operation. You need to resolve the collation conflict.

  Which Transact-SQL statement should you use?

  A. CREATE TABLE #Person (LastName nvarchar(128) SPARSE);
  B. CREATE TABLE #Person (LastName nvarchar(128) COLLATE database_default);
  C. CREATE TABLE #Person (LastName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS);
  D. CREATE TABLE tmpPerson (LastName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS);
  B. CREATE TABLE #Person (LastName nvarchar(128) COLLATE database_default);

• Question 54:

  You are a database developer for your organization.

  You create an application that uses a Transact-SQL variable to store user input data. The database collation is case-insensitive.

  The variable is constructed as shown in the following statement:

  

  You need to implement a keyword search that meets the following requirements:

  Searches for the existence of the word ARPANET within the user-entered content.

  If the search term is found, the statement must return its starting position, and 0 otherwise.

  Performs a case-sensitive search for the given search term.

  Which Transact-SQL statement should you use?

  

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

• Question 55:

  You need to manually raise an error.

  The error message that will be returned will display a message that contains parameters 1, 2, 3 sp_addmessage @msgnum = 6000,

  @severity = 16,

  @msgtext = N'Your message (%d), (%d), (%d)';

  GO

  You have to raise message to get correct message.. 'Your message (1), (2), (3).' Which statement will you use?

  RAISE_ERROR(60000, 16, 1, 1, 2, 3)

  RAISE_ERROR(60000, 16, 1, 1, 2, 3)

• Question 56:

  Click the Exhibit button.

  

  You have the following XML:

  You are tasked to query the sites listed in the XML by using OPENXML. The results will have two columns, ParentSiteURL and SiteURL. The ParentSiteURL column should contain the URL attribute of the parent site. The SiteURL column

  should contain the URL attribute of the site itself.

  The output should look like that in the exhibit.

  You need to write the OPENXML query.

  Which Transact-SQL statement should you use?

  A. SELECT ParentSiteURL, SiteURL FROM OPENXML (@XMLDocHandle, '//@Site', 1) WITH ( ParentSiteURL nVarChar(512) '../URL', SiteURL nVarChar(512) 'URL')
  B. SELECT ParentSiteURL, SiteURL FROM OPENXML (@XMLDocHandle, '//URL', 1) WITH ( ParentSiteURL nVarChar(512) '../@URL', SiteURL nVarChar(512) '@URL')
  C. SELECT ParentSiteURL, SiteURL FROM OPENXML (@XMLDocHandle, '//Site', 1) WITH ( ParentSiteURL nVarChar(512) '../@URL', SiteURL nVarChar(512) '@URL')
  D. SELECT ParentSiteURL, SiteURL FROM OPENXML (@XMLDocHandle, '//@URL', 1) WITH ( ParentSiteURL nVarChar(512) '../URL', SiteURL nVarChar(512) 'URL')
  C. SELECT ParentSiteURL, SiteURL FROM OPENXML (@XMLDocHandle, '//Site', 1) WITH ( ParentSiteURL nVarChar(512) '../@URL', SiteURL nVarChar(512) '@URL')

  ---

  DECLARE @XMLDocHandle int,

  @XMLDoc varchar(1000) ='

  ';

  --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLDoc SELECT ParentSiteURL, SiteURL

  FROM OPENXML (@XMLDocHandle, '//Site', 1)

  WITH ( ParentSiteURL nVarChar(512) '../@URL',

  SiteURL nVarChar(512) '@URL')

• Question 57:

  You are a developer for a Microsoft SQL Server 2008 R2 database instance used to support a customer service application. You create tables named complaint, customer, and product as follows:

  

  You need to write a query to return all customer names and total number of complaints for customers who have made more than 10 complaints. Which SQL query should you use?

  

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

• Question 58:

  Your company's database contains Customers and Orders tables.

  You have been tasked to write a SELECT statement that outputs customer and order data as a valid and well-formed XML document. You are required to mix attribute and element based XML within the document. You have determined that using the FOR XML AUTO clause will not be suitable.

  You need to identify the correct FOR XML clause to meet the requirement.

  Which FOR XML statement should you use? (Each correct answer represents a complete solution. Choose two.)

  A. FOR BROWSE
  B. FOR XML RAW
  C. FOR XML PATH
  D. FOR XML EXPLICIT
  C. FOR XML PATH
  D. FOR XML EXPLICIT

• Question 59:

  You administer a Microsoft SQL Server 2008 database that includes a table named Products. The Products table has the following schema:

  

  You create a User-Defined Function (UDF) in the same database. The UDF has the following schema:

  

  You need to meet the following requirements:

  Ensure that the UnitPrice column does not accept NULL values.

  Use the CalculateProductProfit() UDF.

  Avoid accidental drop or change of the Product table.

  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 60:

  You have been tasked to delete a number of Database Mail messages that have been sent. You need to delete all the emails that were sent more than one month ago.

  Which Transact-SQL statements should you run?

  A. DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE()) EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo
  B. DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE()) EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo
  C. DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE()) EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo,'Success'
  D. DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE()) EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo,'Sent'
  D. DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE()) EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo,'Sent'

  ---

  sysmail_delete_mailitems_sp [ [ @sent_before = ] 'sent_before' ] [ , [ @sent_status = ] 'sent_status' ] Permanently deletes e-mail messages from the Database Mail internal tables.

  [ @sent_before= ] 'sent_before'

  Deletes e-mails up to the date and time provided as the sent_before argument. sent_before is datetime with NULL as default. NULL indicates all dates.

  [ @sent_status= ] 'sent_status'

  Deletes e-mails of the type specified by sent_status. sent_status is varchar(8) with no default. Valid entries are sent, unsent, retrying, and failed. NULL indicates all statuses.

  Database Mail messages and their attachments are stored in the msdb database. Messages should be periodically deleted to prevent msdb from growing larger than expected and to comply with your organizations document retention

  program. Use the sysmail_delete_mailitems_sp stored procedure to permanently delete email messages from the Database Mail tables. An optional argument allows you to delete only older e-mails by providing a date and time. E-mails older

  than that argument will be deleted. Another optional argument allows you to delete only e-mails of a certain type, specified as the sent_status argument. You must provide an argument either for @sent_before or @sent_status. To delete all

  messages, use @sent_before = getdate().