Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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
Q2. 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
Q3. You need to recommend a solution to minimize the amount of time it takes to execute USP_1.
With what should you recommend replacing Table1?
A. A view
B. A temporary table
C. A table variable
D. A function
Answer: A
Q4. Which data type should you use for CustomerID?
A. varchar(11)
B. bigint
C. nvarchar(11)
D. char(11)
Answer: D
Q5. You have a database that is accessed by 300 concurrent users.
You need to log all of the queries 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 script that enables trace flags
C. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views
D. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views
Answer: A
Q6. You have two SQL Server 2014 instances named SQLDev and SQLProd.
You plan to create a new database on SQLProd that will use SQL Server Authentication.
You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev even if they do not have a login on the SQLDev instance.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Triggers
B. Contained database
C. SQL Server Analysis Services (SSAS) scripts
D. Extended Events
E. SQL Server Integration Services (SSIS) scripts
Answer: B
Q7. You need to implement a solution that addresses the upload requirements. Which code segment should you use to implement the Conversions assembly?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q8. You have two databases named DB1 and DB2 that are located on the same server.
You plan to create a stored procedure named SProc1 in DB1.
SProc1 will query a table named Table2 in DB2.
You need to recommend a solution to ensure that SProc1 can access Table2 without granting users direct access to Table2.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Contained databases
B. Application roles
C. Cross-database ownership chaining
D. Digital certificates
Answer: B
Q9. You plan to create a new table that has the following requirements:
Uses a GUID data type as the primary key.
Uses a clustered index as the primary key.
Minimizes fragmentation.
You need to recommend which option to include in the CREATE statement.
Which option should you include?
More than one answer choice may achieve the goal. Select the BEST answer.
A. NEWID
B. @@IDENTITY
C. NEWSEQUENTIALID
D. IDENTITY
Answer: C
Q10. You need to recommend a solution to ensure that USP_4 adheres to the security requirements.
What should you include in the recommendation?
A. Enable SQL Server Audit.
B. Enable trace flags.
C. Configure data manipulation language (DML) triggers.
D. Enable C2 audit tracing.
Answer: A
Q11. 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
Q12. 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
Q13. You need to recommend changes to the ERP application to resolve the search issue. The solution must minimize the impact on other queries generated from the ERP application.
What should you recommend changing?
A. The collation of the Products table
B. The index on the ProductName column
C. The collation of the ProductName column
D. The data type of the ProductName column
Answer: C
Q14. You need to implement a solution that meets the site requirements.
What should you implement?
A. A non-indexed view on Server1
B. A non-indexed view on Server2
C. A distributed view on Server1
D. A distributed view on Server2
Answer: C
Q15. Developers report that usp_UpdateSessionRoom periodically returns error 3960.
You need to prevent the error from occurring. The solution must ensure that the stored procedure returns the original values to all of the updated rows.
What should you configure in Procedures.sql?
A. Replace line 46 with the following code:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. Replace line 46 with the following code:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
C. Move the SELECT statement at line 49 to line 57.
D. Move the SET statement at line 46 to line 53.
Answer: A
Q16. You need to modify Production.ProductDetails_Insert to comply with the application requirements.
Which code segment should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C