70-464 | You need to recommend a solution….

Question: 22

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName. What should you include in the recommendation?

A. Change data capture
B. Change tracking
C. Transactional replication
D. The Distributed Transaction Coordinator (DTC)

Answer: D

70-464|You have a text file that contains an XML Schema Definition (XSD).

Question: 21

You have a text file that contains an XML Schema Definition (XSD).
You have a table named Schemal.Table1.
You have a stored procedure named Schemal.Proc1 that accepts an XML parameter named Param1.
You need to store validated XML data in Schemal.Table1.
The solution must ensure that only valid XML data is accepted by Param1.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A. Define an XML column in Table1 by using an XML schema collection.
B. Create an XML schema collection in the database from the text file.
C. Declare Param1 var1 as type XML and associate the variable to the XML schema collection.
D. use the modify method to insert the XML schema into each row of the XML column in Table1.

Answer: A,B,D

70-464 | You are evaluating the index design.You need to recommend a change to Indexes.sql that will minimize the amount of time it takes for usp_AttendeesReport to execute.

Question : 20

You are evaluating the index design.You need to recommend a change to Indexes.sql that will minimize the amount of time it takes for usp_AttendeesReport to execute. The solution must minimize the amount of database frag- mentation.Which line of code should you use to replace line 12 of Indexes.sql?

A. (LastName);
B. (FirstName) INCLUDE (LastName);
C. (LastName, FirstName);
D. (LastName) INCLUDE (FirstName);

Answer : C

70-464 | You discover that usp.SelectSpeakersByName executes slowly if usp_UpdateSpeakerName executes simultaneously.You need to minimize the execution time of usp.SelectSpeakersByName.

Question : 19

You discover that usp.SelectSpeakersByName executes slowly if usp_UpdateSpeakerName executes simultaneously.You need to minimize the execution time of usp.SelectSpeakersByName. The solution must not affect the performance of the other stored procedures.What should you update?

A. Usp_UpdateSpeakerName to use the NOLOCK query hint
B. Usp_UpdateSpeakerName to use snapshot isolation
C. Usp_SelectSpeakersByName to use the NOLOCK query hint
D. Usp_SelectSpeakersByName to use snapshot isolation

Answer : C

70-464 |Developers report that usp_UpdateSessionRoom periodically returns error 3960.You need to prevent the error from occurring.

Question : 18

Developers report that usp_UpdateSessionRoom periodically returns error 3960.You need to prevent the error from occurring.The solution must ensure that the stored procedure returns the original values to all of the updated rows.What should you configure in Procedures.sql?

A. Replace line 46 with the following code:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. Replace line 46 with the following code:SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
C. Move the SELECT statement at line 49 to line 57.
D. Move the SET statement at line 46 to line 53.

Answer : A

70-464 Sample Question : 16

Question : 16

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 : A

70-464 Sample Question : 15

Question : 15

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 : B

70-464 Sample Question : 14

Question : 14

You are evaluating the table design.You need to recommend a change to Tables.sql that reduces the amount of time it takes for usp_AttendeesReport to execute.What should you add at line 14 of Tables.sql?

A. FullName nvarchar(100) NOT NULL CONSTRAINT DF_FuIlName DEFAULT (dbo.CreateFullName (FirstName, LastName)),
B. FullName AS (FirstName +` ‘+ LastName),
C. FullName nvarchar(100) NOT NULL DEFAULT (dbo.CreateFullName (FirstName, LastName)).
D. FullName AS (FirstName +` ‘+ LastName) PERSISTED,

Answer : D

70-464 Sample Question : 13

Question : 13

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName.What should you include in the recommendation?

A. The Distributed Transaction Coordinator (DTC)
B. Transactional replication
C. Change data capture
D. Change tracking

Answer : A

70-464 Sample Question : 12

Question : 12

You execute usp_TestSpeakers.You discover that usp_SelectSpeakersByName uses inefficient execution plans.You need to update usp_SelectSpeakersByName to ensure that the most efficient execution plan is used.What should you add at line 30 of Procedures.sql?

A. OPTION (FORCESCAN)
B. OPTION (FORCESEEK)
C. OPTION (OPTIMIZE FOR UNKNOWN)
D. OPTION (OPTIMIZE FOR (@LastName= ‘Anderson’))

Answer : C

70-464 Sample Question : 11

Question : 11

You execute IndexManagement.sql and you receive the following error message:”Msg 512, Level 16, State 1, Line 12 Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =, <, <= ,>, > = or when the subquery is used as an expression.”You need to ensure that IndexManagement.sql executes properly.Which WHILE statement should you use at line 18?

A. WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)
B. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)
C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
D. WHILE @counter < (SELECT SUM(RowNumber) FROM @indextabie)

Answer : B

70-464 Sample Question : 10

Question : 10

You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID.Which line of code should you modify in CountryFromID.sql?

A. 04
B. 05
C. 06
D. 19

Answer : D

70-464 Sample Question : 9

Question : 9

You attempt to process an invoice by using usp_InsertInvoice.sql and you receive the following error message: “Msg 515, Level 16, State 2, Procedure usp_InsertInvoice, Line 10 Cannot insert the value NULL into column ‘InvoiceDate’, table ‘DB1.Accounting.Invoices’; column does not allow nulls. INSERT fails.”You need to modify usp_InsertInvoice.sql to resolve the error.How should you modify the INSERT statement?

A. InvoiceDate varchar(l00) ‘InvoiceDate’,
B. InvoiceDate varchar(100) ‘Customer/InvoiceDate’, ‘
C. InvoiceDate date ‘@InvoiceDate’,
D. InvoiceDate date ‘Customer/@InvoiceDate’,

Answer : C

70-464 Sample Question : 8

Question : 8

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

70-464 Sample Question : 7

Question : 7

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

Search Words: 70-464 Exam real exam questions answers practice test braindumps, free Q&A online dumps download and free 70-464 Exam discount coupon code available.

Leave a Comment.

12 − two =