Recertification for MCSE: Data Platform v5.0
Question 1
An administrator provides a digital certificate named ServerCert.
You need to implement Transparent Data Encryption (TDE) on ProductsDB.
Which code segment should you use?
- A. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE DBCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
- B. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
- C. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
- D. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE DBCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
Answer : C
Explanation:
http://msdn.microsoft.com/en-us/library/bb934049.aspx
Question 2
You are planning the ManufacturingSteps table.
You need to define the ProductID column in the CREATE TABLE statement.
Which code segment should you use?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : B
Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx
http://msdn.microsoft.com/en-us/library/ms179610.aspx
http://msdn.microsoft.com/en-us/library/ff878370.aspx
Question 3
You are testing disaster recovery procedures.
When you attempt to restore ProductsDB to another server, you receive the following error message: "Msg
33111, Level 16, State 3, Line 5
Cannot find server certificate with thumbprint '
0x9D876A3468B911ElBA4CFCBF4724019B\
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore ProductsDB to another server.
Which code segment should you execute on the other server?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : D
Question 4
You need to prepare the database to use the .NET Framework ProcessProducts component.
Which code segments should you execute? (Each correct answer presents part of the solution. Choose all that apply.)
- A. Option A
- B. Option B
- C. Option C
- D. Option D
- E. Option E
- F. Option F
- G. Option G
Answer : A,C,D,E
Explanation:
http://msdn.microsoft.com/en-us/library/ms131048.aspx
http://msdn.microsoft.com/en-us/library/ms131052.aspx
http://msdn.microsoft.com/en-us/library/ms189524.aspx
http://msdn.microsoft.com/en-us/library/ms345106.aspx
http://msdn.microsoft.com/en-us/library/ms131107.aspx
Question 5
Which data type should you use for ProductType?
- A. varchar(11)
- B. nvarchar(11)
- C. char(11)
- D. bigint
Answer : C
Question 6
While testing the CategoryFromType function, you discover that the function is returning
'Other'.
You need to update CategoryFromType to return the category name.
Which line of code should you modify in CategoryFromType.sql?
- A. 04
- B. 05
- C. 12
- D. 14
Answer : B
Question 7
You need to create a function that will use a SELECT statement in
ProductsByProductType.sql.
Which code segment should you use to complete the function?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : B
Explanation:
http://msdn.microsoft.com/en-us/library/ms191320.aspx
http://msdn.microsoft.com/en-us/library/ms186755.aspx
Question 8
You need to modify Production.ProductDetails_Insert to comply with the application requirements.
Which code segment should you execute?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : C
Explanation:
http://msdn.microsoft.com/en-us/library/bb669102.aspx
Question 9
Which code segment should you use to define the ProductDetails column?
A. ProductDetails xml (DOCUMENT Production.ProductDetailsSchema) NULL
B. ProductDetails xml NULL -
C. ProductDetails xml (CONTENT Production.ProductDetailsSchema) NULL
D. ProductDetails varchar(MAX) NULL
Answer : D
Topic 5, Litware, Inc
Overview
General Overview
You are a database developer for a company named Litware, Inc. Litware has a main office
in Miami.
Litware has a job posting web application named WebApp1. WebApp1 uses a database
named DB1. DB1 is hosted on a server named Server1. The database design of DB1 is
shown in the exhibit. (Click the Exhibit button.)
Question 10
You need to implement a change to usp_ExportOpenings that meets the integration requirements.
What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?)
- A. To the end of line 04, add [Opening].
- B. To the end of line 05, add [Opening! title].
- C. To line 10, add FOR XML RAW.
- D. To line 10, add FOR XMLEXPLICIT.
- E. To line 10, add FOR XML AUTO.
- F. To the end of line 04, add [Opening!ELEMENT].
- G. To the end of line 06, add [Opening!salary!ELEMENT].
- H. To the end of line 05, add [Opening!title!ELEMENT].
- I. To the end of line 06, add [Opening! salary].
Answer : A,B,E,I
Explanation:
The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. You have minimal control over the shape of the
XML generated. The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics.
Question 11
You need to implement a solution that resolves the salary query issue.
Which statement should you execute on DB1?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : A
Question 12
You need to identify the cause of the page split issues.
Which SQL Server feature should you use?
- A. DBCC REINDEX
- B. SQL Server Profiler
- C. Extended Events
- D. DBCC TRACEOFF
Answer : C
Question 13
You need to implement a solution that addresses the upload requirements.
Which code segment should you use to implement the Conversions assembly?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : D
Question 14
You need to implement a solution that meets the security requirements.
Which statement should you execute?
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer : A
Question 15
You need to implement a solution that meets the data recovery requirements.
You update each stored procedure to accept a parameter named @transactionID.
What should you add next to the beginning of each stored procedure?
- A. SAVE TRANSACTION WITH MARK @transactionID
- B. ROLLBACK DISTRIBUTED TRANSACTION @transactionID
- C. BEGIN TRANSACTION WITH MARK @transactionID
- D. COMMIT TRANSACTION @transactionID
Answer : C