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

1. You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You need to create a view named uv_CustomerFullName to meet the following requirements:
The code must NOT include object delimiters.
The view must be created in the Sales schema.
Columns must only be referenced by using one-part names.
The view must return the first name and the last name of all customers.
The view must prevent the underlying structure of the customer table from being changed.
The view must be able to resolve all referenced objects, regardless of the user's default schema.
Which code segment should you use?
To answer, type the correct code in the answer area.

A) CREATE VIEW Sales.uv_CustomerFullName with Schemabinding AS SELECT FirstName, LastName FROM Customers
B) CREATE VIEW Sales.uv_CustomerFullName AS SELECT FirstName, LastName FROM Customers

2. You administer a Microsoft SQL Server 2012 database. You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!';
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate';
BACKUP CERTIFICATE TDE_Certificate TO FILE = 'd:\TDE_Certificate.cer'
WITH PRIVATE KEY (FILE = 'd:\TDE_Certificate.key', ENCRYPTION BY PASSWORD =
'MyPassword1!');
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location. A hardware failure occurs and so a new server must be installed and configured. After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database. You need to be able to restore the database. Which Transact-SQL statement should you use before attempting the restore?

A) DECLARE @startdate date SET @startdate = GETDATE() CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' WITH START_DATE = @startdate;
B) CREATE ASSEMBLY TDE_Assembly FROM 'd:\TDE_Certificate.cer' WITH PERMISSION_SET = SAFE;
GO
CREATE CERTIFICATE TDE_Certificate
FROM ASSEMBLY TDE_Assembly;
C) CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'd:\TDE_Certificate.key', DECRYPTION BY PASSWORD 'MyPassword1!');
D) CREATE CERTIFICATE TDE_Certificate FROM EXECUTABLE FILE = 'd:\TDE_Certificate.cer'

3. You administer a Microsoft SQL Server 2012. A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60. You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

A) DBCC INPUTBUFFER (60)
B) EXEC sp_who 60
C) EXEC sp_helpdb 60
D) SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60

4. You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9) )
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. Which three Transact-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.)
Build List and Reorder:

5. You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment. All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs access to the SQL Server. You need to create a server role named SpecialDBARole that can perform the following functions:
View all databases.
View the server state.
Assign GRANT, DENY, and REVOKE permissions on logins.
You need to add DOMAIN\JrDBAs to the server role. You also need to provide the least level of privileges necessary. Which SQL statement or statements should you use? Choose all that apply.

A) CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
B) CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
C) ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
D) CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
E) GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];
F) GRANT VIEW DEFINITION TO [SpecialDBARole];

Solutions: