You have a database that contains a table named Department. Department contains the
names and locations of each department.
You have a table-valued function named ProjectList() that returns a list of all the projects
assigned to a department. The name of the department is passed as an argument to the
ProjectList() function.
You need to create a query that returns a list of all the department names and the project
names. The solution must return only departments that are associated to projects.
What should you add to the query?

You have a database that uses the following management views:
Sys.dm_os_volume_stats
Sys.dm_db_partition_stats
Sys.dm_db_file_space_usage

Sys.fulltext_indexes
You plan to migrate the database to Microsoft SQL Azure.
You need to identify which view can be used in SQL Azure.
Which view should you identify?

DRAG DROP
You plan to create a custom aggregation function named Function1.
You plan to deploy Function1 to SQL Server 2012.
You need to ensure that Function1 can access a web service. The solution must minimize
the number of changes made to the database.
You create a Microsoft .NET Framework class that contains the definition of Function1.
You upload a certificate to SQL Server.
What three tasks should you perform next?
To answer, move the appropriate actions from the list of actions to the answer area and
arrange them in the correct order.

You have a SQL Server 2012 database named Database1.
Database1 has a table named Customers. Customers contains more than 1 million rows.
The database has a stored procedure that was created by using the following script:

You need to ensure that up_customers returns rows when the following statement is
executed:
EXECUTEup_customers’1,2,3,4,5′;
What should you do?

You need to identify which long running transactions use an index.
Which dynamic management view should you use?

DRAG DROP
You have a SQL Server 2012 database named Database1. Database1 has a data file
named Database1_data.mdf and a transaction log named Database1jog.ldf.
Database1_data.mdf is 1.5 GB. Database1jog.ldf is 1.5 terabytes.
A full backup of Database1 is performed every day.

You need to reduce the size of the log file. The solution must ensure that you can perform
transaction log backups in the future.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the
answer area and arrange them in the correct order.

DRAG DROP
You have a SQL Server 2012 database named database1.

Users report that queries that usually take less than one second to execute, take more than
30 seconds to execute.
You need to view the server resource consumption when the queries are executed.
What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and
arrange them in the correct order.

You create a table named Customers by using the following code segment:

You create a non-clustered index named IX_Name on the name column.
You write the following query to retrieve all of the customers that have a name that starts
with the letters SMI:

You discover that the query performs a table scan.
You need to ensure that the query uses the index.
What should you do?

You plan to execute the following code:

You need to identify how many rows will be in dbo.Table1 after you execute the code.
How many rows should you identify?

You plan to design an application that temporarily stores data in a SQL Azure database.
You need to identify which types of database objects can be used to store data for the
application. The solution must ensure that the application can make changes to the schema
of a temporary object during a session.
Which type of objects should you identify?