70-464 Exam
Highest Quality 70-464 tutorials Reviews & Tips
Act now and download your Microsoft 70-464 test today! Do not waste time for the worthless Microsoft 70-464 tutorials. Download Update Microsoft Developing Microsoft SQL Server 2012 Databases exam with real questions and answers and begin to learn Microsoft 70-464 with a classic professional.
Q71. Topic 8)
You have a database named database1. Each table in database1 has one index per column.
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
. Identify unused indexes.
. Identify indexes that need to be defragmented. What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)
Answer:
Q72. You have a database hosted on SQL Azure.
You are developing a script to create a view that will be used to update the data in a table.
The following is the relevant portion of the script. (Line numbers are included for reference
only.)
You need to ensure that the view can update the data in the table, except for the data in Column1.
Which code segment should you add at line 06?
A. WITH CHECK OPTION
B. WITH VIEW_METADATA
C. WITH ENCRYPTION
D. WITH SCHEMABINDING
Answer: A
Explanation:
The question concerning the view that has a clause "WHERE Column1 = 'City1' is wrong.
That's not what the CHECK option is made for. Actually you will be able to update ONLY
the rows satisfied by that WHERE clause, that is, only the rows with the Column1 being
'City1'.
None of the answers are valid from that question. You need a trigger to achieve that.
http://msdn.microsoft.com/en-us/library/ms187956.aspx
Q73. 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 );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO AdventureWorks2012.Production.Location
(Name
,CostRate
,Availability
,ModifiedDate)
SELECT *, 0, GETDATE()
FROM @TVP;
GO
Also:
http://msdn.microsoft.com/en-us/library/ms175007.aspx(CREATE TYPE *tabletypename*
AS
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)
Q74. You need to modify Production.ProductDetails_Insert to comply with the application requirements.
Which code segment should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/bb669102.aspx
Q75. 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.
Answer: A
Explanation: Scenario: Query the current open micropayments for users who own multiple micropayments by using a stored procedure named usp.LookupConcurrentUsers
Topic 8, Mix Questions
Q76. You need to modify usp.GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp.GetOrdersAndItems?
A. Add WITH (NOLOCK) to the end of line 47.
B. Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED to line 44.
C. Add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44.
D. Add WITH (READPAST) to the end of line 47.
Answer: B
Q77. 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 SNAPSHOT
B. Add the following line of code to line 13:
WITH (UPDLOCK)
C. Add the following line of code to line 05:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
D. Add the following line of code to line 08:
WITH (UPDLOCK)
Answer: D
Q78. Topic 8)
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:
Q79. Topic 8)
You have a SQL Server 2012 database named Database1. Database1 has a data file named
database1_data.mdf and a transaction log file named database1_Log.ldf. Database1_Data.mdf is
1.5 GB.
Database1_Log.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.
Answer:
Q80. You have the following query on a disk-based table:
You discover that the query takes a long time to complete.
The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)
The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)
You need to reduce the amount of time it takes to complete the query. You must achieve this goal as quickly as possible.
What should you do?
A. Reorganize the index.
B. Update statistics.
C. Create an index on LastName.
D. Rebuild the index.
Answer: C