How to pass Microsoft 70-464 Real Exam in 24 Hours [exam topics 16-30]

Simulation of 70-464 free exam questions materials and keys for Microsoft certification for client, Real Success Guaranteed with Updated 70-464 pdf dumps vce Materials. 100% PASS Developing Microsoft SQL Server 2012 Databases exam Today!

2016 Apr 70-464 Study Guide Questions:

Q16. You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table. 

After the failover is complete, a user receives the following error message when connecting to DB1 on Server2: 

"Msg 916, Level 14, State 1, Line 1 The server principal "Account1" is not able to access the database "DB1" under the current security context." 

You verify that there is a server login for Account1 on Server2. 

You need to ensure that Account1 can connect to DB1. 

What should you do? 

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

A. Update the SID for Account1 on DB1. 

B. Add Account1 to the db_datareader role. 

C. Create a new database user on DB1. 

D. Implement Windows authentication. 

Answer: B 


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


Q18. You have an index for a table in a SQL Azure database. The database is used for Online 

Transaction Processing (OLTP). 

You discover that the index consumes more physical disk space than necessary. 

You need to minimize the amount of disk space that the index consumes. 

What should you set from the index options? 

A. STATISTICS_NORECOMPUTE = OFF 

B. FILLFACTOR = 80 

C. FILLFACTOR = 0 

D. STATISTICS_NORECOMPUTE = ON 

Answer: C 

Explanation: 

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


Q19. Topic 8) 

You are a SQL Server 2014 Developer. A database that you work on contains two tables that are defined as follows: 

Product is an important table that has sensitive audit requirements. 

You need to create a trigger that supports the following requirements: 

1. 

Every row that is inserted or updated in Product will reflect its actual LastUpdatedDate and LastUpdatedBy values in the Product table. 

2. 

Any row that is updated or deleted must write a new record reflecting the OLD values into the ProductAudit table. 

3. 

Any error that occurs during the course of the trigger's execution must prevent the changes from happening. 

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: 


Q20. You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy. 

What should you do? 

A. Query system DMVs to monitor avg_chain_length and max_chain_length. Create alerts to notify you when these values converge. 

B. Create a SQL Agent alert when the File Table: Avg time per file I/O request value is increasing. 

C. Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value increases. 

D. Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value decreases. 

Answer: A 

Explanation: From scenario: 

You need to anticipate when POSTransaction table will need index maintenance. 

The index maintenance strategy for the UserActivity table must provide the optimal structure for both maintainability and query performance. 


70-464  free exam questions

Replace 70-464 free question:

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


Q22. Which code segment should you use to define the ProductDetails column? 

A. ProductDetails xml (DOCUMENT Production.ProductDetailsSchema) NULL 

B. ProductDetails xml NULL 

C. ProductDetails xml (CONTENT Production.ProductDetailsSchema) NULL 

D. ProductDetails varchar(MAX) NULL 

Answer: D 


Q23. You execute the following code: The execution plan for the query is shown in the exhibit. (Click the Exhibit button.) 

You need to prevent the key lookup. What should you modify? 

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

A. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: C 


Q24. You have a table named Rooms that contains three columns. You execute the following query: 

You discover the execution plan shown in the exhibit. (Click the Exhibit button.) 

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

What should you do? 

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

A. Include the RoomName column and the Position column in the Room_IX index. 

B. Create a nonclustered index for RoomName, Id, and Position. 

C. Create a clustered index for Id. 

D. Use the WITH (INDEX(Room_IX),NOLOCK) query hint. 

Answer: B 


Q25. You have an application that uses a view to access data from multiple tables. 

You need to ensure that you can insert rows into the underlying tables by using the view. 

What should you do? 

A. Create an INSTEAD OF trigger on the view. 

B. Define the view by using the SCHEMABINDING option. 

C. Define the view by using the CHECK option. 

D. Materialize the view. 

Answer: C 

Explanation: 

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


certleader.com

Refined 70-464 lab:

Q26. 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: 


Q27. You have a text file that contains an XML Schema Definition (XSD). 

You have a table named Schema1.Table1. 

You have a stored procedure named Schema1.Proc1 that accepts an XML parameter 

named Param1. 

You need to store validated XML data in Schema1.Table1. The solution must ensure that only valid XML data is accepted by Param1. What should you do? (Each correct answer presents part of the solution. Choose all that 

apply.) 

A. Define an XML column in Table1 by using an XML schema collection. 

B. Create an XML schema collection in the database from the text file. 

C. Declare Param1 var1 as type XML and associate the variable to the XML schema collection. 

D. use the modify method to insert the XML schema into each row of the XML column in Table1. 

Answer: A,B,D 

Explanation: Explanation/Reference: http://msdn.microsoft.com/en-us/library/bb510420.aspx http://msdn.microsoft.com/en-us/library/ms187856.aspx http://msdn.microsoft.com/en-us/library/ms176009.aspx http://msdn.microsoft.com/en-us/library/hh403385.aspx http://msdn.microsoft.com/en-us/library/ms184277.aspx 


Q28. You review a query that runs slowly. The query accesses data in a table named Schema1.Table1. 

The following is the relevant portion of the execution plan for the query: You need to create the missing index. 

Which code segment should you execute? 

A. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3 

B. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) 

C. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4) 

D. CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4) 

Answer: C 


Q29. You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID. 

Which line of code should you modify in CountryFromID.sql? 

A. 04 

B. 05 

C. 06 

D. 19 

Answer: D 

Explanation: 

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


Q30. Topic 8) 

You have a SQL Server 2012 database named DB1. DB1 contains four filegroups named FG1, FG2, FG3, and FG4. You execute the following code: 

Two million rows are added to dbo.Sales. 

You need to move the data from the first partition to a new table named SalesHistory and, starting on December 31, 2012, repartition dbo.Sales to support new sales data for three months. 

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: 



see more 70-464 dumps