100% New Updated 70-433 Practice Tests Questions Braindump2go Helps Pass 70-433 Successfully (21-30)

Braindump2go Updates Microsoft 70-433 Exam Dumps Questions, adds some new changed questions from Microsoft Official Exam Center. Want to know 2015 70-433 Exam test points? Download the following free Braindump2go Latest Exam Questions Today!

Exam Code: 70-433
Exam Name: TS: Microsoft SQL Server 2008, Database Development
Certification Provider: Microsoft

Keywords: 70-433 Exam Dumps,70-433 Practice Tests,70-433 Practice Exams,70-433 Exam Questions,70-433 PDF,70-433 VCE Free,70-433 Book,70-433 E-Book,70-433 Study Guide,70-433 Braindump,70-433 Prep Guide

QUESTION 21
Your company exchanges information with other companies by using XML and Web services. Your manager asks you to remove a schema collection that is no longer used.
Before dropping the schema, you should confirm that it is no longer in use.
You need to use a catalog view to determine if the schema collection is being used.
Which catalog view should you use?

A.    sys.xml_schema_components
B.    sys.xml_schema_namespaces
C.    sys.xml_schema_collections
D.    sys.column_xml_schema_collection_usages

Answer: D
Explanation:
sys.column_xml_schema_collection_usages returns a row for each column that is validated by an XML schema.

QUESTION 22
You have an XML schema that you must use to validate XML data in your database.
You need to store this XML schema.
Which code segment should you use?

A.    CREATE SCHEMA CustomerSchema
B.    CREATE DEFAULT CustomerSchema AS 'XML'
C.    CREATE PRIMARY XML INDEX CustomerSchema
D.    CREATE XML SCHEMA COLLECTION CustomerSchema

Answer: D

QUESTION 23
You have a table named Customers that has an XML column named CustomerData.
There are currently no indexes on the table.
You use the following WHERE clause in a query:
WHERE CustomerData.exist ('/CustomerDemographic/@Age[.>="21"]') = 1
You need to create indexes for the query.
Which Transact-SQL statements should you use?

A.    CREATE CLUSTERED INDEX CL_IDX_Customer
ON Customers(CustomerID);
CREATE PRIMARY XML INDEX PXML_IDX_Customer
ON Customers(CustomerData);
CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)
USING XML INDEX PXML_IDX_Customer FOR PATH;
B.    CREATE PRIMARY XML INDEX PXML_IDX_Customer
ON Customers(CustomerData);
CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)
USING XML INDEX PXML_IDX_Customer
FOR VALUE;
C.    CREATE PRIMARY XML INDEX PXML_IDX_Customer
ON Customers(CustomerData);
CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)
USING XML INDEX PXML_IDX_Customer
FOR PATH;
D.    CREATE CLUSTERED INDEX CL_IDX_Customer
ON Customers(CustomerID);
CREATE PRIMARY XML INDEX PXML_IDX_Customer
ON Customers(CustomerData);
CREATE XML INDEX SXML_IDX_Customer_Property ON Customer(CustomerData)
USING XML INDEX PXML_IDX_Customer
FOR VALUE;

Answer: A
Explanation:
The primary XML index indexes all tags, values, and paths within the XML instances in an XML column. To create a primary XML index, the table in which the XML column occurs must have a clustered index on the primary key of the table. SQL Server uses this primary key to correlate rows in the primary XML index with rows in the table that contains the XML column.
The first index on the xml type column must be the primary XML index. Using the primary XML index, the following types of secondary indexes are supported: PATH, VALUE, and PROPERTY.
Following are some guidelines for creating one or more secondary indexes:
If your workload uses path expressions significantly on XML columns, the PATH secondary XML index is likely to speed up your workload. The most common case is the use of the exist() method on XML columns in the WHERE clause of Transact-SQL.
If your workload retrieves multiple values from individual XML instances by using path expressions, clustering paths within each XML instance in the PROPERTY index may be helpful. This scenario typically occurs in a property bag scenario when properties of an object are fetched and its primary key value is known.
If your workload involves querying for values within XML instances without knowing the element or attribute names that contain those values, you may want to create the VALUE index. This typically occurs with descendant axes lookups, such as //author[last- name="Howard"], where <author> elements can occur at any level of the hierarchy. It also occurs in wildcard queries, such as /book [@* = "novel"], where the query looks for <book> elements that have some attribute having the value "novel".

QUESTION 24
You need to capture the execution plan for a query.
Which statement should you use?

