QUESTION 1
You have a table named Inventory. You open a Microsoft Windows PowerShell session at the following location by using the SQL Server Windows PowerShell provider. PS
SQLSERVER:\SQL\CONTOSO\DEFAULT\Databases\ReportServer\Tables\dbo.Inventory\Columns>
Using the SQL Server Windows PowerShell provider, you need to query all the columns in the table. Which cmdlet should you use?
A. Get-Item
B. Get-Location
C. Get-ChildItem
D. Get-ItemProperty
Answer: C
QUESTION 2
You are configuring Service Broker to process messages within a single database. You have performed the following steps.
— CREATE MESSAGE TYPE
— CREATE CONTRACT
— CREATE QUEUE
You need to complete the Service Broker configuration. What should be the next step?
A. CREATE ROUTE
B. CREATE SERVICE
C. CREATE ENDPOINT
D. CREATE BROKER PRIORITY
Answer: B
QUESTION 3
You have a database named Contoso. The Contoso database has a Service Broker queue named VacationRequestQueue. The Contoso database has been restored to a new server. Since restoring the database, Service Broker is no longer able to send new messages. You need to configure Service Broker in order to resolve the issue. Which Transact-SQL statement should you use?
A. ALTER DATABASE Contoso SET NEW_BROKER;
B. ALTER DATABASE Contoso SET ENABLE_BROKER;
C. ALTER QUEUE VacationRequestQueue WITH STATUS = ON;
D. ALTER QUEUE VacationRequestQueue WITH ACTIVATION (STATUS = ON);
Answer: A
QUESTION 4
You created a Service Broker queue by using the following Transact-SQL statement:
CREATE QUEUE VacationRequestQueue WITH RETENTION = OFF, ACTIVATION (
PROCEDURE_NAME = dbo.VacationRequestProcess,
MAX_QUEUE_READERS = 5, EXECUTE AS SELF
);
You need to modify the Service Broker queue to prevent it from processing received messages. The queue should continue to receive messages. Which Transact-SQL statement should you use?
A. ALTER QUEUE VacationRequestQueue WITH RETENTION = ON;
B. ALTER QUEUE VacationRequestQueue WITH STATUS = OFF;
C. ALTER QUEUE VacationRequestQueue WITH ACTIVATION (STATUS = OFF);
D. ALTER QUEUE VacationRequestQueue WITH ACTIVATION (EXECUTE AS OWNER);
Answer: C
QUESTION 5
You use the same Service Broker configuration to support a Web site and an internal application. The Web site generates a greater workload than the internal application. You need to configure Service Broker to ensure that messages sent by the internal application are processed before those sent by the Web site. Which Transact-SQL statement should you use?
A. ALTER SERVICE
B. CREATE CONTRACT
C. CREATE BROKER PRIORITY
D. ALTER QUEUE WITH ACTIVATION
Answer: C
QUESTION 6
You are using Microsoft SQL Server 2008 Enterprise Edition. You need to maintain a history of all data modifications made to a table, including the type of modification and the values modified. Which tracking method should you use?
A. Database Audit
B. Change Tracking
C. C2 Audit Tracing
D. Change Data Capture
Answer: D
QUESTION 7
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 …
Answer: A
QUESTION 8
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’
Answer: C
QUESTION 9
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
Answer: C
QUESTION 10
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’)
Answer: D
http://www.passleader.com/70-433.html
QUESTION 11
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’)
Answer: D
QUESTION 12
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
Answer: CD
QUESTION 13
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’)
Answer: D
QUESTION 14
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)
Answer: D
QUESTION 15
Click the Exhibit button.
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’)
Answer: C
QUESTION 16
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
Answer: A
QUESTION 17
You work for a company that provides marketing data to other companies. You have the following Transact-SQL statement:
DECLARE @CustomerDemographics XML SET @CustomerDemographics=N’
<CustomerDemographics>
<Customer CustomerID=”1″ Age=”21″ Education=”High School”>
<IsCoffeeDrinker>0</IsCoffeeDrinker>
</Customer>
<Customer CustomerID=”2″ Age=”27″ Education=”College”>
<IsCoffeeDrinker>1</IsCoffeeDrinker>
<IsFriendly>1</IsFriendly>
</Customer>
<Customer CustomerID=”3″ Age=”35″ Education=”Unknown”>
<IsCoffeeDrinker>1</IsCoffeeDrinker>
<IsFriendly>1</IsFriendly>
</Customer>
</CustomerDemographics>’
DECLARE @OutputAgeOfCoffeeDrinkers XML
SET @OutputAgeOfCoffeeDrinkers = @CustomerDemographics.query(‘
for $output in /child::CustomerDemographics/child::Customer[( child::IsCoffeeDrinker[1] cast as xs:boolean )]
return <CoffeeDrinkingCustomer> { $output/attribute::Age \} </CoffeeDrinkingCustomer>’)
SELECT @OutputAgeOfCoffeeDrinkers
You need to determine the result of the query. What result should you expect?
A. <CoffeeDrinkingCustomer Age=”27″ />
<CoffeeDrinkingCustomer Age=”35″ />
B. <CoffeeDrinkingCustomer Age=”21″ />
C. <CustomerDemographics>
<Customer>
<CoffeeDrinkingCustomer Age=”21″ />
</Customer>
</CustomerDemographics>
D. <CustomerDemographics>
<Customer>
<CoffeeDrinkingCustomer Age=”27″ />
</Customer>
<Customer>
<CoffeeDrinkingCustomer Age=”35″ />
</Customer>
</CustomerDemographics>
Answer: A
QUESTION 18
You have a table named Stores that has an XML column named OpenHours. This column contains the opening and closing times.
<hours dayofWeek= “Monday” open =”8:00 AM” closed=”8:00 PM”
<hours dayofWeek= “Tuesday” open =”8:00 AM” closed=”8:00 PM”
…
<hours dayofWeek= “Saturday” open =”8:00 AM” closed=”8:00 PM”
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’
D. SELECT StoreName, OpenHours.value(‘/hours[1][@dayofWeek=sql:variable(“@Day”)]/@open’,’time’)
FROM Stores
Answer: C
QUESTION 19
You have the following XML document that contains Product information.
DECLARE @prodList xml =’
<ProductList xmlns=”urn:Wide_World_Importers/schemas/Products”>
<Product Name=”Product1″ Category=”Food” Price=”12.3″ />
<Product Name=”Product2″ Category=”Drink” Price=”1.2″ />
<Product Name=”Product3″ Category=”Food” Price=”5.1″ />
…
</ProductList>’;
You need to return a list of products that contains the Product Name, Category, and Price of each product. Which query should you use?
A. SELECT prod.value(‘.[1]/@Name’,’varchar(100)’), prod.value(‘.[1]/@Category’,’varchar(20)’), prod.value(‘.[1]/@Price’,’money’)
FROM @prodList.nodes(‘/ProductList/Product’) ProdList(prod);
B. SELECT prod.value(‘@Name’,’varchar(100)’), prod.value(‘@Category’,’varchar(20)’),
prod.value(‘@Price’,’money’)
FROM @prodList.nodes(‘/ProductList/Product’) ProdList(prod);
C. WITH XMLNAMESPACES(DEFAULT ‘urn;Wide_World_Importers/schemas/Products’ as o)
SELECT prod.value(‘Name[1]’,’varchar(100)’), prod.value(‘Category[1]’,’varchar(20)’),
prod.value(‘Price[1]’,’money’)
FROM @prodList.nodes(‘/o:ProductList/o:Product’) ProdList(prod);
D. WITH XMLNAMESPACES(DEFAULT ‘urn:Wide_World_Importers/schemas/Products’)
SELECT prod.value(‘./@Name’,’varchar(100)’), prod.value(‘./@Category’,’varchar(20)’),
prod.value(‘./@Price’,’money’)
FROM @prodList.nodes(‘/ProductList/Product’) ProdList(prod);
Answer: D
QUESTION 20
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:
<Root>
<Category Name=”Socks” />
<Category Name=”Pants” />
<Category Name=”Shirts” />
</Root>
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
Answer: C
Achieve High Goals Of The 70-433 Test By Trying Passleader New 70-433 Braindumps