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 are creating a script that uses the CREATE VIEW statement to create a view. Data in a table
in CorpDB1 will be updated using the view. You need to ensure that the view can update all data
in the table except the data in a column specified in a SELECT statement in the view.
How can you achieve this?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
A server named ABC-SQL1 hosts an Online Transaction Processing (OLTP) database named
ABC-DB1. A table in ABC-DB1 has an index. You discover a high level of page splits when data
is updated in the table or when new rows are inserted.
You plan to recreate the index on the table.
What should you do first to reduce the number of page splits?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
You create a table named Orders in a database named SalesDB1. The table includes a column
named Date which has the NULL and SPARSE options configured.
You populate the Orders table with 1 million rows of order data. More than 800,000 rows do not
have a value in the Date column.
You need to create an index for the Orders table.
You need to optimize the index so that the storage space used by the index is minimized.
Which of the following statements should you use to create the index?

You work as a Database Administrator (DBA) for a company named ABC.com. The company has
a Windows Azure subscription. The company uses a cloud-based SQL Server environment
hosted on SQL Azure.
You have a database named ABC-DB1. The database contains a table named Entries and a table
named Updates. The Updates table is used for auditing purposes.
You want the Updates table to be automatically updated every time a new row is added to the
Entries table.
What is the best way to achieve this?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
You create a table using the following statement:

CREATE TABLE Orders
(
OrderID int NOT NULL PRIMARY KEY,
CustomerID int NOT NULL,
OrderDate date NOT NULL
);
You intended the table to be created in a schema named Sales. However the table was created in
a schema named Purchasing.
You want to move the Orders table from the Purchasing schema to the Sales schema.
Which statement should you run?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
A server named ABC-SQL1 has a tape backup device named Tape1 attached to it.
An application named SalesApp is used by the Sales department. SalesApp stores data in a
database named SalesAppDB on ABC-SQL1.
Sales users report that SalesApp is displaying an error. You discover that the log file for
SalesAppDB is full.
You want to clear log file space and ensure the LSN (log sequence number) chain is not affected
by backing up the log file.

Which of the following backup statements should you use?

You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
You have configured parallel execution plans on a server named ABC-SQL1.
You suspect that some of the parallel execution plans may be running in serial.
How can you view which parallel execution plans are running in serial?

You work as a Database Administrator (DBA) for a company named ABC.com. The company has
a Windows Azure subscription. The company uses a cloud-based SQL Server environment
hosted on SQL Azure.
Developers in your company are creating an ecommerce web site. You are designing a database
for the website. The database will be hosted on SQL Azure.

The database will store web site login details and customer credit card numbers.
You need to ensure the usernames, passwords and credit card details are securely stored in the
database.
Which of the following would be most suitable secure storage solution?

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 need to deploy a SQL Server 2012 server to host databases used by the Research and
Development department. The databases used by the Research and Development department
will store sensitive data.
A company security policy states that if Research and Development department database files
and log files are moved to another server, the files must be encrypted.
Which of the following solutions would meet the encryption requirement?

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 need to implement a highly available solution. You need to ensure that the databases remain
online in the event of a single server failure.
You are evaluating the server requirements for AlwaysOn with automatic failover and mirroring
with automatic failover.
What is the minimum number of servers required for each solution? (Choose two).