Ensurepass

QUESTION 101

A database contains tables named Sales and SalesArchive. SalesArchive contains historical sales data. You configure Change Tracking on the Sales table. The minimum valid version of the Sales table is 10. You need to write a query to export only sales data that changed since version 10, including the primary key of deleted rows. Which method should you use?

 

A.

FROM Sales

RIGHT JOIN CHANGETABLE (CHANGES Sales, 10) AS C …

B.

FROM Sales

INNER JOIN CHANGETABLE (CHANGES Sales, 10) AS C …

C.

FROM Sales

INNER JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C …

D.

FROM Sales

RIGHT JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C …

 

Correct Answer: A

 

 

QUESTION 102

You are required to modify a table named Sales.SalesOrder. The table has change tracking enabled on it. You need to disable change tracking prior to modifying the Sales.SalesOrder table.

Which Transact-SQL statement should you use?

 

A.

EXEC sys.sp_cdc_disable_db

B.

ALTER DATABASE Contoso

SET CHANGE_TRACKING = OFF

C.

ALTER TABLE Sales.SalesOrder

DISABLE CHANGE_TRACKING

D.

EXEC sys.sp_cdc_disable_table

@source_schema = N’Sales’,

@source_name = N’SalesOrder’,

@capture_instance = N’Sales_SalesOrder’

 

Correct Answer: C

Explanation:

Enabling Change Tracking for a Database

Before you can use change tracking, you must enable change tracking at the database level.

ALTER DATABASE AdventureWorks2008R2

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

 

Enabling Change Tracking for a Table

Change tracking must be enabled for each table that you want tracked. When change tracking is enabled, change tracking information is maintained for all rows in the table that are affected by a DML operation.

ALTER TABLE Person.Person

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON);

 

Disabling Change Tracking

Change tracking must first be disabled for all change-tracked tables before change tracking can be set to OFF for the database. To determine the tables that have change tracking enabled for a database, use the sys.

change_tracking_tables catalog view.

ALTER TABLE Person.Person

DISABLE CHANGE_TRACKING;

 

When no tables in a database track changes, you can disable change tracking for the database.

ALTER DATABASE AdventureWorks2008R2

SET CHANGE_TRACKING = OFF;

 

 

QUESTION 103

You have implemented change tracking on a table named Sales.SalesOrder. You need to determine all columns that have changed since the minimum valid version. Which function should you use?

 

A.

CHANGE_TRACKING_CURRENT_VERSION

B.

CHANGE_TRACKING_IS_COLUMN_IN_MASK

C.

CHANGETABLE with the CHANGES argument

D.

CHANGETABLE with the VERSION argument

 

Correct Answer: C

Explanation:

CHANGETABLE (

{ CHANGES table , last_sync_version

| VERSION table , <primary_key_values> } )

