Free Download Pass4sure Microsoft MCTS SQL Server 70-433 Dumps
TS: Microsoft SQL Server 2008, Database Development: 70-433 Exam
70-433 Questions & Answers
Exam Code: 70-433
Exam Name: TS: Microsoft SQL Server 2008, Database Development
Q & A: 180 Q&As
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.Invent
ory\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
…you can go to http://www.lead2pass.com/70-433.html to download the full version.