You need to ensure that your result set is encapsulated within a root element
You are a database developer. You plan to design a database solution by using SQL Server
2008. You are writing a query that returns XML data from a table in a database. You need to
ensure that your result set is encapsulated within a root element. What should you do?
You need to reduce the occurrence of these timeout errors
You administer several Microsoft SQL Server 2012 database servers. Merge replication has been
configured for an application that is distributed across offices throughout a wide area network
(WAN). Many of the tables involved in replication use the XML and varchar (max) data types.
Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of
these timeout errors. What should you do?
You need to design the database to meet these requirements
You u se S QL S erver 2 008 t o d esign a d atabase t hat w ill h old i ncoming XML r
esponses f or a n Electronic Data Interchange system. You have the following requirements:
• The database is scalable. • The data is accessible to heterogeneous platforms. • The
database stores various types of reports from multiple sources. • The database stores large
amounts of data. • The solution allows search by keywords. • Full text search is not enabled
in the database. You need to design the database to meet these requirements. What should
you do?
You need to achieve this goal by granting the minimum permissions required
You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a
stored procedure that returns database-level information from Dynamic Management Views. You
grant User1 access to execute the stored procedure. You need to ensure that the stored procedure
returns the required information when User1 executes the stored procedure. You need to achieve
this goal by granting the minimum permissions required. What should you do? (Each correct answer
presents a complete solution. Choose all that apply.)
You need to identify the appropriate function to use
You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain a view. The view will contain a computed column that will
be indexed. The computed column will use a user-defined scalar function. The function will
perform complex string manipulations. You need to identify the appropriate function to use.
What should you do?
You need to develop a stored procedure strategy that will manipulate temporary result sets to meet the followi
You need to develop a stored procedure strategy that will manipulate temporary result sets
to meet the following requirements: • Stored procedures must receive result sets as
parameters • The strategy must be developed by using the minimum amount of effort The
result sets will contain less than 1,000 rows. What should you use?
Which Transact-SQL query should you use?
You develop a Microsoft SQL Server 2012 database that contains tables named Customers and
Orders. The tables are related by a column named CustomerId.
You need to create a query that meets the following requirements:
Returns the CustomerName for all customers and the OrderDate for any orders that they have
placed.
Results must not include customers who have not placed any orders.
Which Transact-SQL query should you use?
What should you include in the strategy?
You are developing a security policy for the SQL Server developers in your organization.
You need to create a security strategy to protect Transact-SQL applications from SQL
injection attacks. What should you include in the strategy?
You need to design the function to meet these requirements
You are a database developer. You plan to design a database solution by using SQL Server
2008. Your company operates a fulfillment warehouse. The company has the following
business requirements: • The packing of items to fulfill orders is processed as quickly as
possible. • When the product picker indicates that a box is full, the exact items list of the
content and invoice are made available. You plan to design a Transact-SQL user-defined
function. The function must perform the following tasks: • Accept a list of value pairs named
ProductID and Quantity as input. • Retrieve the product price information from the
ProductPrice table. • Calculate the total price for each product. • Calculate the total price for
all products. • Return the detailed and aggregated totals. You need to design the function to
meet these requirements. You want to achieve this goal by using the minimum amount of
coding effort. What should you do?
Which object should you use?
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets
the following requirements:
Status information must be logged to a status table.
If the status table does not exist at the beginning of the batch, it must be created.
Which object should you use?