Online Access Free 70-457 Exam Questions

You use Microsoft SQL Server 2012 database to develop a shopping cart application. You need to rotate the unique values of the ProductName field of a table-valued expression into multiple columns in the output. Which Transact-SQL operator should you use?

• A.UNPIVOT
• B.CROSS JOIN
• C.PIVOT
• D.CROSS APPLY

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code. You need to protect the code from being viewed by your customers. Which stored procedure option should you use?

• A.ENCRYPTBYKEY
• B.ENCRYPTBYPASSPHRASE
• C.ENCRYPTBYCERT
• D.ENCRYPTION

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

You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data. BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book. You need to ensure that the new column is assigned a GUID for existing rows. Which four 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:

Correct Answer:

Explanation:
--Burgos - NO
Verified answer as correct. Actually, in the real world, you don't have to use WITH VALUES at the end of the statement and it works just as well. But because the question specifically states which FOUR TSQL statements to use, we have to include it.
Reference: http://msdn.microsoft.com/en-us/library/ms190273.aspx

You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?

• A.READ COMMITTED SNAPSHOT
• B.SNAPSHOT
• C.REPEATABLE READ
• D.SERIALIZABLE