IT認証試験問題集
毎月、ITshikenは1500人以上の受験者が試験準備を助けて、試験に合格するために受験者にご協力します
 ホームページ / 70-464 問題集  / 70-464 問題練習

Microsoft 70-464 問題練習

Developing Microsoft SQL Server 2012 Databases 試験

最新更新時間: 2019/07/10,合計394問。

平成から令和へ:70-464 最新真題を買う時、日本語版と英語版両方を同時に獲得できます。

実際の問題集を練習し、試験のポイントを了解し、テストに申し込むするかどうかを決めることができます。

さらに試験準備時間の35%を節約するには、70-464 問題集を使用してください。

 / 27

Question No : 1
You have a SQL Server 2012 database named database1. Database1 contains a table named OrderDetails. For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?



正解:

Question No : 2
You have a database named database1. Database developers report that there are many deadlocks. You need to implement a solution to monitor the deadlocks.
The solution must meet the following requirements:
- Support real-time monitoring.
- Be enabled and disabled easily.
- Support querying of the monitored data.
What should you implement? More than one answer choice may achieve the goal. Select the BEST answer.

正解:
Explanation:
http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/08/12/monitor-deadlock-in-sql-2012/
http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_deadlocks_2d00_in_2d00_sql_2d00_server.aspx

Question No : 3
You have a server that has SQL Server 2012 installed. You need to identify which parallel execution plans are running in serial.
Which tool should you use?

正解:
Explanation:
http://msdn.microsoft.com/en-us/library/bb677278.aspx
http://msdn.microsoft.com/en-us/library/bb630282.aspx
http://www.sql-server-performance.com/2006/query-execution-plan-analysis/
http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/
http://www.sqlservercentral.com/articles/SQL+Server+2012/At+last%2c+execution+plans+show+true+thread+reservations./92458/
http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx
http://sqlblog.com/blogs/paul_white/archive/2012/05/02/parallel-row-goals-gone-rogue.aspx
http://msdn.microsoft.com/en-us/library/bb895310.aspx
http://msdn.microsoft.com/en-us/library/bb895313.aspx
http://msdn.microsoft.com/en-us/library/hh231122.aspx

Question No : 4
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.

正解:

Question No : 5
DRAG DROP
You have a table named Table1. Table1 has 1 million rows. Table1 has a columnstore index for a column named Column1. You need to import data to Table1. The solution must minimize the amount of time it takes to import the data.
What should you do? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.



正解:


Explanation:
Note:
* An xVelocity memory optimized columnstore index, groups and stores data for each column and then joins all the columns to complete the whole index.
Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries.
* Tables that have a columnstore index cannot be updated.
There are three ways to work around this problem.
A) To update a table with a columnstore index, drop the columnstore index, perform any required INSERT, DELETE, UPDATE, or MERGE operations, and then rebuild the columnstore index.
B) (applies in this scenario) Partition the table and switch partitions. For a bulk insert, insert data into a staging table, build a columnstore index on the staging table, and then switch the staging table into an empty partition. For other updates, switch a partition out of the main table into a staging table, disable or drop the columnstore index on the staging table, perform the update operations, rebuild or re-create the columnstore index on the staging table, and then switch the staging table back into the main table.
C) Place static data into a main table with a columnstore index, and put new data and recent data likely to change, into a separate table with the same schema that does not have a columnstore index.
Reference: Best Practices: Updating Data in a Columnstore Index

Question No : 6
DRAG DROP
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.)



正解:


Explanation:
Note:
Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.

Question No : 7
You have a database for a mission-critical web application. The database is stored on a SQL Server 2012 instance and is the only database on the instance. The application generates all T-SQL statements dynamically and does not use stored procedures. You need to maximize the amount of memory available for data caching.
Which advanced server option should you modify?

正解:

Question No : 8
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.



正解:

Question No : 9
You have a SQL Server 2012 database named DB1. You have a backup device named Device1. You discover that the log file for the database is full. You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs)
Which code segment should you execute?

正解:
Explanation:
http://msdn.microsoft.com/en-us/library/ms186865.aspx
http://msdn.microsoft.com/en-us/library/ms179478.aspx
http://msdn.microsoft.com/en-us/library/ms190925.aspx

Question No : 10
You execute the following code:



You need to reduce the amount of time it takes to execute the query.
What should you do?

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

Question No : 11
You execute the following code:



You need to select the task that has an Is Finished value of true from the Project that has an Id value of 1.
Which code segment should you use?



正解:

Question No : 12
You use SQL Azure to store data used by an e-commerce application. You develop a stored procedure named sp1. Sp1 is used to read and change the price of all the products sold on the e-commerce site. You need to ensure that other transactions are blocked from updating product data while sp1 is executing.
Which transaction isolation level should you use in sp1?

正解:

Question No : 13
DRAG DROP
You run the following code segment:



After you add 10,000 rows to Customers, you discover that the index is fragmented. You need to defragment the index in the least amount of time.
Which code segment should you execute? To answer, drag the appropriate value to the correct location in the code segment in the answer area. (Answer choices may be used once, more than once, or not at all.)



正解:


Explanation:
Note:
Locking the table during the process and not recomputing statistics would be the fastest.
* Online = OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
* STATISTICS_NORECOMPUTE = ON
Out-of-date statistics are not automatically recomputed.
Reference: ALTER INDEX (Transact-SQL)

Question No : 14
You are creating a table to support an application that will cache data outside of SQL Server. The application will detect whether cached values were changed before it updates the values. You need to create the table, and then verify that you can insert a row into the table.
Which code segment should you use?



正解:
Explanation:
http://msdn.microsoft.com/en-us/library/ms182776.aspx
http://msdn.microsoft.com/en-us/library/ms187942.aspx
http://msdn.microsoft.com/en-us/library/ms190348.aspx

Question No : 15
You review a query that runs slowly. The query accesses data in a table named Schemal.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?

正解:

 / 27