You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
Your investigation shows the root cause is a query against a read-only table that has a
clustered index.
The query returns the following six columns:
• One column in its WHERE clause contained in a non-clustered index •
• Four additional columns
• One COUNT (*) column based on a grouping of the four additional columns You
need to optimize the statement.
What should you do?

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are
defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the
CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are
defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the
Customerld value set to 1 in the following XML format.

Which Transact-SQL query should you use?

You use Microsoft SQL Server 2012 to write code for a transaction that contains several
statements.
There is high contention between readers and writers on several tables used by your
transaction.
You need to minimize the use of the tempdb space. You also need to prevent reading
queries from blocking writing queries.
Which isolation level should you use?

You create a table that has the StudentCode, SubjectCode, and Marks columns to record
mid-year marks for students. The table has marks obtained by 50 students for various
subjects.
You need to ensure that the top half of the students arranged by their average marks must
be given a rank of 1 and the remaining students must be given a rank of 2. Which TransactSQL query should you use?

You create a table that has the StudentCode, SubjectCode, and Marks columns to record
mid-year marks for students. The table has marks obtained by 50 students for various
subjects.
You need to ensure that the following requirements are met:
Students must be ranked based on their average marks.
If one or more students have the same average, the same rank must be given to these
students.
Consecutive ranks must be skipped when the same rank is assigned.
Which Transact-SQL query should you use?

You create a table that has the StudentCode, SubjectCode, and Marks columns to record
mid-year marks for students. The table has marks obtained by 50 students for various
subjects.
You need to retrieve the students who scored the highest marks for each subject along with
the marks.
Which Transact-SQL query should you use?

You use a contained database named ContosoDb within a domain. You need to create a
user who can log on to the ContosoDb database. You also need to ensure that you can port
the database to different database servers within the domain without additional user account
configurations.
Which type of user should you create?

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 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.)