Get New Valid Dumps To Pass Exam 70-464: The following new 70-464 exam questions were updated in recent days by PassLeader, visit passleader.com to get the full version of new 70-464 153q exam dumps with free version of new VCE Player software, our valid 70-464 153q briandump will help you passing 70-464 exam easily!
keywords: 70-464 exam,70-464 exam dumps,70-464 153q exam questions,70-464 pdf dumps,70-464 vce dumps,70-464 153q braindump,Developing Microsoft SQL Server 2014 Databases Exam
QUESTION 111
Drag and Drop Question
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.
QUESTION 112
Drag and Drop Question
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:
QUESTION 113
Drag and Drop Question
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 by using UNC paths.
– StudentGrades must be backed up on a separate schedule than the rest of the 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:
QUESTION 114
Drag and Drop Question
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.
Answer:
QUESTION 115
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. Use the ROWLOCK table hint.
B. Wait for Proc2 to complete.
C. Update less than 4,000 rows simultaneously.
D. Use the PAGLOCK table hint.
Answer: C
QUESTION 116
You need to implement a solution that meets the data recovery requirements. You update each stored procedure to accept a parameter named @transactionID. What should you add next to the beginning of each stored procedure?
A. SAVE TRANSACTION WITH MARK @transactionID
B. COMMIT TRANSACTION @transaction
C. BEGIN TRANSACTION WITH MARK @transactionID
D. ROLLBACK DISTRIBUTED TRANSACTION @transactionID
Answer: C
QUESTION 117
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 SERIALIZABLE
B. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
C. Add the following line of code to line 13:
WITH (UPDLOCK)
D. Add the following line of code to line 08:
WITH (UPDLOCK)
Answer: D
QUESTION 118
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 multi-statement table-valued function
C. A stored procedure
D. A scalar user-defined function
Answer: D
QUESTION 119
Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1 contains a database name DB1 and a table named Customers. You add an additional server named SQL2 that runs SQL Server 2012. You need to create a distributed partitioned view. The solution must minimize the amount of network traffic. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Add SQL2 as a Distributor.
B. Add the Customers table to SQL2.
C. Add SQL2 as a linked server.
D. Create the view on SQL1.
E. Remove the Customers table from SQL1.
F. Create the view on SQL2.
Answer: BCDF
QUESTION 120
You plan to migrate an instance of SQL Server 2008 to a new installation of SQL Server 2012. You need to migrate alerts and e-mail notifications. Which system stored procedures should you use? (Each correct answer presents part of the solution. Choose all that apply.)
A. sp_syspolicy_create_job
B. sp_add_operator
C. sp_audit_write
D. sp_add_alert
Answer: BC
http://www.passleader.com/70-464.html
QUESTION 121
You have a table named Table1 that stores customer data. Each customer has a credit limit that can only be discovered by querying multiple tables. You need to ensure that the value of the credit limit is returned by executing a query on Table1. What should you create?
A. A trigger that uses a ranking function
B. A trigger that uses a table-valued function
C. A calculated column that uses a table-valued function
D. A calculated column that uses a scalar function
Answer: C
QUESTION 122
You plan to create a new table that has the following requirements:
– Uses a GUID data type as the primary key.
– Uses a clustered index as the primary key.
– Minimizes fragmentation.
You need to recommend which option to include in the CREATE statement. Which option should you include? More than one answer choice may achieve the goal. Select the BEST answer.
A. ROWGUID
B. SEQUENCE
C. NEWID
D. NEWSEQUENTIALID
Answer: D
QUESTION 123
You are creating a stored procedure named usp1. Usp1 will create a table that will be used during the execution of usp1. Only usp1 will be allowed to access the table. You need to write the code required to create the table for usp1. The solution must minimize the need to recompile the stored procedure. Which code segment should you use to create the table?
A. CREATE TABLE oneTable
B. CREATE TAB1E ##oneTable
C. CREATE TABLE #oneTable
D. DECLARE goneTable TABLE
Answer: B
QUESTION 124
You plan to modify a stored procedure to use temporary data. The stored procedure must meet the following requirements:
– Favor physical memory when physical memory is available.
– Be able to roll back changes to the temporary data.
You need to recommend which object to add to the stored procedure. Which T-SQL command should you recommend?
A. CREATE TABLE ##Table…
B. CREATE TABLE Table…
C. CREATE VIEW Table…
D. CREATE PARTITION SCHEME Table…
E. DECLARE TABLE @ Table…
Answer: A
QUESTION 125
You have a database named database1. You execute the following code:
You have the following query. (Line numbers are included for reference only.)
Users report that the query takes a long time to complete. You create a full-text index for the Notes column. You need to recommend changes to the query to reduce the amount of time it takes for the query to complete. Which code segment should you use to replace line 03?
A. INNER JOIN FREETEXTTABLE(dbo.table1, notes, ‘call’) AS t2 ON dbo.table1.ID = t2.key WHERE
B. WHERE CONTAINS(notes, ‘call*’) AND
C. WHERE FREETEXT(notes, ‘%call%’) AND
D. WHERE CONTAINS(notes, ‘cal%’) AND
Answer: C
QUESTION 126
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_session_transactions dynamic management views
D. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
Answer: A
QUESTION 127
Drag and Drop Question
You have a SQL Server 2012 database named database1. Users report that queries that usually take less than one second to execute, take more than 30 seconds to execute. You need to view the server resource consumption when the queries are executed. What should you do? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
QUESTION 128
Drag and Drop Question
Your network contains a server named Server1 that runs SQL Server 2012. Server1 contains an instance named Instance1. Instance1 contains a database named ContentDatabase. ContentDatabase uses transaction log backups. The recovery model of ContentDatabase is set to FULL. You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log. Which three code segments 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.
Answer:
QUESTION 129
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 minimize the impact on the server. What should you create?
A. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
B. A script that enables trace flags
C. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
D. A SQL Server Profiler trace
Answer: B
QUESTION 130
You have a Microsoft SQL Azure database that contains a table named Employees.
You create a non-clustered index named EmployeeName on the name column. You write the following query to retrieve all of the employees that have a name that starts with the letters JOH:
You discover that the query performs a table scan. You need to ensure that the query uses EmployeeName. What should you do?
A. Recreate EmployeeName as a unique index
B. Recreate EmployeeName as a clustered index
C. Replace LEFT(name,3) = ‘JOH’ by using name like ‘JOH%’
D. Replace LEFT(name,3) = ‘JOH’ by using substring(name, 1, 3) = ‘JOH’
Answer: C