[AS] table_alias [ ( column_alias [ ,…n ] )

CHANGES table , last_sync_version

Returns tracking information for all changes to a table that have occurred since the version that is specified by

last_sync_version.

VERSION table, { <primary_key_values> }

Returns the latest change tracking information for a specified row. Primary key values must identify the row.

<primary_key_values> identifies the primary key columns and specifies the values.

 

 

QUESTION 104

You have two tables named Customers and Orders. They are related by a foreign key constraint on the CustomerID on each table. You need to generate the following XML structure that includes all customers and their related orders.

 

<Root>

 

<Customer>

 

<CustomerName>Customer1</CustomerName>

 

<Orders>

 

<Order><OrderDate>1/1/2008</OrderDate><OrderValue>422</OrderValue></Order>

 

<Order><OrderDate>4/8/2008</OrderDate><OrderValue>300</OrderValue></Order>

 

 

</Orders>

 

</Customer>

 

<Root>

 

Which query should you use?

 

A.

SELECT CustomerName,

OrderDate,

OrderValue

FROM Customers c

JOIN Orders o

ON o.CustomerID = c.CustomerID

FOR XML AUTO, TYPE

B.

SELECT *

FROM (SELECT CustomerName,

NULL AS OrderDate,

NULL AS OrderValue

FROM Customers

UNION ALL

SELECT NULL,

OrderDate,

OrderValue

FROM Orders) CustomerOrders

FOR XML AUTO, ROOT(‘Root’)

C.

SELECT CustomerName,

(SELECT OrderDate,

OrderValue

FROM Orders

FOR XML PATH(‘Order’))

FROM Customers

FOR XML PATH(‘Customer’), ROOT(‘Root’), TYPE

D.

SELECT CustomerName,

(SELECT OrderDate,

OrderValue

FROM Orders

WHERE Orders.CustomerId = Customers.CustomerId

FOR XML PATH(‘Order’), TYPE) Orders

FROM Customers

FOR XML PATH(‘Customer’), ROOT(‘Root’)

 

Correct Answer: D

 

 

QUESTION 105

You need to generate the following XML document.

 

<ProductExport>

 

<Product Price=”99″>Product1</Product>

 

<Product Price=”199″>Product2</Product>

 

<Product Price=”299″>Product3</Product>

 

<Product Price=”399″>Product4</Product>

 

</ProductExport>

 

Which query should you use?

 

A.

SELECT Price, ProductName

FROM Products AS ProductExport

FOR XML PATH(‘Product’)

B.

SELECT Price, ProductName

FROM Products

FOR XML AUTO, ROOT(‘ProductExport’)

C.

SELECT Price [@Price],

ProductName AS [*]

FROM Products AS ProductExport

FOR XML AUTO, ELEMENTS

D.

SELECT Price [@Price],

ProductName AS [*]

FROM Products

FOR XML PATH(‘Product’),ROOT(‘ProductExport’)

 

Correct Answer: D

 

 

QUESTION 106

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

 

Correct Answer: CD

 

 

QUESTION 107

Your company’s database contains Customers and Orders tables. You have been tasked to write a SELECT statement that exposes the data as a valid and well-formed XML document. The XML data must be attribute-based, and the order data XML must be nested in the customer data XML.

You need to write a SELECT statement to meet the requirements. Which Transact-SQL statement should you use?

 

A.

SELECT c.ContactName, o.OrderDate, o.RequiredDate

FROM Customers c

INNER JOIN Orders o

ON c.CustomerID = o.CustomerID

FOR XML RAW(‘Contact’), ROOT(‘ContactOrderDate’)

B.

SELECT c.ContactName, o.OrderDate, o.RequiredDate

FROM Customers c

INNER JOIN Orders o

ON c.CustomerID = o.CustomerID

FOR XML PATH(‘ContactOrderDate’)

C.

SELECT c.ContactName, o.OrderDate, o.RequiredDate

FROM Customers c

INNER JOIN Orders o

ON c.CustomerID = o.CustomerID

FOR XML AUTO

D.

SELECT c.ContactName, o.OrderDate, o.RequiredDate

FROM Customers c

INNER JOIN Orders o

ON c.CustomerID = o.CustomerID

FOR XML AUTO, ROOT(‘ContactOrderDate’)

 

Correct Answer: D

Explanation:

SELECT c.ContactName, o.OrderDate, o.RequiredDate

FROM Customers c INNER JOIN Orders o

ON c.CustomerID = o.CustomerID

FOR XML RAW(‘Contact’), ROOT(‘ContactOrderDate’)

 

Produce the following result:

 

<ContactOrderDate>

<Contact ContactName=”Paul Henriot” OrderDate=”1996-07-04T00:00:00″ RequiredDate=”1996-08-01T00:00:00″ />

<Contact ContactName=”Karin Josephs” OrderDate=”1996-07-05T00:00:00″ RequiredDate=”1996-08-16T00:00:00″ />

 

<Contact ContactName=”Paula Wilson” OrderDate=”1998-05-06T00:00:00″ RequiredDate=”1998-06-03T00:00:00″ />

</ContactOrderDate>

SELECT c.ContactName, o.OrderDate, o.RequiredDate

FROM Customers c INNER JOIN Orders o

ON c.CustomerID = o.CustomerID

FOR XML PATH(‘ContactOrderDate’)

 

Produce the following result:

 

<ContactOrderDate>

<ContactName>Paul Henriot</ContactName>

<OrderDate>1996-07-04T00:00:00</OrderDate>

<RequiredDate>1996-08-01T00:00:00</RequiredDate>

</ContactOrderDate>

<ContactOrderDate>

<ContactName>Karin Josephs</ContactName>

<OrderDate>1996-07-05T00:00:00</OrderDate>

<RequiredDate>1996-08-16T00:00:00</RequiredDate>

</ContactOrderDate>

 

<ContactOrderDate>

<ContactName>Paula Wilson</ContactName>

<OrderDate>1998-05-06T00:00:00</OrderDate>

<RequiredDate>1998-06-03T00:00:00</RequiredDate>

</ContactOrderDate>

 

SELECT c.ContactName, o.OrderDate, o.RequiredDate

FROM Customers c INNER JOIN Orders o

ON c.CustomerID = o.CustomerID

FOR XML AUTO

 

Produce the following result:

<c ContactName=”Paul Henriot”>

<o OrderDate=”1996-07-04T00:00:00″ RequiredDate=”1996-08-01T00:00:00″ /> </c>

<c ContactName=”Karin Josephs”>

<o OrderDate=”1996-07-05T00:00:00″ RequiredDate=”1996-08-16T00:00:00″ /> </c>

 

<c ContactName=”Paula Wilson”>

<o OrderDate=”1998-05-06T00:00:00″ RequiredDate=”1998-06-03T00:00:00″ /> </c>

 

SELECT c.ContactName, o.OrderDate, o.RequiredDate

FROM Customers c INNER JOIN Orders o

ON c.CustomerID = o.CustomerID

FOR XML AUTO, ROOT(‘ContactOrderDate’)

 

Produce the following result:

<ContactOrderDate>

<c ContactName=”Paul Henriot”>

<o OrderDate=”1996-07-04T00:00:00″ RequiredDate=”1996-08-01T00:00:00″ /> </c>

<c ContactName=”Karin Josephs”>

<o OrderDate=”1996-07-05T00:00:00″ RequiredDate=”1996-08-16T00:00:00″ /> </c>

 

<c ContactName=”Paula Wilson”>

<o OrderDate=”1998-05-06T00:00:00″ RequiredDate=”1998-06-03T00:00:00″ /> </c>

</ContactOrderDate>

 

 

QUESTION 108

You have a table named Customer that has an XML column named Locations. This column stores an XML fragment that contains details of one or more locations, as show in the following examples.

 

<Location City=”Sydney” Address=”…” PhoneNumber=”…” />

 

<Location City=”Chicago” Address=”…” PhoneNumber=”…” />

 

<Location City=”London” Address=”…” PhoneNumber=”…” />

 

You need to write a query that returns a row for each of the customer’s locations. Each resulting row must include the customer name, city, and an XML fragment that contains the location details. Which query should you use?

 

A.

SELECT

CustomerName,

Locations.query(‘for $i in /Location return data($i/@City)’),

Locations.query(‘for $i in /Location return $i’)

FROM Customer

B.

SELECT

CustomerName,

Locations.query(‘for $i in /Location return element Location {$i/@City, $i}’)

FROM Customer

C.

SELECT

CustomerName,

Locations.query(‘data(/Location/@City)’),

Locations.query(‘/Location’)

FROM Customer

D.

SELECT

CustomerName,

Loc.value(‘@City’,’varchar(100)’),

Loc.query(‘.’)

FROM Customer

CROSS APPLY Customer.Locations.nodes (‘/Location’) Locs(Loc)

 

Correct Answer: D

 

 

QUESTION 109

Click the Exhibit button.

 

image014

 

You have the following XML:

 

<Site URL=”http://www.contoso.com/index.htm”>

 

<Site URL=”http://www.contoso.com/finance/index.htm”>

 

<Site URL=”http://www.contoso.com/finance/reports/index.htm” />

 

<Site URL=”http://www.contoso.com/finance/main/index.htm” />

 

</Site>

 

<Site URL=”http://www.contoso.com/marketing/index.htm”>

 

<Site URL=”http://www.contoso.com/marketing/reports/index.htm” />

 

<Site URL=”http://www.contoso.com/marketing/main/index.htm” />

 

</Site>

 

<Site URL=”http://www.contoso.com/sales/index.htm” />

 

</Site>

 

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’)

 

Correct Answer: C

Explanation:

DECLARE @XMLDocHandle int,

@XMLDoc varchar(1000) =’

<Site URL=”http://www.contoso.com/index.htm”>

<Site URL=”http://www.contoso.com/finance/index.htm”>

<Site URL=”http://www.contoso.com/finance/reports/index.htm” />

<Site URL=”http://www.contoso.com/finance/main/index.htm” />

</Site>

<Site URL=”http://www.contoso.com/marketing/index.htm”>

<Site URL=”http://www.contoso.com/marketing/reports/index.htm” />

<Site URL=”http://www.contoso.com/marketing/main/index.htm” />

</Site>

<Site URL=”http://www.contoso.com/sales/index.htm” />

</Site>’;

–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 110

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

 

Correct Answer: A

Explanation:

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.

 

Instant Access to Download Latest Complete Collection of Microsoft 70-433 Real Exam

Try Microsoft 70-433 Free Demo