Welcome to ExamTopics
ExamTopics Logo
- Expert Verified, Online, Free.
Location Chicago IL, USA

Microsoft 70-464 Exam Actual Questions

The questions for 70-464 were last updated at June 26, 2019.
  • Viewing page 1 out of 23 pages.
  • Viewing questions 1-5 out of 114 questions
Topic 1 - Single Topic
Question #1 Topic 1

You have a table named Table1 that contains 1 million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar (16) data type.
You have a certificate named Cert1.
You need to replace Column1 with a new encrypted column named Column2 that uses one-way hashing.
Which code segment should you execute before you remove Column1?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
Select and Place:

Reveal Solution Hide Solution   Discussion  

Correct Answer:
* There are a few different hashing algorithms available in SQL Server 2005: MD2, MD4, MD5, SHA, SHA1, with each having pros and cons.
* In cryptography, SHA-1 is a cryptographic hash function designed by the United States National Security Agencyand published by the United StatesNISTas a
USFederal Information Processing Standard.SHA stands for "secure hash algorithm".The four SHAalgorithmsare structured differently and are distinguished asSHA-0,SHA-1,SHA-2, andSHA-3.SHA-1 is very similar to SHA-0, but corrects an error in the original SHA hash specification that led to significant weaknesses.The SHA-0 algorithm was not adopted by many applications.SHA-2 on the other hand significantly differs from the SHA-1 hash function. SHA-1 is the most widely used of the existing SHA hash functions, and is employed in several widely used applications and protocols.
* To encrypt a column of data using a simple symmetric encryption In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
USE AdventureWorks2012;
--If there is no master key, create one now.

(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj' GO

WITH SUBJECT = 'Customer Credit Card Numbers';

GO -


GO -
-- Create a column in which to store the encrypted data.

ALTER TABLE Sales.CreditCard -
ADD CardNumber_Encryptedvarbinary(128);

GO -
-- Open the symmetric key with which to encrypt the data.
-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.

UPDATE Sales.CreditCard -
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11') , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
, CreditCardID)));

GO -
Reference: SQL Server 2012, Encrypt a Column of Data
Ref: http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example- using-symmetric-keys/

Question #2 Topic 1

You use SQL Azure to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read and change the price of all the products sold on the e-commerce site.
You need to ensure that other transactions are blocked from updating product data while sp1 is executing.
Which transaction isolation level should you use in sp1?

  • A. Repeatable read
  • B. Read committed
  • C. Serializable
  • D. Snapshot
Reveal Solution Hide Solution   Discussion  

Correct Answer: C

Question #3 Topic 1

You review a query that runs slowly. The query accesses data in a table named Schema1.Table1.
The following is the relevant portion of the execution plan for the query:

You need to create the missing index.
Which code segment should you execute?

  • A. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3
  • B. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1)
  • C. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4)
  • D. CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4)
Reveal Solution Hide Solution   Discussion  

Correct Answer: C

Question #4 Topic 1

You use SQL Server 2012 to maintain the data used by the applications at your company.
You plan to create a table named Table1 by using the following statement. (Line numbers are included for reference only.)

You need to ensure that Table1 contains a column named UserName. The UserName column will:
✑ Store string values in any language.
✑ Accept a maximum of 200 characters.
✑ Be case-insensitive and accent-insensitive.
Which code segment should you add at line 03?

  • A. UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL,
  • B. UserName varchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
  • C. UserName varchar(200) COLLATE Latin 1_General_CS_AS NOT NULL,
  • D. UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
Reveal Solution Hide Solution   Discussion  

Correct Answer: D

Question #5 Topic 1

You execute the following code:

You need to select the task that has an IsFinished value of true from the Project that has an Id value of 1.
Which code segment should you use?

  • A. Option A
  • B. Option B
  • C. Option C
  • D. Option D
Reveal Solution Hide Solution   Discussion  

Correct Answer: B