Microsoft 70-464 Exam Questions Instant Access

Question 1

You need to implement a solution that meets the locking requirements.

Which line of code should you modify?

AChange line 07 in usp_UpdateOpening to:
UPDATE Openings WITH (UPDLOCK)

BChange line 09 in usp_GetOpenings to:
FROM Openings o (ROWLOCK)

CChange line 07 in usp_UpdateOpening to:
UPDATE Openings WITH (READPAST)

DChange line 09 in usp_GetOpenings to:
FROM Openings o (NOLOCK)

Answer : D

Question 2

You have a SQL Server 2012 database named Database1.

Database1 has a table named Customers. Customers contains more than 1 million rows.

The database has a stored procedure that was created by using the following script:

You need to ensure that up_customers returns rows when the following statement is executed:

EXECUTE up_customers'1,2,3,4,5';

What should you do?

AUpdate @CustcmerTypelist to use the int data type.

BConvert @CustomerTypeList to a table variable.

CConvert @CustomerTypeList to an XML variable.

DUpdate @CustomerTypeList to use the XML data type.

Answer : B

Question 3

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?

AFullName nvarchar(100) NOT NULL CONSTRAINT DF_FuIlName DEFAULT (dbo.CreateFullName (FirstName, LastName)),

BFullName AS (FirstName +' '+ LastName),

CFullName nvarchar(100) NOT NULL DEFAULT (dbo.CreateFullName (FirstName, LastName)).

DFullName AS (FirstName +' '+ LastName) PERSISTED,

Answer : D

http://msdn.microsoft.com/en-us/library/ms188300.aspx

http://msdn.microsoft.com/en-us/library/ms191250.aspx

Question 4

You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2.

Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table.

Schema1.Udf1 was defined by using the following code:

You need to write a query that will contain the following columns:

*Country

*CountryID

*CustomerName

The solution must meet the following requirements:

*Rows must be returned only if the function returns data.

*The amount of time it takes the query to execute must be minimized.

Which query should you use?

AOption A

BOption B

COption C

DOption D

Answer : B

Question 5

You need to provide referential integrity between the Offices table and Employees table.

Which code segment or segments should you add at line 27 of Tables.sql? (Each correct answer presents part of the solution. Choose all that apply.)

AOption A

BOption B

COption C

DOption D

Answer : C, D

http://msdn.microsoft.com/en-us/library/ms189049.aspx

Question 6

You run the following code:

You need to ensure that the root node of the XML data stored in the Details column is .

What should you implement?

More than one answer choice may achieve the goal. Select the BEST answer.

AA user-defined data type

BAn XML index

CA Data Definition Language (DDL) trigger

DA data manipulation language (DML) trigger

EAn XML schema collection

Answer : E

http://msdn.microsoft.com/en-us/library/ms187856.aspx

Question 7

You need to implement a solution that addresses the index monitoring requirements.

What should you do?

ASchedule a SQL Server Agent job that saves data from the dynamic management views to a table in the database.

BCreate a SQL Server Audit that saves data to a log file, and then create a SQL Server Audit Specification that gathers data from the DATABASE_OPERATION group.

CCreate a performance monitor Data Collector Set (DCS) that monitors the SQL Server counters.

DSchedule a SQL Server Profiler trace, and then save the trace data to a table in the database.

Answer : A

Microsoft Developing Microsoft SQL Server Databases 70-464 Exam Questions