70-459|You need to recommend a solution that addresses the concurrency

Question: 18

You need to recommend a solution that addresses the concurrency requirement. What should you recommend?

A. Make calls to Sales.Proc1 and Sales.Proc2 synchronously.
B. Modify the stored procedures to update tables in the same order for all of the stored procedures.
C. Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
D. Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.

Answer: B

70-459|Which system objects should you use in your query to best achieve this task?

Question: 17

You administer a SQL Server 2014 instance.
Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues.
You want to write a query of the system views that will report the following:
Number of users that have a connection to the server Whether a user’s connection is active Whether any connections are blocked What queries are being executed Whether the database restore is still executing and, if it is, what percentage of the restore is complete

Which system objects should you use in your query to best achieve this task?

A. sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects
B. sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects
C. sys.sysprocesses, sys.dm_exec_query_text, sys.objects
D. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text

Answer: D
Section: [none]
Explanation
Explanation/Reference:
* sys.dm_exec_requests
Returns information about each request that is executing within SQL Server.
* sys.dm_exec_sessions
Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server- scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.
* sys.dm_exec_query_text
Returns the text of the SQL batch that is identified by the specified sql_handle.
sys.dm_exec_requests (Transact-SQL)
sys.dm_exec_sessions (Transact-SQL)

70-459 | You need to recommend a change to sp3 to ensure that the procedure completes only if all of the UPDATE statements complete.Which change should you recommend?

Question : 15

You need to recommend a change to sp3 to ensure that the procedure completes only if all of the UPDATE statements complete.Which change should you recommend?

A. Set the IMPLICIT_TRANSACTIONS option to off.
B. Set the XACT_ABORT option to off
C. Set the IMPLICIT_TRANSACTIONS option to on.
D. Set the XACT_ABORT option to on.

Answer : D

70-459 Sample Question : 14

Question : 14

You need to recommend a solution for the planned changes to the customer classifications. What should you recommend? (Each correct answer presents part of the solution. Choose all that apply.)

A. Add a table to track any changes made to the classification of each customer.
B. Add columns for each classification to the Customers table.
C. Implement change data capture.
D. Add a row to the Customers table each time a classification changes.
E. Add a column to the Classifications table to track the status of each classification.

Answer : AC

70-459 Sample Question : 13

Question : 13

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP). You discover that many page splits occur when records are inserted or updated in the table. You need to minimize the number of page splits.What should you set from the index options?

A. FILLFACTOR = 0
B. STATISTICS_NORECOMPUTE = ON
C. STATISTICS_NORECOMPUTE = OFF
D. FILLFACTOR = 80

Answer : D

70-459 Sample Question : 12

Question : 12

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. Use the modify method to insert the XML schema into each row of the XML column in Table1.
B. Define an XML column in Table1 by using an XML schema collection.
C. Declare Param1 as type XML and associate the variable to the XML schema collection.
D. Create an XML schema collection in the database from the text file.

Answer : ABD

70-459 Sample Question : 11

Question : 11

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 VIEW_METADATA
B. WITH ENCRYPTION
C. WITH CHECK OPTION
D. WITH SCHEMABINDING

Answer : C

70-459 Sample Question : 10

Question : 10

You have a SQL Azure database. You need to identify which keyword must be used to create a view that will be indexed.Which keyword should you identify?

A. DISTINCT
B. DEFAULT
C. SCHEMABINDING
D. VIEW_METADATA

Answer : C

70-459 Sample Question : 9

Question : 9

You have a server named Server1 that has 16 processors. You plan to deploy multiple instances of SQL Server 2012 to Server1.You need to recommend a method to allocate processors to each instance.What should you include in the recommendation? More than one answer choice may achieve the goal.Select the BEST answer.

A. Max Degree of Parallelism
B. Processor affinity
C. Windows System Resource Manager (WSRM)
D. Resource Governor

Answer : B

70-459 Sample Question : 8

Question : 8

You plan to create a database. The database will be used by a Microsoft.NET application for a special event that will last for two days. During the event, data must be highly available. After the event, the database will be deleted. You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications.What should you recommend? More than one answer choice may achieve the goal.Select the BEST answer.

A. SQL Server 2012 Enterprise
B. SQL Azure
C. SQL Server 2012 Express with Advanced Services
D. SQL Server 2012 Standard

Answer : B

70-459 Sample Question : 7

Question : 7

You are troubleshooting an application that runs a query. The application frequently causes deadlocks.You need to identify which transaction causes the deadlock.What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

A. Query the sys.dm_exec_sessions dynamic management view.
B. Create an extended events session to capture deadlock information.
C. Query the sys.dm_exec_requests dynamic management view.
D. Create a trace in SQL Server Profiler that contains the Deadlock graph event.

Answer : B

70-459 Sample Question : 6

Question : 6

You are creating a database that will store usernames and passwords for an application. You need to recommend a solution to store the passwords in the database. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

A. Encrypting File System (EFS)
B. One-way encryption
C. Reversible encryption
D. Transparent Data Encryption (TDE)

Answer : B

70-459 Sample Question : 5

Question : 5

Your company has a SQL Azure subscription.You implement a database named Database1.In Database1,you create two tables named Table1 and Table2. You create a stored procedure named sp1.Sp1 reads data from Table1 and inserts data into Table2.A user named User1 informs you that he is unable to run sp1. You verify that User1 has the SELECT permission on Table1 and Table2.You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1.What should you do?

A. Grant User1 the INSERT permission on Table2.
B. Add User1 to the db_datawriter role.
C. Grant User1 the EXECUTE permission on sp1.
D. Change sp1 to run as the sa user.

Answer : C

70-459 Sample Question : 4

Question : 4

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. STATISTICS_NORECOMPUTE = ON
C. FILLFACTOR = 0
D. FILLFACTOR = 80

Answer : C

70-459 Sample Question : 3

Question : 3

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. STATISTICS_NORECOMPUTE = ON
C. FILLFACTOR = 0
D. FILLFACTOR = 80

Answer : C

Search Words: 70-459 Braindumps real exam questions answers practice test braindumps, free Q&A online dumps download and free 70-459 Braindumps discount coupon code available.

Leave a Comment.

four × 3 =