This page was exported from Braindump2go Free Exam Dumps with PDF and VCE Collection [ https://www.mcitpdump.com ] Export date:Mon Nov 25 8:45:38 2024 / +0000 GMT ___________________________________________________ Title: Download Free Microsoft 70-464 Tests Questions and Answers From Braindump2go - 100% Success On Microsoft 70-464 (116-130) --------------------------------------------------- 2015 Microsoft 70-464 Certification Exam is coming! Getting a Laest 70-464 Practice Test is very important for an Exam Candiate! Braindump2go New Updated 70-464 Exam Questions Well Formatted in PDF and VCE versions, providing you convenience and excellence both at the same time! Free Questions and Answer are provided Following: Vendor: Microsoft Exam Code: 70-464 Exam Name: Developing Microsoft SQL Server 2014 Databases Exam  QUESTION 116You 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 @transactionIDB.    COMMIT TRANSACTION @transactionC.    BEGIN TRANSACTION WITH MARK @transactionID D.    ROLLBACK DISTRIBUTED TRANSACTION @transactionID Answer: C QUESTION 117You 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 SERIALIZABLEB.    Add the following line of code to line 05:SET TRANSACTION ISOLATION LEVEL SNAPSHOTC.    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 118You 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 functionB.    A multi-statement table-valued functionC.    A stored procedureD.    A scalar user-defined function Answer: D QUESTION 119Your 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 120You 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_jobB.    sp_add_operatorC.    sp_audit_writeD.    sp_add_alert Answer: BCExplanation: B: sp_add_operatorCreates an operator (notification recipient) for use with alerts and jobs.C: sp_audit_writeAdds a user-defined audit event to the USER_DEFINED_AUDIT_GROUP. If USER_DEFINED_AUDIT_GROUP is not enabled, sp_audit_write is ignored.Incorrect:Not A: sp_syspolicy_create_jobNo such standard stored procedure.Not D: sp_add_alertCreates an alert. QUESTION 121You 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 functionB.    A trigger that uses a table-valued functionC.    A calculated column that uses a table-valued functionD.    A calculated column that uses a scalar function Answer: CExplanation: A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions. Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters.Incorrect:Not A: A scalar function would only be able to use other columns from the same table. QUESTION 122You 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.    ROWGUIDB.    SEQUENCEC.    NEWIDD.    NEWSEQUENTIALID Answer: D QUESTION 123You 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 oneTableB.    CREATE TAB1E ##oneTableC.    CREATE TABLE #oneTableD.    DECLARE goneTable TABLE Answer: D QUESTION 124You 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: AExplanation: Temporary TablesYou can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name) QUESTION 125You 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 WHEREB.    WHERE CONTAINS(notes, 'call*') ANDC.    WHERE FREETEXT(notes, '%call%') ANDD.    WHERE CONTAINS(notes, 'cal%') AND Answer: C QUESTION 126You 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 traceB.    A script that enables trace flagsC.    A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management viewsD.    A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views Answer: AExplanation: 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. QUESTION 127Drag and Drop QuestionYou 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 128Drag and Drop QuestionYour 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 129You 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 viewsB.    A script that enables trace flagsC.    A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management viewsD.    A SQL Server Profiler trace Answer: B QUESTION 130You 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 indexB.    Recreate EmployeeName as a clustered indexC.    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 70-464 Updated Questions are 2015 Latest Released Which 100% will Meet in Your 70-464 Test! Braindump2go New Released 70-464 Exam Dumps Contain All New Added Questions Which Will Help you Have A Totally Success in 2015 New Tear! Download our 100% Pass Guaranteed 70-464 Exam Dumps Full Version, special 10% Off Discount enjoyed! http://www.braindump2go.com/70-464.html --------------------------------------------------- Images: http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/1922.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/wps1C3.tmp_thumb2_thumb.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/wps4B9F.tmp_thumb_thumb.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/wps63B2.tmp_thumb_thumb.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/wps8DCF.tmp_thumb_thumb.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/wpsA610.tmp_thumb_thumb.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/wpsBE81.tmp_thumb_thumb.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/wpsD2DC.tmp_thumb_thumb.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/wpsF73F.tmp_thumb2_thumb.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/wps2699.tmp_thumb2_thumb.png http://www.itexamquiz.com/braindump2go/bdimages/fa6c89dc10d7_E1E0/1522.png --------------------------------------------------- --------------------------------------------------- Post date: 2015-03-03 08:04:51 Post date GMT: 2015-03-03 08:04:51 Post modified date: 2015-03-03 08:04:51 Post modified date GMT: 2015-03-03 08:04:51 ____________________________________________________________________________________________ Export of Post and Page as text file has been powered by [ Universal Post Manager ] plugin from www.gconverters.com