Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Sample Questions:

1. 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?

A) Stored procedure
B) Inline function
C) Scalar-valued function
D) User-defined data type

2. You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and
backup schedule are configured as shown in the following table:

One of the hard disk drives that stores the reporting database fails at 16:40 hours. You need to ensure that
the reporting database is restored. You also need to ensure that data loss is minimal.
What should you do?

A) Restore the latest full backup. Then, restore the latest differential backup.
B) Perform a point-in-time restore.
C) Perform a partial restore.
D) Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
E) Perform a page restore.
F) Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
G) Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
H) Restore the latest full backup.

3. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications' ability to access data.
The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the existing applications.
What should you create for each application?

A) table-valued functions
B) stored procedures
C) table partitions
D) views

4. You administer a Microsoft SQL Server 2012 server that has multiple databases. You need to ensure that users are unable to create stored procedures that begin with sp_. Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Build List and Reorder:

5. You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute other stored procedures in the other databases. You need to ensure that callers that do not have permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL statement should you use?

A) EXECUTE AS OWNER
B) USE Database1
C) EXECUTE AS CALLER
D) USE Database2

Solutions: