Top 15 study guides 70-464 for IT specialist (46 to 60)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-464 Exam Dumps (PDF & VCE):
Available on: https://www.certleader.com/70-464-dumps.html


70-464 Product Description:
Exam Number/Code: 70-464 vce
Exam name: Developing Microsoft SQL Server 2012 Databases
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Instant Access to Free VCE Files: Microsoft 70-464 Developing Microsoft SQL Server 2012 Databases

70-464 examcollection

Question No. 46

You have a Microsoft SQL Azure database. 

You have the following stored procedure: 

You discover that the stored procedure periodically fails to update HR.Employees. 

You need to ensure that HR.Employees is always updated when up_employees executes. 

The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held. 

What should you do? 

A. Add the following line of code to line 05: 

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

B. Add the following line of code to line 13: 

WITH (UPDLOCK) 

C. Add the following line of code to line 05: 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

D. Add the following line of code to line 08: 

WITH (UPDLOCK) 

Answer:


Question No. 47

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 Attendees 

ADD Confirmed bit DEFAULT 0; 

B. ALTER TABLE Attendees 

ADD Confirmed char(l) DEFAULT '1' 

C. ALTER TABLE Attendees 

ADD Confirmed bit DEFAULT 1; 

D. ALTER TABLE Attendees 

ADD Confirmed char(l) DEFAULT ‘1’; 

Answer:

Explanation: 

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


Question No. 48

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? 

A. A table variable 

B. An OFFSET-FETCH clause 

C. The ROWNUMBER keyword 

D. A recursive common table expression 

Answer:

Explanation: 

http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-serverpaging-methods/ http://msdn.microsoft.com/en-us/library/ms188385.aspx http://msdn.microsoft.com/en-us/library/ms180152.aspx http://msdn.microsoft.com/en-us/library/ms186243.aspx http://msdn.microsoft.com/en-us/library/ms186734.aspx http://www.sqlserver-training.com/how-to-use-offset-fetch-option-in-sql-server-order-byclause/-http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/11/30/using-offset-and-fetch/ 


Question No. 49

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? 

A. BACKUP LOG DB1 TO Device1 WITH COPY_ONLY 

B. BACKUP LOG DB1 TO Device1 WITH NORECOVERY 

C. BACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY 

D. BACKUP LOG DB1 TO Device1 

Answer:

Explanation: 

http://msdn.microsoft.com/en-us/library/ms186865.aspx http://msdn.microsoft.com/en-us/library/ms179478.aspx http://msdn.microsoft.com/en-us/library/ms190925.aspx 


Question No. 50

Topic 8) 

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 that uses two-way encryption. 

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. 

Answer: 


Question No. 51

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:


Question No. 52

Topic 7) 

You need to create the usp.AssignUser stored procedure. 

Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks. 

Answer: 


Question No. 53

You need to implement a solution that meets the job application requirements. What should you do? 

A. Create a one-to-one relationship between the Openings table and the Applications table. 

B. Create a one-to-one relationship between the Candidates table and the Applications table. 

C. Add a UNIQUE constraint to the Applications table on the ApplicationID column and CandidateID column. 

D. Add a UNIQUE constraint to the Applications table on the OpeningID column and the CandidateID column. 

Answer:


Question No. 54

You have a SQL Server 2012 database that contains a table named Users. The Users table contains usernames and passwords. 

You need to ensure that all new records have a password. 

Which code segment should you use? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:


Question No. 55

You plan to design an application that temporarily stores data in a SQL Azure database. 

You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session. 

Which type of objects should you identify? 

A. Common table expressions (CTEs) 

B. Temporary stored procedures 

C. Temporary tables 

D. Table variables 

Answer:

Explanation: 

http://msdn.microsoft.com/en-us/library/ms175972.aspx http://msdn.microsoft.com/en-us/library/ms189084.aspx http://msdn.microsoft.com/en-us/library/ms175010.aspx http://msdn.microsoft.com/en-us/library/bb510489.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx http://zacksfiasco.com/post/2010/01/21/SQL-Server-Temporary-Stored-Procedures.aspx 


Question No. 56

Topic 8) 

You are a SQL Server 2014 Developer. A database that you work on contains two tables that are defined as follows: 

Product is an important table that has sensitive audit requirements. 

You need to create a trigger that supports the following requirements: 

1. 

Every row that is inserted or updated in Product will reflect its actual LastUpdatedDate and LastUpdatedBy values in the Product table. 

2. 

Any row that is updated or deleted must write a new record reflecting the OLD values into the ProductAudit table. 

3. 

Any error that occurs during the course of the trigger's execution must prevent the changes from happening. 

Develop the solution by selecting and arranging the required code blocks in the correct order. 

You may not need all of the code blocks. 

Answer: 


Question No. 57

Topic 8) 

You plan to create a custom aggregation function named Median. 

You plan to deploy Median to a SQL Server 2014 server named Server1. 

You need to ensure that Median can access a web service named WebApp1. The solution 

must minimize the number of changes made to the database. 

You create a Microsoft .NET Framework class that contains the definition of Median. 

You upload a certificate to Server1. 

What three tasks should you perform next? 

To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. 

Answer: 


Question No. 58

You need to convert the functionality of Legacy.sql to use a stored procedure. Which code segment should the stored procedure contain? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer:

Explanation: 

http://msdn.microsoft.com/en-us/library/ms187926.aspx http://msdn.microsoft.com/en-us/library/ms190782.aspx http://msdn.microsoft.com/en-us/library/bb669091.aspx http://msdn.microsoft.com/en-us/library/windows/desktop/ms709342.aspx http://msdn.microsoft.com/en-us/library/ms188001.aspx 


Question No. 59

You have an application that uses a view to access data from multiple tables. 

You need to ensure that you can insert rows into the underlying tables by using the view. 

What should you do? 

A. Create an INSTEAD OF trigger on the view. 

B. Define the view by using the SCHEMABINDING option. 

C. Define the view by using the CHECK option. 

D. Materialize the view. 

Answer:

Explanation: 

http://msdn.microsoft.com/en-us/library/ms180800.aspx http://msdn.microsoft.com/en-us/library/ms187956.aspx 


Question No. 60

You need to modify the usp_DetectSuspiciousActivity stored procedure. 

Which two actions should you perform? Each correct answer presents part of the solution. Choose two. 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

F. Option F 

Answer: D,E 

Explanation: 

Note: 

* Move micropayments to dbo.POSException table by using a stored procedure named ups_DetectSuspiciousActivity.