Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. Topic 8)
You have two existing tables, one named COUNTRY and the other named STATES. The tables are defined as follows:
You need to set up a rule that every STATE.Country_Abbr must match an existing record in the COUNTRY table.
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:
Q2. 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
Explanation:
Ref: http://msdn.microsoft.com/en-us/librAry/ms190439(v=sql.90).aspx
Q3. Topic 8)
You are designing two stored procedures named Procedure1 and Procedure2.
You identify the following requirements:
. Procedure1 must take a parameter that ensures that multiple rows of data can pass into the stored procedure. . Procedure2 must use business logic that resides in a Microsoft .NET Framework assembly.
You need to identify the appropriate technology for each stored procedure.
Which technologies should you identify?
To answer, drag the appropriate technology to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)
Answer:
Q4. You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp_GetOrdersAndItems?
A. Add SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to line 03.
B. Add SET TRANSACTION ISOLATION LEVEL SNAPSHOT to line 03.
C. Add (UPDLOCK) to the end of line 06.
D. Add (READPAST) to the end of line 06.
Answer: D
55. You need to implement a solution that addresses the bulk insert requirements.
What should you add to line 08 in usp_ImportOrderDetails?
A. LASTROW=0.
B. BATCHSIZE=0.
C. BATCHSIZE=1000.
D. LASTROW = 1000.
Q5. You discover that usp.SelectSpeakersByName executes slowly if usp_UpdateSpeakerName executes simultaneously.
You need to minimize the execution time of usp.SelectSpeakersByName. The solution must not affect the performance of the other stored procedures.
What should you update?
A. Usp_UpdateSpeakerName to use the NOLOCK query hint
B. Usp_UpdateSpeakerName to use snapshot isolation
C. Usp_SelectSpeakersByName to use the NOLOCK query hint
D. Usp_SelectSpeakersByName to use snapshot isolation
Answer: C
Explanation: NOLOCK
Is equivalent to READUNCOMMITTED.
READUNCOMMITTED
Specifies that dirty reads are allowed.
Q6. Topic 8)
Your network contains a server named Server1 that runs SQL Server 2012. Server1 contains an instance named Instance1. Instance1 contains a database named ContentDatabase.
ContentDatabase uses transaction log backups.
The recovery model of ContentDatabase is set to FULL.
You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log.
Which three code segments 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:
Q7. You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
.
Permit insertions into a table variable.
.
Support structured exception handling.
.
Prevent changes to the definition of referenced objects.
.
Support the use of the APPLY operator on the output of the object.
Which type of object should you use?
A. An inline table-valued function
B. A stored procedure
C. A scalar user-defined function
D. A multi-statement table-valued function
Answer: C
Q8. You have a SQL Server 2012 instance that hosts a single-user database.
The database does not contain user-created stored procedures or user-created functions.
You need to minimize the amount of memory used for query plan caching.
Which advanced server option should you modify?
A. Scan for Startup Procs
B. Enable Contained Databases
C. Optimize for Ad hoc Workloads
D. Allow Triggers to Fire Others
Answer: C
Q9. Topic 8)
You have a table named Customers that has a clustered index defined on the ID column.
You write a script to create a stored procedure.
You need to complete the script for the stored procedure. The solution must minimize the
number of locks and deadlocks.
What should you do?
To answer, drag the appropriate option to the correct location in the answer area. (Answer
choices may be used once, more than once, or not at all.)
Answer:
Q10. The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values.
You need to update Fax to meet the following requirements:
. Prevent null values from being used.
. Always use an empty string instead of a null value.
Which statement or statements should you execute? (Each correct answer presents part of the solution. Choose all that apply.)
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
Answer: A,B,E
Explanation: E: First change the NULLs to ' '.
A: Then set the default to the column to ' '.
B: Finally add the NOT NULL constraint to the column.
Q11. 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
Q12. 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.
A. Log errors by using trace flag 1222
B. Log errors by using trace flag 1204
C. A SQL Server Profiler template
D. An Extended Events session
Answer: D
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_d eadlocks_2d00_in_2d00_sql_2d00_server.aspx
Q13. Topic 8)
You have a table named Table1 that contains 1 million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar(16) data type.
You have a certificate named Cert1.
You need to replace Column1 with a new encrypted column that uses two-way encryption.
Which code segment should you execute before you remove Column1?
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:
Q14. You are planning the ManufacturingSteps table.
You need to define the ProductID column in the CREATE TABLE statement.
Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/ms189049.aspx http://msdn.microsoft.com/en-us/library/ms179610.aspx
http://msdn.microsoft.com/en-us/library/ff878370.aspx
Q15. While testing the CategoryFromType function, you discover that the function is returning 'Other'.
You need to update CategoryFromType to return the category name.
Which line of code should you modify in CategoryFromType.sql?
A. 04
B. 05
C. 12
D. 14
Answer: B
Q16. 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. Add WITH (FORCESEEK) to line 69 in usp.GetOrdersByProduct.
C. Add WITH RECOMPILE to line 64 in usp.GetOrdersByProduct.
D. Execute sp_recompile usp.GetOrdersByProduct'.
Answer: B
Q17. Topic 8)
You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS, and CPU use can average 30% over the eight processors.
The first process summarizes the day's activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
. Both processes must never use more than 50 percent of the CPU at any one time. . The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10. . The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
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:
149. You are creating a table named Orders.
You need to ensure that every time a new row is added to the Orders table, a table that is
used for auditing is updated.
What should you use?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A Data Definition Language (DDL) trigger
B. A DEFAULT constraint
C. A CHECK constraint
D. A FOREIGN KEY constraint
E. A data manipulation language (DML) trigger