You work as a Systems Administrator at a company named ABC.com. You are in the process of
deploying new servers running SQL Server 2012.
You install a new server named ABC-SQL07. A week later, you receive reports of poor database
performance. You discover that at the times of poor performance, the CPU is running at nearly
100% utilization.
You need to recommend a monitoring solution for ABC-SQL07 that will send you an email alert if
the CPU utilization is more than 80%.
What should you include in your recommendation?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
Company developers are testing a custom application. The developers report that deadlocks
often occur when the application runs a query. The developers have asked you to help identify
which transaction is causing the deadlocks.
How should you troubleshoot the issue?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
You need to create a table for a database. The table will have between 1 million and 5 million
rows. The table will have columns that have null-valued rows 70% of the time.
You need to optimize the table so that the storage space used for each row is minimized.
What should you do?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
You need to create a table for a database. The table will have between 1 million and 5 million
rows. The data in one column comes from an expression that uses other columns in the same
table.
You need to optimize the table so that the storage space used for each row is minimized.
What should you do?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
The company has a database named Products. Tables in the Products database contain data
including part numbers, product name, color, type and size.
Users in the Marketing department have created brochures for each product. The brochures have
been created in the XML Paper Specification (XPS) format.
You have been asked to add a table to the Products database to store the product brochures.
The brochures need to be stored in a folder structure. Company users will also need to access
the brochures from Windows applications using UNC paths.
How can you meet these requirements?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
The company has a database named CorpDB1.
CorpDB1 is over 1 Terabyte in size and contains many tables.
The backup schedule for CorpDB1 includes a full backup every week, a differential backup every
day and a transaction log backup every hour.

You plan to add a table to CorpDB1. You want the new table to have a full backup every night and
a transaction log backup every 15 minutes.
Which feature will enable you to specify a different backup schedule?

You work as a Database Administrator (DBA) at ABC.com. The company has a SQL Server 2008
R2 environment.
You have a requirement for a temporary database that requires the features of SQL Server 2012.
The database must be highly available for one week while it is in use after which time the
database will be deleted.
Your solution must not affect the current SQL Server environment.
Which of the following is the best solution to meet the requirement?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.

A database named CorpDB1 has many tables containing historical data about customers,
products and sales. CorpDB1 is 200GB in size.
You plan to create a view to display information from multiple tables in CorpDB1. To improve
query performance, you want to ensure that the view is indexed.
How can you ensure that the view is indexed?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
You plan to create a stored procedure. You need to configure a parameter that will enable the
stored procedure to receive multiple rows of data without creating a temporary table or many
parameters.
Which of the following will enable this functionality?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows

Server 2012 servers running SQL Server 2012.
You plan to create a stored procedure. You want to create the stored procedure using Microsoft
Visual C# code.
Which of the following technologies enables you to create stored procedures in a .NET Framework
language?