[2021-New] Microsoft 70-464 Dumps With Update Exam Questions (61-70)

70-464 Dumps

70-464 Exam Questions - Online Test

70-464 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. You need to create the object used by the parameter of usp_UpdateEmployeeName.

Which code segment should you use?

A. CREATE XML SCHEMA COLLECTION EmployeesInfo

B. CREATE TYPE EmployeesInfo AS Table

C. CREATE SCHEMA EmployeesInfo

D. CREATE TABLE EmployeesInfo

Answer: B

Explanation:

Example Usage of Table-Valued Parameters (Database Engine) http://msdn.microsoft.com/en-us/library/bb510489.aspx (Benefits of using Table-Valued Parameters)

/* Create a table type. */

CREATE TYPE LocationTableType AS TABLE

( LocationName VARCHAR(50)

, CostRate INT );

/* Create a procedure to receive data for the table-valued parameter. */

CREATE PROCEDURE dbo. usp_InsertProductionLocation

@TVP LocationTableType READONLY

SET NOCOUNT ON

INSERT INTO AdventureWorks2012.Production.Location

(Name

,CostRate

,Availability

,ModifiedDate)

SELECT *, 0, GETDATE()

FROM @TVP;

Also:

http://msdn.microsoft.com/en-us/library/ms175007.aspx(CREATE TYPE *tabletypename*

TABLE)

http://msdn.microsoft.com/en-us/library/ms175010.aspx(table data types)

Wrong Answers:

http://msdn.microsoft.com/en-us/library/ms174979.aspx(CREATE TABLE)

http://msdn.microsoft.com/en-us/library/ms189462.aspx(CREATE SCHEMA)

http://msdn.microsoft.com/en-us/library/ms176009.aspx(CREATE XML SCHEMA

COLLECTION)

Q2. You need to implement a solution that resolves the salary query issue. Which statement should you execute on DB1?

A. Option A B. Option B

C. Option C

D. Option D

Answer: A

Q3. Topic 7)

You need to redesign the system to meet the scalability requirements of the application.

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:

76. You need to modify the stored procedure usp_LookupConcurrentUsers.

What should you do?

A. Add a clustered index to the summary table.

B. Add a nonclustered index to the summary table.

C. Add a clustered columnstore index to the summary table.

D. Use a table variable instead of the summary table.

Q4. Topic 8)

You have a table named Table1. Table1 has 1 million rows.

Table1 has a columnstore index for a column named Column1.

You need to import data to Table1. The solution must minimize the amount of time it takes

to import the data.

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:

Q5. 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: B,C,D,F

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

Topic 3, Scenario 3

Application Information

You have two servers named SQL1 and SQL2. SQL1 has SQL Server 2012 Enterprise installed. SQL2 has SQL Server 2008 Standard installed.

You have an application that is used to manage employees and office space. Users report that the application has many errors and is very slow.

You are updating the application to resolve the issues. You plan to create a new database on SQL1 to support the application. The script that you plan to use to create the tables for the new database is shown in Tables.sql. The script that you plan to use to create the stored procedures for the new database is shown in StoredProcedures.sql. The script that you plan to use to create the indexes for the new database is shown in Indexes.sql. A database named DB2 resides on SQL2. DB2 has a table named EmployeeAudit that will audit changes to a table named Employees.

A stored procedure named usp_UpdateEmployeeName will be executed only by other stored procedures. The stored procedures executing usp_UpdateEmp!oyeeName will always handle transactions.

A stored procedure named usp_SelectEmployeesByName will be used to retrieve the names of employees. Usp_SelectEmployeesByName can read uncommitted data.

A stored procedure named usp_GetFutureOfficeAssignments will be used to retrieve office assignments that will occur in the future.

StoredProcedures.sql

Indexes.sql Tables.sql

Q7. You need to create a function that filters invoices by CustomerID. The SELECT statement for the function is contained in InvoicesByCustomer.sql.

Which code segment should you use to complete the function?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: A

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

Explanation: NOLOCK

Is equivalent to READUNCOMMITTED.

READUNCOMMITTED

Specifies that dirty reads are allowed.

Q9. 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. NEWID

B. @@IDENTITY

C. NEWSEQUENTIALID

D. IDENTITY

Answer: C

Q10. The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values.

You need to update Fax to meet the following requirements:

. Prevent null values from being used.

. Always use an empty string instead of a null value.

Which statement or statements should you execute? (Each correct answer presents part of the solution. Choose all that apply.)

A. Option A

B. Option B

C. Option C

D. Option D

E. Option E

Answer: A,B,E

Explanation: E: First change the NULLs to ' '.

A: Then set the default to the column to ' '.

B: Finally add the NOT NULL constraint to the column.