A.    SET FORCEPLAN ON;
B.    SET SHOWPLAN_XML ON;
C.    SET STATISTICS IO ON;
D.    SET STATISTICS TIME ON;

Answer: B
Explanation:
SET SHOWPLAN_XML ON
This statement causes SQL Server not to execute Transact-SQL statements. Instead, Microsoft SQL Server returns execution plan information about how the statements are going to be executed in a well-formed XML document.
SET SHOWPLAN_TEXT ON
After this SET statement is executed, SQL Server returns the execution plan information for each query in text. The Transact-SQL statements or batches are not executed.
SET SHOWPLAN_ALL ON
This statement is similar to SET SHOWPLAN_TEXT, except that the output is in a format more verbose than that of SHOWPLAN_TEXT.
SET STATISTICS XML ON
Returns execution information for each statement after the statement executes in addition to the regular result set the statement returns. The output is a set of well-formed XML documents. SET STATISTICS XML ON produces an XML output document for each statement that executes. The difference between SET SHOWPLAN_XML ON and SET STATISTICS XML ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS XML ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.
SET STATISTICS PROFILE ON
Returns the execution information for each statement after the statement executes in addition to the regular result set the statement returns. Both SET statement options provide output in text. The difference between SET SHOWPLAN_ALL ON and SET STATISTICS PROFILE ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS PROFILE ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.

QUESTION 25
You work as a network database administrator at ABC.com.
ABC.com has a database server named ABC-DB02 hosting the Inventory database.
The Inventory database has two tables named Sales and SalesRep that are related by a foreign key with the CASCADE DELETE option.
How would have the records of the Sales table removed?

A.    By creating a Transact-SQL statement utilizing the ALTER TABLE Sales.
B.    By creating a Transact-SQL statement utilizing the DELETE FROM SalesRep.
C.    By creating a Transact-SQL statement utilizing the TRUNCATE DATABASE Inventory.
D.    By creating a Transact-SQL statement utilizing the INSTEAD OF DELETE.
E.    By creating a Transact-SQL statement utilizing the DELETE TOP Sales.

Answer: B

QUESTION 26
You are troubleshooting query performance on SQL Server 2008.
You are tasked to capture a graphical execution plan.
You need to save the plan to a file that can be used by SQL Server Management Studio to display the graphical execution plan.
Which file extension should you use?

A.    .gif
B.    .xml
C.    .psql
D.    .sqlplan

Answer: D

QUESTION 27
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.
ou 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

Answer: B
Explanation:
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 28
You are using SQL Server Profiler to gather deadlock information.
You need to capture an XML description of a deadlock.
Which event should you use?

A.    Lock:Deadlock
B.    Showplan XML
C.    Deadlock Graph
D.    Lock:Deadlock Chain

Answer: C
Explanation:
The Lock:Deadlock event class is produced when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim.
Use the Lock:Deadlock event class to monitor when deadlocks occur and which objects are involved.
You can use this information to determine if deadlocks are significantly affecting the performance of your application.
You can then examine the application code to determine if you can make changes to minimize deadlocks.
The Deadlock Graph event class provides an XML description of a deadlock.
This class occurs simultaneously with the Lock:Deadlock event class.
The Lock:Deadlock Chain event class is produced for each participant in a deadlock.

QUESTION 29
You are troubleshooting query performance on SQL Server 2008.
You have profiler trace data in a table named PerfData.
You need to determine which events are taking longer than one second of CPU time or run for more than two seconds.
Which Transact-SQL statement should you use?

A.    SELECT TextData, Duration, CPU
FROM PerfData
WHERE EventClass = 12 AND
( CPU > 1000 OR
Duration > 2000 )
B.    SELECT TextData, Duration, CPU
FROM PerfData
WHERE EventClass = 12 AND
( CPU > 1000 OR
Duration > 2000000 )
C.    SELECT TextData, Duration, CPU
FROM PerfData
WHERE EventClass = 12 AND
( CPU > 1000000 OR
Duration > 2000 )
D.    SELECT TextData, Duration, CPU
FROM PerfData
WHERE EventClass = 12 AND
( CPU > 1000000 OR
Duration > 2000000 )

Answer: B
Explanation:
Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

QUESTION 30
You are using the Database Engine Tuning Advisor (DTA) to analyze a workload.
You need to save the recommendations generated by the DTA.
Which command should you use?

A.    Preview Workload Table
B.    Export Session Results
C.    Import Session Definition
D.    Export Session Definition

Answer: B

http://www.braindump2go.com/70-433.html