No doubt that 70-464 exam is a worth challenging task but you should not feel hesitant against the confronting difficulties. GreatExam is supplying the new version of 70-464 VCE dumps now. Get a complete hold on 70-464 exam syllabus through GreatExam and boost up your skills. What’s more, the 70-464 dumps are the latest. It would be great helpful to your 70-464 exam.
QUESTION 81
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.)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: CD
Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx
QUESTION 82
You execute usp_SelectEmployeesByName multiple times, passing strings of varying lengths to @>LastName.
You discover that usp_SelectEmployeesByName uses inefficient execution plans.
You need to update usp_SelectEmployeesByName to ensure that the most efficient execution plan is used.
What should you add at line 31 of StoredProcedures.sql?
A. OPTION (KEEPFIXED PLAN)
B. OPTION (KEEP PLAN)
C. OPTION (ROBUST PLAN)
D. OPTION (OPTIMIZE FOR UNKNOWN)
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms181714.aspx
QUESTION 83
You need to add a new column named Confirmed to the Employees table.
The solution must meet the following requirements:
– Have a default value of TRUE.
– Minimize the amount of disk space used.
Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
QUESTION 84
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. transactional replication
C. change tracking
D. the Distributed Transaction Coordinator (DTC)
Answer: D
QUESTION 85
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 TABLE EmployeesInfo
C. CREATE TYPE EmployeesInfo AS Table
D. CREATE SCHEMA EmployeesInfo
Answer: C
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)
Case Study 5: Litware Inc (Question 86 ~ Question 96)
General Overview
You are a database developer for a company named Litware, Inc.
Litware has a main office in Miami.
Litware has a job posting web application named WebApp1.
WebApp1 uses a database named DB1. DB1 is hosted on a server named Server1.
The database design of DB1 is shown in the exhibit. (Click the Exhibit button.)
WebApp1 allows a user to log on as a job poster or a job seeker.
Candidates can search for job openings based on keywords, apply to an opening, view their application, and load their resume in Microsoft Word format.
Companies can add a job opening, view the list of candidates who applied to an opening, and mark an application as denied.
Users and Roles
DB1 has five database users named Company, CompanyWeb, Candidate, CandidateWeb, and Administrator.
DB1 has three user-defined database roles.
The roles are configured as shown in the following table.
Keyword Search
The keyword searches for the job openings are performed by using the following stored procedure named usp_GetOpenings:
Opening Update
Updates to the Openings table are performed by using the following stored procedure named usp_UpdateOpening:
The Candidates role and the Companies role are assigned the GRANT EXECUTE permission for usp_UpdateOpening.
Problems and Reported Issues
Concurrency Problems
You discover that deadlocks frequently occur.
You identify that a stored procedure named usp_AcceptCandidate and a stored procedure named usp_UpdateCandidate generate deadlocks.
The following is the code for usp_AcceptCandidate:
Salary Query Issues
Users report that when they perform a search for job openings without specifying a minimum salary, only job openings that specify a minimum salary are displayed.
Log File Growth Issues
The current log file for DB1 grows constantly.
The log file fails to shrink even when the daily SQL Server Agent Shrink Database task runs.
Performance Issues
You discover that a stored procedure named usp_ExportOpenings takes a long time to run and executes a table scan when it runs.
You also discover that the usp_GetOpenings stored procedure takes a long time to run and that the non-clustered index on the Description column is not being used.
Page Split Issues
On DB1, many page splits per second spike every few minutes.
Requirements
Security and Application Requirements
Litware identifies the following security and application requirements:
– Only the Administrator, Company, and CompanyWeb database users must be able to execute the usp_UpdateOpening stored procedure.
– Changes made to the database must not affect WebApp1.
Locking Requirements
Litware identifies the following locking requirements:
– The usp_GetOpenings stored procedure must not be blocked by the usp_UpdateOpening stored procedure.
– If a row is locked in the Openings table, usp_GetOpenings must retrieve the latest version of the row, even if the row was not committed yet.
Integration Requirements
Litware exports its job openings to an external company as XML data.
The XML data uses the following format:
A stored procedure named usp_ExportOpenings will be used to generate the XML data.
The following is the code for usp_ExportOpenings:
The stored procedure will be executed by a SQL Server Integration Services (SSIS) package named Package1.
The XML data will be written to a secured folder named Folder1. Only a dedicated Active Directory account named Account1 is assigned the permissions to read from or write to Folder1.
Refactoring Requirements
Litware identifies the following refactoring requirements:
New code must be written by reusing the following query:
The results from the query must be able to be joined to other queries.
Upload Requirements
Litware requires users to upload their job experience in a Word file by using WebApp1.
WebApp1 will send the Word file to DB1 as a stream of bytes.
DB1 will then convert the Word file to text before the contents of the Word file is saved to the Candidates table.
A database developer creates an assembly named Conversions that contains the following:
A class named Convert in the SqlConversions namespace A method named ConvertToText in the Convert class that converts Word files to text
The ConvertToText method accepts a stream of bytes and returns text.
The method is used in the following stored procedure:
Job Application Requirements
A candidate can only apply to each job opening once.
Data Recovery Requirements
All changes to the database are performed by using stored procedures. WebAppl generates a unique transaction ID for every stored procedure call that the application makes to the database.
If a server fails, you must be able to restore data to a specified transaction.
QUESTION 86
You need to create a script that automates the export of the XML data.
The script must meet the integration requirements.
What should you include in the script?
A. The CREATE SERVER ROLE command and the sp_add_proxy, sp_add_job,
sp_add_jobstep/ and sp_grant_proxy_to_subsystem system stored procedures.
B. The CREATE CREDENTIAL command and the sp_reassign_proxy, sp_add_job,
sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures.
C. The CREATE CREDENTIAL command and the sp_add_proxy, sp_add_job,
sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures.
D. The CREATE SERVER ROLE command and the sp_reassign_proxy, sp_add_job,
sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures.
Answer: C
QUESTION 87
You need to resolve the performance issues of the usp_ExportOpenings stored procedure.
The solution must minimize the amount of hard disk space used.
Which statement should you execute on DB1?
A. EXEC sp_dboption ‘DB1’, ‘auto create statistics’, ‘TRUE’;
B. CREATE INDEX IX_Exp_Openings ON Openings(PostDate, FilledDate)
INCLUDE (Description, Title, Salary);
C. CREATE INDEX IX_Exp_Openings ON Openings(PostDate)
INCLUDE (Description, Title, Salary)
WHERE FilledDate IS NULL;
D. EXEC sp_recompile ‘usp_ExportOpenings’;
Answer: C
QUESTION 88
You need to implement a solution that meets the job application requirements.
What should you do?
A. Create a one-to-one relationship between the Candidates table and the Applications table.
B. Create a one-to-one relationship between the Openings table and the Applications table.
C. Add a UNIQUE constraint to the Applications table on the OpeningID column and the CandidateID column.
D. Add a UNIQUE constraint to the Applications table on the ApplicationID column and CandidateID column.
Answer: C
QUESTION 89
You need to identify the cause of the page split issues.
Which SQL Server feature should you use?
A. DBCC TRACEOFF
B. DBCC REINDEX
C. Extended Events
D. SQL Server Profiler
Answer: C
QUESTION 90
You need to design a solution that meets the refactoring requirements.
Which type of object should you include in the solution?
A. A table-valued function
B. A distributed view
C. An aggregate function
D. An indexed view
Answer: A
QUESTION 91
You need to recommend a solution that meets the concurrency problems.
What should you include in the recommendation?
A. Modify the stored procedures to use the SERIALIZABLE isolation level.
B. Modify the order in which usp_UpdateCandidate accesses the Applications table and the Candidates table.
C. Modify the stored procedures to use the REPEATABLE READ isolation level.
D. Modify the order in which usp_AcceptCandidate accesses the Applications table and the Candidates table.
Answer: B
QUESTION 92
You need to implement a change to usp_ExportOpenings that meets the integration requirements.
What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?)
A. To the end of line 04, add [Opening].
B. To the end of line 05, add [Opening! title].
C. To line 10, add FOR XML RAW.
D. To line 10, add FOR XMLEXPLICIT.
E. To line 10, add FOR XML AUTO.
F. To the end of line 04, add [Opening!ELEMENT].
G. To the end of line 06, add [Opening!salary!ELEMENT].
H. To the end of line 05, add [Opening!title!ELEMENT].
I. To the end of line 06, add [Opening! salary].
Answer: ABEI
Explanation:
E: The AUTO mode generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified.
You have minimal control over the shape of the XML generated.
The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics.
QUESTION 93
You need to implement a solution that meets the locking requirements.
Which line of code should you modify?
A. Change line 07 in usp_UpdateOpening to:
UPDATE Openings WITH (UPDLOCK)
B. Change line 09 in usp_GetOpenings to:
FROM Openings o (ROWLOCK)
C. Change line 07 in usp_UpdateOpening to:
UPDATE Openings WITH (READPA5T)
D. Change line 09 in usp_GetOpenings to:
FROM Openings o (NOLOCK)
Answer: D
QUESTION 94
You need to implement a solution that addresses the upload requirements.
Which code segment should you use to implement the Conversions assembly?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
QUESTION 95
You need to implement a solution that meets the security requirements.
Which statement should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
QUESTION 96
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
Case Study 6: Coho Winery (Question 97 ~ Question 110)
Overview
You are a database developer for a company named Coho Winery.
Coho Winery has an office in London.
Coho Winery has an application that is used to process purchase orders from customers and retailers in 10 different countries.
The application uses a web front end to process orders from the Internet.
The web front end adds orders to a database named Sales.
The Sales database is managed by a server named Server1.
An empty copy of the Sales database is created on a server named Server2 in the London office. The database will store sales data for customers in Europe.
A new version of the application is being developed.
In the new version, orders will be placed either by using the existing web front end or by loading an XML file. Once a week, you receive two files that contain the purchase orders and the order details of orders from offshore facilities.
You run the usp_ImportOders stored procedure and the usp_ImportOrderDetails stored procedure to copy the offshore facility orders to the Sales database.
The Sales database contains a table named Orders that has more than 20 million rows.
Database
Definitions
Database and Tables
The following scripts are used to create the database and its tables:
Stored Procedures
The following are the definitions of the stored procedures used in the database:
Indexes
The following indexes are part of the Sales database:
Data Import
The XML files will contain the list of items in each order.
Each retailer will have its own XML schema and will be able to use different types of encoding. Each XML schema will use a default namespace.
The default namespaces are not guaranteed to be unique.
For testing purposes, you receive an XSD file from a customer.
For testing purposes, you also create an XML schema collection named ValidateOrder. ValidateOrder contains schemas for all of the retailers.
The new version of the application must validate the XML file, parse the data, and store the parsed data along with the original XML file in the database.
The original XML file must be stored without losing any data.
Reported Issues
Performance Issues
You notice the following for the usp_GetOrdersAndItems stored procedure:
The stored procedure takes a long time to complete.
Less than two percent of the rows in the Orders table are retrieved by usp_GetOrdersAndItems.
A full table scan runs when the stored procedure executes.
The amount of disk space used and the amount of time required to insert data are very high.
You notice that the usp_GetOrdersByProduct stored procedure uses a table scan when the stored procedure is executed.
Page Split Issues
Updates to the Orders table cause excessive page splits on the IX_Orders_ShipDate index.
Requirements
Site Requirements
Users located in North America must be able to view sales data for customers in North America and Europe in a single report.
The solution must minimize the amount of traffic over the WAN link between the offices.
Bulk Insert Requirements
The usp_ImportOrderDetails stored procedure takes more than 10 minutes to complete.
The stored procedure runs daily.
If the stored procedure fails, you must ensure that the stored procedure restarts from the last successful set of rows.
Index Monitoring Requirements
The usage of indexes in the Sales database must be monitored continuously.
Monitored data must be maintained if a server restarts.
The monitoring solution must minimize the usage of memory resources and processing resources.
QUESTION 97
You need to implement a solution that meets the site requirements.
What should you implement?
A. A non-indexed view on Server2
B. A distributed view on Server2
C. A distributed view on Server1
D. A non-indexed view on Server1
Answer: C
QUESTION 98
You need to ensure that usp_AddXMLOrder can be used to validate the XML input from the retailers.
Which parameters should you add to usp_AddXMLOrder on line 04 and line 05? (Each correct answer presents part of the solution. Choose all that apply.)
A. @schema varbinary(100).
B. @items varchar(max).
C. @schema sysname.
D. @items varbinary(max).
E. @items xml.
F. @schema xml.
Answer: CE
QUESTION 99
You need to implement a solution that addresses the performance issues of the usp_GetOrdersByProduct stored procedure.
Which statement should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
QUESTION 100
You plan to create a stored procedure that inserts data from an XML file to the OrderDetails table. The following is the signature of the stored procedure:
The following is the XSD file used to create the ValidateOrder schema collection:
You develop a code segment that retrieves the number of items and loops through each item. Each time the loop runs, a variable named @itemNumber is incremented.
You need to develop a code segment that retrieves the product ID of each item number in the loop.
Which code segment should you develop?
A. SET @productID = @items.value(‘/Root/Product/productID’, int)
B. SET @productID = @items.value(‘/Root/Product[‘+ @itemNumber+ ‘]/@productID’, int)
C. SET @productID = @items.value(‘/Root/Product/@productID’, int)
D. SET @productID = @items.value(‘/Root/Product[‘+ @itemNumber+ ‘]/productID’, int)
Answer: D
GreatExam is one of the leading exam preparation material providers. We have a complete range of exams offered by the top vendors. You can download 70-464 dumps in PDF format on GreatExam.com. Comparing with others’, our 70-464 exam questions are more authoritative and complete. What’s more, the 70-464 prepare material are the latest. We ensure you pass the 70-464 exam easily.