The Secret of 70-464 exam

It is more faster and easier to pass the Microsoft 70-464 exam by using Precise Microsoft Developing Microsoft SQL Server 2012 Databases questuins and answers. Immediate access to the Down to date 70-464 Exam and find the same core area 70-464 questions with professionally verified answers, then PASS your exam with a high score now.

Q31. 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 


Q32. You need to implement security for the restore and audit process. What should you do? 

A. Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions. 

B. Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions. 

C. Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions. 

D. Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions. 

Answer:


Q33. Topic 8) 

You plan to deploy SQL Server 2012. You must create two tables named Table1 and Table2 that will have the following specifications: 

. Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time. . Table2 will contain a column named Column2 that is the product of two other columns in Table2. . Both Table1 and Table2 will contain more than 1 million rows. 

You need to recommend which options must be defined for the columns. The solution must minimize the storage requirements for the tables. Which options should you recommend? To answer, drag the appropriate options to the correct column in the answer area. 

Answer: 


Q34. 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 


Q35. You execute the following code: The execution plan for the query is shown in the exhibit. (Click the Exhibit button.) 

You need to prevent the key lookup. What should you modify? 

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:


Q36. Topic 8) 

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. 

Answer: 


Q37. 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:


Q38. Topic 8) 

You execute the following code: 

You discover that the Customers table was created in the dbo schema. 

You need to create a code segment to move the table to another schema named 

Schema2. 

What should you create? 

To answer, drag the appropriate code segments to the correct location in the answer area. 

 (Answer choices may be used once, more than once, or not at all.) 

Answer: 


Q39. You have a database that is accessed by 300 concurrent users. 

You need to log all of the queries 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? 

A. A SQL Server Profiler trace 

B. A script that enables trace flags 

C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views 

D. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views 

Answer:

Explanation: Analyze Deadlocks with SQL Server Profiler Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis. To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. 


Q40. You need to implement a solution that meets the security requirements. Which statement should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: