You administer a Microsoft SQL Server 2012 database named Contoso that contains a single userdefined database role namedBillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permission for all stored procedures in Contoso to BillingUsers.
Which Transact-SQL statement should you use?

You have been hired as a Database Consultant by ABC.com to design a SQL Server 2012 database
solution.
You are tasked with designing a scale-out and high-availability SQL Server 2012 Online
Transaction Processing (OLTP) database solution that will maintain copies of data across two server
instances.
Your solution must provide scale-out of read operations by distributing the reads from clients across
two SQL Server 2012 nodes. The data in both SQL Server nodes needs to be indexed.
What should you include in your solution?

You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table
that will store Customer data from different sources. The table will include a column that contains
the CustomerID from the source system and a column that contains the SourceID. A sample of this
data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to
ensure that the data in the table is in the order of SourceID and then CustomerID. Which TransactSQL statement 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 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 CustomerId value
set to 1 in the following XML format.

Which Transact-SQL query should you use?

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a
computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader
and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit
button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans
where the estimated rows do not match the actual rows for SalesOrderHeader by using an
unexpected index on SalesOrderDetail.
You need to improve the performance of the query.
What should you do?

You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The
database contains two tables that have the following definitions:

Global customers place orders from several countries. You need to view the country from which
each customer has placed the most orders.
Which Transact-SQL query do you use?

DRAG DROP
You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the
database for a report that displays Customer Name and the total revenue for that customer.
Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements
from the list of statements to the answer area and arrange them in the correct order.)

You use Microsoft SQL Server 2012 to develop a database application. You need to create an object
that meets the following requirements:
Takes an input variable
Returns a table of values
Cannot be referenced within a view
Which object should you use?