Ideas to microsoft sql server 2012 development (70-464)


♥♥ 2017 NEW RECOMMEND ♥♥

Free VCE & PDF File for Microsoft 70-464 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-464 Exam Dumps (PDF & VCE):
Available on: http://www.certleader.com/70-464-dumps.html


70-464 Product Description:
Exam Number/Code: 70-464 vce
Exam name: Developing Microsoft SQL Server 2012 Databases
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Instant Access to Free VCE Files: Microsoft 70-464 Developing Microsoft SQL Server 2012 Databases

70-464 examcollection

Exam Code: 70-464 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Developing Microsoft SQL Server 2012 Databases
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-464 Exam.

2016 Jun preparing for exam 70-464:

Q91. 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_AcceptCandidate accesses the Applications table and the Candidates table. 

C. Modify the order in which usp_UpdateCandidate accesses the Applications table and the Candidates table. 

D. Modify the stored procedures to use the REPEATABLE READ isolation level. 

Answer: C 

Topic 6, Coho Winery 

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. 


Q92. Your company has a main office in London and a branch office in New York. 

Your network contains a server named Server5 that has SQL Server 2012 installed. Server5 contains a database name ContentDB and a table named ContentTable. 

You add an additional server named Server9 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. Create the view on Server5. 

B. Add Server9 as a linked server. 

C. Create the view on Server9. 

D. Add the Customers table to Server9. 

E. Add Server9 as a Distributor. 

F. Remove the Customers table from Server5. 

Answer: A,B,C,D 


Q93. You plan to modify a stored procedure to use temporary data. The stored procedure must meet the following requirements: 

. Favor physical memory when physical memory is available. 

. Be able to roll back changes to the temporary data. 

You need to recommend which object to add to the stored procedure. 

Which T-SQL command should you recommend? 

A. CREATE TABLE ##Table... 

B. CREATE TABLE Table... 

C. CREATE VIEW Table... 

D. CREATE PARTITION SCHEME Table... 

E. DECLARE TABLE @ Table... 

Answer: A 

Explanation: Temporary Tables You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned. Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name) 


Q94. You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU). 

You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits. 

The solution must minimize the amount of development effort required. 

What should you include in the recommendation? 

A. A FOREIGN KEY constraint 

B. A trigger 

C. A user-defined data type 

D. A CHECK constraint 

Answer: C 


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

Explanation: 

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


Q96. You execute the following code: 

You have a stored procedure that includes the following SELECT statement: 

You need to create a covering index on UserInfo. Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C 


Q97. Topic 8) 

You have a database that contains three tables. The tables are configured as shown in the following table. 

You have the following query: 

The execution plan for the query is shown in the exhibit. (Click the Exhibit button.) 

You need to create one index to minimize the amount of time it takes to execute the query. 

What should you do? 

To answer, drag the appropriate columns to the correct locations in the answer area. 

 (Answer choices may be used once, more than once, or not at all.) 

Answer: 


70-464  exam prep

Avant-garde ms 70-464:

Q98. Topic 8) 

You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a database. 

Your company identifies the following requirements for each stored procedure: 

. USP_1 cannot allow dirty reads. 

. USP_2 must place range locks on the data to ensure read consistency. 

You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks. 

Which isolation level should you identify? 

To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.) 

Answer: 



Q99. 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_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures. 

B. The CREATE CREDENTIAL command and the sp_add_proxy, sp_add_job, sp_add_jobstep, and sp_grant_proxy_to_subsystem system stored procedures. 

C. The CREATE CREDENTIAL command and the sp_reassign_proxy, sp_add_job, sp_add_jobstep, and sp_grant_login_to_proxy system stored procedures. 

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

Answer: B 


Q100. While testing usp.GetFutureSessions, you discover that IX_Sessions is accessed by a scan rather than a seek. 

You need to minimize the amount of time it takes to execute usp_GetFutureSessions. 

What should you do? (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 

F. Option F 

Answer: B,E 

Explanation: Future delivery dates. 


Q101. You execute the following code: 

You create a nonclustered index named IX_CustomerName on the CustomerName column. 

You execute the following query: 

You need to reduce the amount of time it takes to execute the query. 

What should you do? 

A. Partition the table and use the CustomerName column for the partition scheme. 

B. Replace IX_CustomerName with a clustered index. 

C. Replace LEFT(CustomerName ,1) = 'a' with CustomerName LIKE 'a%'. 

D. Replace LEFT(CustomerName ,1) = 'a' with SUBSTRING(CustomerName ,1,1) - 'a'. 

Answer: C 

Explanation: 

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


Q102. Topic 7) 

You need to design the UserActivity table. 

Which three steps should you perform in sequence? To answer, move the appropriate three actions from the list of actions to the answer area and arrange them in the correct order. 

Answer: 


Q103. You have a SQL Server 2012 database named Database1. You execute the following code: 

You insert 3 million rows into Sales. 

You need to reduce the amount of time it takes to execute Proc1. 

What should you do? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: A 


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


Q105. You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users. 

You need to log all of the queries to DB1 that become deadlocked. The solution must meet the following requirements: 

. Provide a representation of the deadlock in XML format. 

. Minimize the impact on the server. 

What should you create? 

A. A SQL Server Profiler trace 

B. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views 

C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views 

D. A script that enables trace flags 

Answer: A 



see more Developing Microsoft SQL Server 2012 Databases