Which isolation level should you use?
You are a database developer. You design a database solution by using SQL Server 2008.
Your company has offices in Europe, Asia, North America, and Africa. The company data is
updated for each region after business hours. The queries used by reports on the database
are blocked when the data is being updated. Occasionally, the data is updated
simultaneously for the Africa and Europe regions. You need to ensure maximum
concurrency for the database by using minimum possible system resources. Which isolation
level should you use?
You need to ensure that when the UPDATE statement causes an exception, SP1 performs the following tasks: • T
You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a stored procedure that is created by using the following DDL
code segment. You discover that the UPDATE statement occasionally throws an exception
that causes the entire transaction to roll back. You need to ensure that when the UPDATE
statement causes an exception, SP1 performs the following tasks: • The INSERT statement
is committed, • All data is in a consistent state. • The Transaction count is equal to 0 after
execution completes. What should you do?
You need to ensure that throughout the transaction the data maintains the original version
You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that during reads, the transaction experiences blocking from concurrent updates.
You need to ensure that throughout the transaction the data maintains the original version.
What should you do?
Which Transact-SQL statement should you use?
You use Microsoft SQL Server 2012 to develop a database application.
You create a stored procedure named dbo.ModifyData that can modify rows.
You need to ensure that when the transaction fails, dbo.ModifyData meets the following
requirements:
Does not return an error
Closes all opened transactions
Which Transact-SQL statement should you use?
What should you recommend?
You need to recommend a solution that will enable two or more application sessions to
share the same transaction and locks. The solution must enable the applications to work on
the same data without creating lock conflicts. What should you recommend?
You need to capture execution plans that will include detailed information on missing indexes recommended by t
You are developing a database application by using Microsoft SQL Server 2012.
You have a query that runs slower than expected.
You need to capture execution plans that will include detailed information on missing indexes
recommended by the query optimizer.
What should you do?
What should the strategy include?
A new data analytics application is being implemented in your organization. Users will run a
process that compares result sets before and after executing complex data modifications.
The data will always be queried by using the same conditions. Some tables updated by the
processes will not need to be compared. Your need to design a locking strategy for the
process that meets the following requirements: • Enables other processes or users to
modify tables that are not being compared • Prevents other processes from performing data
manipulation language (DML) activity on the tables that are being compared What should
the strategy include?
You need to reduce procedure cache usage from these statements without creating any additional indexes
You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that a large amount of memory is consumed by single-use dynamic queries.
You need to reduce procedure cache usage from these statements without creating any additional
indexes.
What should you do?
Which function should you 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 mathematical manipulations using random generated float numeric values.
You need to identify the appropriate function to use to generate random values. Which
function should you use?
What should you recommend?
You are designing a table to store customer data. The table will contain a column to store
the e-mail addresses of the customers. You need to recommend a solution to store the
e-mail addresses. The solution must meet the following requirements: • E-mail addresses
must contain the @ symbol • E-mail addresses must be validated by using a regular
expression • E-mail addresses must contain a top-level domain of .com, .org, or .edu What
should you recommend?