Microsoft Developing Microsoft SQL Server Databases - 70-464 Exam Practice Test
You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required.
What should you add to usp_SelectSpeakersByName?
What should you add to usp_SelectSpeakersByName?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You need to ensure that usp_AddXMLOrder can be used to validate the XML input from the retailers.
Which parameters should you add to usp_AddXMLOrder on line 04 and line 05? (Each correct answer presents part of the solution. Choose all that apply.)
Which parameters should you add to usp_AddXMLOrder on line 04 and line 05? (Each correct answer presents part of the solution. Choose all that apply.)
Correct Answer: C,E
Vote an answer
You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table.
After the failover is complete, a user receives the following error message when connecting to DB1 on Server2:
"Msg 916, Level 14, State 1, Line 1
The server principal "Account1" is not able to access the database "DB1" under the current security context." You verify that there is a server login for Account1 on Server2.
You need to ensure that Account1 can connect to DB1.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
After the failover is complete, a user receives the following error message when connecting to DB1 on Server2:
"Msg 916, Level 14, State 1, Line 1
The server principal "Account1" is not able to access the database "DB1" under the current security context." You verify that there is a server login for Account1 on Server2.
You need to ensure that Account1 can connect to DB1.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: A
Vote an answer
While testing usp.GetFutureSessions, you discover that IX_Sessions is accessed by a scan rather than a seek.
You need to minimize the amount of time it takes to execute usp_GetFutureSessions.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
You need to minimize the amount of time it takes to execute usp_GetFutureSessions.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
Correct Answer: A,F
Vote an answer
You have a table named Rooms that contains three columns.
You execute the following query:
You discover the execution plan shown in the exhibit. (Click the Exhibit button.)
You need to recommend a solution to reduce the amount of time it takes to execute the query.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
You execute the following query:
You discover the execution plan shown in the exhibit. (Click the Exhibit button.)
You need to recommend a solution to reduce the amount of time it takes to execute the query.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: C
Vote an answer
You need to provide referential integrity between the Offices table and Employees table.
Which code segment or segments should you add at line 27 of Tables.sql? (Each correct answer presents part of the solution. Choose all that apply.)
Which code segment or segments should you add at line 27 of Tables.sql? (Each correct answer presents part of the solution. Choose all that apply.)
Correct Answer: C,D
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You have a SQL Server 2012 database named DB1. You have a backup device named Device1.
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
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.
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.
Correct Answer:
Explanation:
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:
Ref: http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/
You execute the following code:
You create a nonclustered index named IX_CustomerName on the CustomerName column.
You execute the following query:
You need to reduce the amount of time it takes to execute the query.
What should you do?
You create a nonclustered index named IX_CustomerName on the CustomerName column.
You execute the following query:
You need to reduce the amount of time it takes to execute the query.
What should you do?
Correct Answer: C
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users.
You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?
You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements:
Provide a representation of the deadlock in XML format.
Minimize the impact on the server.
What should you create?
Correct Answer: A
Vote an answer
You need to convert the functionality of Legacy.sql to use a stored procedure.
Which code segment should the stored procedure contain?
Which code segment should the stored procedure contain?
Correct Answer: C
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You need to add a new column named Confirmed to the Employees table. The solution must meet the following requirements:
Have a default value of TRUE.
Minimize the amount of disk space used.
Which code segment should you use?
Have a default value of TRUE.
Minimize the amount of disk space used.
Which code segment should you use?
Correct Answer: C
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You use SQL Server 2012 to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site.
You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data.
Which transaction isolation level should you use in sp1?
You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site.
You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data.
Which transaction isolation level should you use in sp1?
Correct Answer: B
Vote an answer
You have a SQL Azure database named Database1. You need to design the schema for a table named table1.
Table1 will have less than one million rows. Table1 will contain the following information for each row:
The solution must minimize the amount of space used to store each row. Which data types should you recommend for each column? To answer, drag the appropriate data type to the correct column in the answer area.
Table1 will have less than one million rows. Table1 will contain the following information for each row:
The solution must minimize the amount of space used to store each row. Which data types should you recommend for each column? To answer, drag the appropriate data type to the correct column in the answer area.
Correct Answer:
Explanation:
http://msdn.microsoft.com/en-US/library/ms187752.aspx