Refresh 70-464 Forum 2021

NEW QUESTION 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1:

First create a hash key using the certificate. Not AES: AES is not based on hashing. Box 2:

Add a column with varbinary data type. Box 3:

Box 4:

Box 5:

Note:
* 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. IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj' GO
CREATE CERTIFICATE Sales09
WITH SUBJECT = 'Customer Credit Card Numbers'; GO
CREATE SYMMETRIC KEY CreditCards_Key11 WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09; 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. OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09;
-- 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

NEW QUESTION 2

The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values.
You need to update Fax to meet the following requirements:
Prevent null values from being used.
Always use an empty string instead of a null value.
Which statement or statements 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

Answer: ABE

Explanation:
E: First change the NULLs to ' '.
A: Then set the default to the column to ' '.
B: Finally add the NOT NULL constraint to the column.

NEW QUESTION 3

You need to create a function that filters invoices by CustomerID. The SELECT statement for the function is contained in InvoicesByCustomer.sql.
Which code segment should you use to complete the function?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: A

NEW QUESTION 4

You are testing disaster recovery procedures.
You attempt to restore DB1 to a different server and you receive the following error message: "Msg 33111. Level 16, State 3, Line 1
Cannot find server certificate with thumbprint
,0xA694FBEA88C9354E5E2567C30A2A69E8FB4C44A9
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore DB1 to a different server. Which code segment should you execute?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: B

NEW QUESTION 5

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName.
What should you include in the recommendation?

• A. Change data capture
• B. Change tracking
• C. Transactional replication
• D. The Distributed Transaction Coordinator (DTC)

Answer: D

NEW QUESTION 6

You are building a new index for an application.
You need to reduce the storage space used by the index and to minimize logical reads. What should you configure?

• A. SORT_IN_TEMPDB = ON
• B. MAXDOP = 4
• C. PAD_INDEX = OFF
• D. DATA_COMPRESSION = PAGE
• E. DATA_COMPRESSION = ROW

Answer: D

Explanation:
The data compression feature to help reduce the size of the rowstore tables and indexes. It can also help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk.

NEW QUESTION 7

You need to identify which nonclustered indexes are unused by queries.
How should you complete the statement? To answer, drag the appropriate values to the correct locations. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: sys.dm_db_index_usage_stats
sys.dm_db_index_usage_stats shows you how many times the index was used for user queries. It returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.
Box 2: sys.indexes
sys.indexes contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql

NEW QUESTION 8

You are creating a table to support an application that will cache data outside of SQL Server. The application will detect whether cached values were changed before it updates the values. You need to create the table, and then verify that you can insert a row into the table.
Which code segment should you use?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

Explanation:
http://msdn.microsoft.com/en-us/library/ms182776.aspx
http://msdn.microsoft.com/en-us/library/ms187942.aspx http://msdn.microsoft.com/en-us/library/ms190348.aspx

NEW QUESTION 9

Your network contains a SQL Server 2012 instance named SQL1. SQL1 contains a database named DB1. DB1 contains three tables.
The tables are configured as shown in the following table.

You plan to create indexes for the tables.
You need to identify which type of index must be created for each table. The solution must minimize the amount of time required to return information from the tables.
Which type of index should you create for each table? To answer, drag the appropriate index type to the correct table in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 10

You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
•Permit insertions into a table variable.
•Support structured exception handling.
•Prevent changes to the definition of referenced objects.
•Support the use of the APPLY operator on the output of the object. Which type of object should you use?

• A. An inline table-valued function
• B. A stored procedure
• C. A scalar user-defined function
• D. A multi-statement table-valued function

Answer: C

NEW QUESTION 11

You need to add a new column named Confirmed to the Attendees table. The solution must meet the following requirements:
Have a default value of false.
Minimize the amount of disk space used.
Which code block should you use?

• A. ALTER TABLE AttendeesADD Confirmed bit DEFAULT 0;
• B. ALTER TABLE AttendeesADD Confirmed char(l) DEFAULT '1';
• C. ALTER TABLE AttendeesADD Confirmed bit DEFAULT 1;
• D. ALTER TABLE AttendeesADD Confirmed char(l) DEFAULT ‘1’;

Answer: A

