Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com
QUESTION 1
You work as a contract developer for National Retailers. You are currently working
on the online order application the National Retailers database developer has
informed you that the product parts and components will be stored in a xml data
type column. National Retailers employees are allowed to request product
information regarding a doll or action figure line by clicking a button on the Web
form. The data will only be retrieved if the employee requests details.
You need to design the appropriate data access technologies and must thus choose
the object that you need to use to store the query results for product parts and
component information.
What should you do?
A. Make use of a DataSet object.
B. Make use of an XmlElement object.
C. Make use of an XmlDocument object.
D. Make use of an XmlDocumentFragment object.
Answer: D
Explanation: An XmlDocumentFragment object can store either a well-formed
XML document or a fragment of a document. This would be consistent with the xml
data type which has the ability to store well-formed documents as well as fragments.
1. The National Retailers management wants to expand business by selling their
exclusive doll line on the company Web site
Incorrect Answers:
A: A DataSet object is used when the method returned a relational data set, akin to the
method that is generated from a SELECT statement.
B: An XmlElement object is used to store the results of a SELECT ... FOR XML query,
not the contents of a single value.
C: An XmlDocument object must store well-formed XML Documents. It is thus not
compatible with the xml data type.
QUESTION 2
You work as the database developer for National Retailers. You need to optimize
the indexing strategies and are thus designing the indexes for the Sales.Orders table.
The query in the following exhibit is frequently executed, though it is not the most
commonly executed query.
SELECT Salesrepresentative, SUM(Commission)
FROM Sales.Orders
WHERE Date BETWEEN @ startDate AND @ endDate
GROUP BY Salesrepresentative
ORDER BY Salesrepresentative
You need to use the appropriate statement to create the best index to accommodate
this query.
What should you do?
A. Use the following statement:
CREATE INDEX ix_Commission
ON Sales.Orders(Salesrepresentative, Date, Commission)
B. Use the following statement:
CREATE CLUSTERED INDEX ix_Commission
ON Sales.Orders(Salesrepresentative, Date)
C. Use the following statement:
CREATE INDEX ix_Commission
ON Sales.Orders(Date)
INCLUDE (Salesrepresentative);
D. Use the following statement:
CREATE INDEX ix_Commission
ON Sales.Orders(Date, Salesrepresentative)
INCLUDE (Commission);
Answer: D
Explanation: The Date column is used to select the records and the
Salesrepresentative column is used to group and order the records. This means that
both these columns have to be key columns. The Date column is used in a
BETWEEN comparison, thus it should also be the first in the query. Furthermore,
it has higher selectivity than the Salesrepresentative column.
1. Sales Representatives should also be allowed to check the current commissions due to them.
Incorrect Answers:
A: The column used for equality or BETWEEN comparisons should be listed first. This
must then be followed by the most selective column, then the rest of the predicate
columns in order of decreasing selectivity. And, although it is possible that you can
create an index by using a computed column as the key column, it is recommended that
key columns be kept as narrow as possible. This means that making use of an included
column for Commission is a better option.
B: A Clustered index should have high selectivity. The Salesrepresentative column does
not have high selectivity. This means that this clustered index will not be appropriate for
any other queries done against the table like retrieving order information for instance. A
table can only have one unique index.
C: The Salesrepresentative column is used in the GROUP BY clause. Thus it would be
better suited as the key column rather than an included column. Furthermore, this is not a
covering index as it does not include Commission as an included column.
QUESTION 3
You work as the database developer for National Retailers. While busy designing
queries for the retrieval of data from XML sources, you are writing a script that
will generate an XML file to be imported into the collections application. This
application assumed XML data includes both elements and attributes.
You need to make a choice of the most appropriate type of Transact-SQL statement
to use to retrieve the data from the database.
What should you do?
A. Make use of the OPENXML Transact-SQL statement.
B. Make use of the sp_xml_preparedocument stored procedure.
C. Make use of the SELECT ... FOR XML AUTO Transact-SQL statement.
D. Make use of the SELECT ... FOR XML PATH Transact-SQL statement.
Answer: D
Explanation: The FOR XML PATH clause of the SELECT
statement will allow you to use XPath to define the structure for the XML data that
is returned. This structure can include both elements and attributes.
1. The Chicago office handles debt collection. All customers with an outstanding balance
over 60 days are also sent to the Chicago office. The accounting department makes use of
a collections application that imports XML data.
Incorrect Answers:
A: The OPENXML statement is used to insert XML data into relational tables. Not to
retrieve a resultant set formatted as XML from relational tables.
B: The sp_xml_preparedocument system stored procedure is used prior to calling OPENXML.
C: The FOR XML AUTO clause of the SELECT statement will generate a result set of
nested elements and as such do not make allowance for a mix of elements and attributes.
QUESTION 4
You are busy designing the database query strategy for National Retailers that will
retrieve the result set used to report on monthly sales trends. To this end you are
designing the stored procedure. This stored procedure will be used by an
application that makes use of Microsoft Visual C# .NET.
Of the requirements that you need to keep in mind is that the analysts need the
ability to retrieve a result set with approximately 100 records and scroll through
them to gather information. These analysts will need the ability to locate records
based on either factory or product I
D. They do NOT update any data. You thus
need to design the most appropriate cursor strategy for National Retailers.
What should you do?
A. Create a server-side static cursor.
B. Create a server-side dynamic cursor.
C. Create a client-side forward-only cursor.
D. Use a default result set and do not create a cursor.
Answer: D
Explanation: The Default Result Set caches all records in the result set to the client.
This will thus reduce round trips across the network to one and prevent data from
being stored in the tempdb.
1. Regularly at the end of each month, reports are generated manually and e-mailed to the
Chicago office. Monthly sales and invoice aging information is included in these reports.
Incorrect Answers:
A: If you create a Server-side static cursor it will consume server resources and require a
round-trip across the network each time the client fetches data. Furthermore the cursor
will then be stored in tempdb.
B: A Server-side dynamic cursor does require a round-trip across the network every time
a client fetches data.
C: Creating a Client-side forward-only cursor will not allow analysts to scroll through
the data the way that they require to. These cursors only support the ability to fetch the
next record and not to move to a specific record.
QUESTION 5
You work as the database developer for National Retailers. You are currently
designing the code that will retrieve the product information for the online order
application. To this end you need to determine the appropriate type of object that
you should create to return to the Web application. The Web application will
require that information such as product name, product description, and product
price information be returned in response to a search request.
What should you do?
A. Use a Transact-SQL stored procedure.
B. Use an extended stored procedure.
C. Use a table-valued user-defined function.
D. Use a Common Language Runtime (CLR) user-defined function.
Answer: A
Explanation: The online application accesses data via a Native XML Web service.
This means that you are limited to using an object that can be exposed as a Web
method. A Transact-SQL stored procedure can return the required information and
can be exposed as a Web method.
1. The National Retailers management wants to expand business by selling their
exclusive doll line on the company Web site.
2. The future online Sales application will be outsourced. NRCH-DB02 will host a
database to support the e-commerce application. The e-commerce application will make
use of Simple Object Access Protocol (SOAP) to retrieve product information and submit
orders.
Incorrect Answers:
B: An Extended stored procedure cannot be exposed as a Web method.
C: A Table-valued user-defined function cannot be exposed as a Web method.
D: A CLR user-defined function is not required because the data can be retrieved using
Transact-SQL.
QUESTION 6
You are designing the error-handling routines for National Retailers. You are
creating a function that will be used to check the audit trail for the products. This
function must raise a custom error in the event of the audit trail not being
verifiable. Do bear in mind that the code to check the audit trail requires a query
and a cursor.
What should you do?
A. Use the following error-handing method:
-- Perform query and open the cursor
-- Run code that iterates through the cursor and checks the audit trail
IF @@ERROR
RAISERROR @@ERROR
B. Use the following error-handing method:
BEGIN TRY
-- Perform query and open the cursor
-- Run code that iterates through the cursor and checks the audit trail
END TRY
BEGIN CATCH
RAISERROR (@myCustomError, 15)
END CATCH
C. Use the following error-handing method:
--Perform query and open the cursor
--Fetch the first row
WHILE /*not the last row*/
IF /*Code that checks the audit trail*/
--Fetch the next row
ELSE
SET @@ERROR = @myCustomError
END WHILE
D. Use the following error-handing method:
--Perform query and open the cursor
--Fetch the first row
WHILE /*not the last row*/
IF /*Code that checks the audit trail*/
-- Fetch the next row
ELSE
RAISERROR (@myCustomError, 15)
END WHILE
Answer: D
Explanation: You need to pass the error message back to the application via an IF
ELSE statement to check the values in a row, then calling RAISERROR in the event
of the values being invalid. The RAISERROR function will allow you to send an
error message up the calling chain.
1. Industry regulations require an audit trail for all components and parts that go into a
doll or action figure. The audit trail must be able to use the UPC number on the package
to trace all the components and parts, who supplied the components and parts, and what
date the components and parts were shipped. Components and parts information must be
entered at the time the product line is created and cannot be altered after the quality
inspection. Attempts to modify components and parts information after the inspection
time must be logged.
2. Only product lines with an intact audit trail can be shipped. If an audit trail is not
available, the product line must be destroyed. The product line application includes a
verification routine that checks a specific product line's audit trail. The application makes
use of structured error handling to react to a product line that must be destroyed.
Incorrect Answers:
A: The requirements will not be met if you use @@ERROR after iterating through the
cursor. The @@ERROR global variable contains the error number generated by the last
statement. Thus if will only include an error number if the last statement to process the
cursor is responsible for the error.
B: Using a TRY ... CATCH block within a function is not possible.
C: @@ERROR is a global variable that is automatically set by SQL Server every time a
statement executes. Thus you cannot set @@ERROR.
QUESTION 7
You are designing the error-handling routines for the National Retailers Web
application. In the event of a customer failing to enter valid payment information,
you need to send a custom error to the calling application. Furthermore, this error
message has to be localized to the user's language. The Web application will pass the
language when it opens a connection to the database server. You thus need to decide
which would be the most appropriate way to support error messages in multiple
languages.
What should you do?
A. A CASE statement should be added to the CATCH block.
Then call RAISERROR with a different error message for each language.
B. A different error number should be used for each language version.
Then a CASE statement should be added to the trigger and call RAISERROR with
the language-specific error.
C. Add a message for each language using sp_addmessage.
A different error number should be used for each language version.
Raise the error in the @@ERROR global variable.
D. Add a message for each language using sp_addmessage.
The same error number should be used for each language version.
Call RAISERROR from the trigger.
Answer: D
Explanation: The same error numbers should be used when adding an error
message for each language. This will allow SQL Server to match the session
language to the error message language when you call RAISERROR. The
sp_addmessage stored procedure is used to add an error message.
1. The National Retailers management wants to expand business by selling their
exclusive doll line on the company Web site.
2. The future online Sales application will be outsourced. NRCH-DB02 will host a
database to support the e-commerce application. The e-commerce application will make
use of Simple Object Access Protocol (SOAP) to retrieve product information and submit
orders.
Incorrect Answers:
A: A CASE statement should not be added to the CATCH block and then call
RAISERROR with a different error message for each language. This will make
maintenance quite difficult since you will then be required to modify the code that calls
RAISERROR if a message changes or you need to add a language.
B: There is no need to use a different error number for each different language version
and then add a CASE statement to the trigger. This option will work, though it will be
difficult to maintain since the trigger will then have to be modified each time a language
is added. It will also require more programming effort instead of allowing SQL Server to
choose the right language for the error message.
C: If you raise the error in @@ERROR, then the error associated with the last statement
will be raised and not the custom error message. Also, there is no need to define a
different error number for each language version.
QUESTION 8
You are designing the error-handling routines for National Retailers. You thus need
to design the code that will validate the audit trail for a product batch.
Users that do not enjoy membership of the sysadmin role will run the
fn_ValidateBatch function. You need to use the appropriate statements to ensure
that in the event of an invalid audit trail, an error is raised to the calling application
and an event is written to the application log.
What should you do?
A. Use the following statements:
sp_addmessage (@msgnum=50888, @severity=15, @msgtext="Batch" + @b "cannot be
validated.")
and
RAISERROR(50888, @batchnum, WITH LOG)
B. Use the following statements:
sp_addmessage (@msgnum=50888, @severity=16, @msgtext="Batch %b cannot be
validated.")
and
RAISERROR (50888, WITH SETERR, @batchnum)
C. Use the following statements:
sp_addmessage (@msgnum=50888, @severity=16, @msgtext="Batch %b cannot be
validated.")
and
RAISERROR (50888, %batchnum)
D. Use the following statements:
sp_addmessage (@msgnum=50888, @severity=16, @msgtext="Batch %b cannot be
validated.")
and
RAISERROR(50888, @batchnum, WITH LOG)
E. Use the following statements:
sp_addmessage (@msgnum=50888, @severity=21, @msgtext="Batch %b cannot be
validated.")
and
RAISERROR(50888, @batchnum, WITH LOG)
Answer: D
Explanation: By specifying the 16 severity level, you ensure that the users who do
not belong to the sysadmin role have the ability to raise the error. Also this option
displays the correct syntax for using arguments in a message text string. By default,
only messages with a severity level of over 19 are logged to the application log. This
can be overridden by specifying WITH LOG when calling RAISERROR.
1. Industry regulations require an audit trail for all components and parts that go into a
doll or action figure. The audit trail must be able to use the UPC number on the package
to trace all the components and parts, who supplied the components and parts, and what
date the components and parts were shipped. Components and parts information must be
entered at the time the product line is created and cannot be altered after the quality
inspection. Attempts to modify components and parts information after the inspection
time must be logged.
Incorrect Answers:
A: This option would be correct except for the severity level of 15. By default, only
messages with a severity level of over 19 are logged to the application log. This option
does not offer any overriding probabilities to have the error logged to the application log.
B: This option is only partly correct. But you should not raise the error by specifying
SETERR. This will result in @ERROR and ERROR_NUMBER to be set to 50000. You
do not make use of a concatenated string to use an argument in the message text.
C: The RAISERROR (50888, $batchnum) is the incorrect syntax. A local variable starts
with an @ and not a %. Also this option will not cause the application to be logged to the
application log.
E: You should not specify a severity of 21. Messages with this severity level can only be
raised by members of the sysadminrole.
QUESTION 9
You work as the database developer for National Retailers. You are currently
designing the indexes that will be used for the OnlineSales.Customers table. The
following exhibit illustrates the most common query that will be used.
SELECT FirstName, LastName, Address, City, State, Zip
FROM OnlineSales.Customers
WHERE CustomerID = @emailAddress
You thus need to select the best statement that should be used to create this index
that will accommodate this query.
What should you do?
A. Use the following statement:
CREATE INDEX ix_custInfo
ON OnlineSales.Customer(CustomerID)
INCLUDE (FirstName, LastName, Address, City, State, Zip);
B. Use the following statement:
CREATE INDEX ix_custInfo
ON OnlineSales.Customer(LastName, FirstName)
INCLUDE Address, City, State, Zip;
C. Use the following statement:
CREATE CLUSTERED INDEX ix_custInfo
ON OnlineSales.Customer(CustomerID, FirstName, LastName, Address, City, State,
Zip);
D. Use the following statement:
CREATE CLUSTERED INDEX ix_custInfo
ON OnlineSales.Customer(CustomerID)
INCLUDE (LastName, Address, FirstName, City, Zip, State);
Answer: A
Explanation: Making use of this statement will create an index with a small unique
key that covers the entire query. For optimal performance a small key size that
includes the columns used in the
WHERE clause and other predicates or in joins are recommended. In this scenario
the only required key column would be the CustomerID column. Then you can
include the rest of the columns as non-key columns. This means that you need to
create the index as a non-clustered index because non-key included columns are not
supported for clustered indexes.
1. The National Retailers management wants to expand business by selling their
exclusive doll line on the company Web site.
Incorrect Answers:
B: This index would not be used for the query because the CustomerID column is not
listed as a key column.
C: This statement, if created will result in an index with 344 bytes in the key column.
Though it is possible, it is not optimal since it is better to create an index with a smaller
key column and included columns.
D: This is incorrect because you cannot make use of included columns in a clustered
index.
QUESTION 10
National Retailers require a database that will support the e-commerce application.
You need to find a way to provide the e-commerce application with information
about the existing inventory. The database must determine the nearest factory that
has the product ordered in stock and send the order to that specific factory to have
it fulfilled.
You thus need to determine how the inventory information should be made
available to the e-commerce application while providing the best possible
performance when placing the order.
What should you do?
A. Query the database server at each factory using OPENROWSET from a stored
procedure run on NRCH-DB02.
B. At each factory implement an HTTP endpoint on the database server and expose a
Web method that checks inventory.
C. Replicate the data from the database servers at the different factories to the
NRCH-DB02 server using merge replication.
D. Use a linked server for the database server at each factory at NRCH-DB02.
Answer: C
Explanation: Using merge replication to replicate all data from the factories to
NRCH-DB02 will make the data available locally and improve query performance when
an order is placed. This is the best type of replication to use in this case because there
could be conflicts by orders placed over the Web and order places by the Sales
representatives at each factory. Also in a case where the different sites/factories are not
well-connected making use of merge replication makes sense. And in this case the
factories are connected to the Chicago office by demand-dial links.
1. The National Retailers management wants to expand business by selling their
exclusive doll line on the company Web site.
2. The future online Sales application will be outsourced. NRCH-DB02 will host a
database to support the e-commerce application. The e-commerce application will make
use of Simple Object Access Protocol (SOAP) to retrieve product information and submit
orders.
Incorrect Answers:
A: Making use of OPENROWSET to query each database server at each factory from a
stored procedure on NRCH-DB02 will result in one or more remote queries to be sent
across the network for each product ordered.
B: Making use of an HTTP endpoint will result in more overhead than is required. There
is already a demand-dial connection between the Chicago office and the different
factories. Thus there is no firewall that restricts incoming protocol to only HTTP. Making
use of a SOAP request will result in even more overhead than a remote query.
D: Making use of a linked server for the database server at each factory and performing a
remote query is not the best solution because each product that is ordered will cause at
least one, and possible more, remote queries to be sent across the network.
Best Microsoft MCTS Certification, Microsoft MCITP Training at certkingdom.com
