It is impossible to pass Microsoft 70-469 exam without any help in the short term. Come to Exambible soon and find the most advanced, correct and guaranteed Microsoft 70-469 practice questions. You will get a surprising result by our Updated Recertification for MCSE: Data Platform practice guides.
Q33. 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
Q34. You have a SQL Azure database.
You execute the following script:
You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.
You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?
A. CREATE INDEX IX_Table1 ON Table1 (Column2) WITH FILLFACTOR-0
B. CREATE INDEX IX_Table1 OK Table1 (Column1) INCLUDE (Column2)
C. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NULL
D. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NOT NULL
Answer: D
Q35. 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
Q36. You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows.
Proc1 updates data in Table1. Proc2 reads data from Table1.
You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1.
You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating.
What should you change Proc1 to do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Update less than 4,000 rows simultaneously.
B. Use the PAGLOCK table hint.
C. Wait for Proc2 to complete.
D. Use the ROWLOCK table hint.
Answer: A
Q37. You need to recommend a change to USP_3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails.
Which change should you recommend?
A. Set the XACT_ABORT option to off.
B. Set the XACT_ABORT option to on.
C. Set the IMPLICIT_TRANSACTIONS option to off.
D. Set the IMPLICIT_TRANSACTIONS option to on.
Answer: A
Q38. You need to create the InvoiceStatus table in DB1.
How should you define the InvoiceID column in the CREATE TABLE statement?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q39. You need to identify the cause of the page split issues. Which SQL Server feature should you use?
A. DBCC REINDEX
B. SQL Server Profiler
C. Extended Events
D. DBCC TRACEOFF
Answer: C
Q40. 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
Q41. 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
Q42. You need to write code that will allow the sales force to retrieve data for their reports with the least amount of effort.
Which code should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q43. You need to modify the usp_DetectSuspiciousActivity stored procedure.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
F. Option F
Answer: DE
Q44. You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs.
What should you do?
A. Execute sp_help 'usp_GetOrdersByProduct'.
B. Execute sp_recompile 'usp_GetOrdersByProduct'.
C. Add WITH RECOMPILE to line 03 in usp_GetOrdersByProduct.
D. Add WITH (FORCESEEK) to line 07 in usp_GetOrdersByProduct.
Answer: C
Q45. You have a server named Server1 that has 16 processors.
You plan to deploy multiple instances of SQL Server 2014 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. Processor affinity
B. Windows System Resource Manager (WSRM)
C. Max Degree of Parallelism
D. Resource Governor
Answer: A
Q46. 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?
A. Optimize for Ad hoc Workloads
B. Enable Contained Databases
C. Allow Triggers to Fire Others
D. Scan for Startup Procs
Answer: A
Q47. You need to recommend a solution that meets the data recovery requirement. What should you include in the recommendation?
A. A differential backup
B. A transaction log backup
C. Snapshot isolation
D. A database snapshot
Answer: D
Q48. You create a view by using the following code:
Several months after you create the view, users report that the view has started to return unexpected results.
You discover that the design of Table2 was modified since you created the view.
You need to ensure that the view returns the correct results.
Which code segment should you run?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D