Explanation:
http://msdn.microsoft.com/en-us/library/ms177603.aspx

NEW QUESTION 12

You have a SQL Server 2012 database named Database1. Database1 has a data file named Database1_data.mdf and a transaction log named Database1jog.ldf. Database1_data.mdf is 1.5 GB. Database1jog.ldf is 1.5 terabytes.
A full backup of Database1 is performed every day.
You need to reduce the size of the log file. The solution must ensure that you can perform transaction log backups in the future.
Which code segment should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:

NEW QUESTION 13

You need to optimize the index structure that is used by the tables that support the fraud detection services. What should you do?

• A. Add a hashed nonclustered index to CreateDate.
• B. Add a not hash nonclustered index to CreateDate.
• C. Add a not hash clustered index on POSTransactionId and CreateDate.
• D. Add a hashed clustered index on POSTransactionId and CreateDate.

Answer: A

Explanation:
The fraud detection service will need to meet the following requirement (among others):
* Detect micropayments that are flagged with a StatusId value that is greater than 3 and that occurred within the last minute.

NEW QUESTION 14

You have a table named Customers that has a clustered index defined on the ID column. You write a script to create a stored procedure.
You need to complete the script for the stored procedure. The solution must minimize the number of locks and deadlocks.
What should you do?
To answer, drag the appropriate option to the correct location in the answer area. (Answer choices may be used once, more than once, or not at all.)

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Note:
* Optimized bulk load operations on heaps block queries that are running under the following isolation levels: SNAPSHOT
READ UNCOMMITTED
READ COMMITTED using row versioning
* READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
* SERIALIZABLE (more locks) Specifies the following:
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
* UPDLOCK
Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead.
When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored. For example, if the isolation level of the session is set to SERIALIZABLE and a query specifies (UPDLOCK, READCOMMITTED), the READCOMMITTED hint is ignored and the transaction is run using the SERIALIZABLE isolation level.
* XLOCK
Specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.
Reference: Table Hints (Transact-SQL)

NEW QUESTION 15

You need to create the InvoiceStatus table in DB1.
How should you define the InvoiceID column in the CREATE TABLE statement?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

NEW QUESTION 16

You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp_GetOrdersAndItems?

• A. Add SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to line 03.
• B. Add SET TRANSACTION ISOLATION LEVEL SNAPSHOT to line 03.
• C. Add (UPDLOCK) to the end of line 06.
• D. Add (READPAST) to the end of line 06.

Answer: D

NEW QUESTION 17

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

NEW QUESTION 18

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

NEW QUESTION 19

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)

Answer: C

NEW QUESTION 20

You need to implement a solution that addresses the page split issues. Which statement should you execute?

• A. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (PAD_INDEX=OFF, DROP_EXISTING = ON);
• B. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (FILLFACTOR=50, DROP_EXISTING = ON);
• C. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (FILLFACTOR = 0, DROP_EXISTING = ON);
• D. ALTER INDEX IX_Orders_ShipDate ON OrdersREBUILD WITH (PAD_INDEX=ON, DROP_EXISTING = ON);

Answer: B

NEW QUESTION 21

You are designing a database for a university. The database will contain two tables named Classes and StudentGrades that have the following specifications:
Classes will store brochures in the XPS format.
The brochures must be structured in folders and must be accessible byusing UNC paths.
StudentGrades must be backed up on a separate schedule than the rest ofthe database.
You need to identify which SQL Server technology meets the specifications of each table. Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
http://msdn.microsoft.com/en-us/library/gg471497.aspx http://msdn.microsoft.com/en-us/library/ff929144.aspx http://msdn.microsoft.com/en-us/library/ms189563.aspx http://msdn.microsoft.com/en-us/library/ms190174.aspx http://msdn.microsoft.com/en-us/library/ms187956.aspx

NEW QUESTION 22

You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows.
Proc1 updates data in Table1. Proc2 reads data from Table1.
You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1.
You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating.
What should you change Proc1 to do?
More than one answer choice may achieve the goal. Select the BEST answer.

• A. Update less than 4,000 rows simultaneously.
• B. Use the PAGLOCK table hint.
• C. Wait for Proc2 to complete.
• D. Use the ROWLOCK table hint.

Answer: A

NEW QUESTION 23
......