Q1. Topic 7)
You need to redesign the system to meet the scalability requirements of the application.
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:
76. You need to modify the stored procedure usp_LookupConcurrentUsers.
What should you do?
A. Add a clustered index to the summary table.
B. Add a nonclustered index to the summary table.
C. Add a clustered columnstore index to the summary table.
D. Use a table variable instead of the summary table.
Q2. You discover that the usp_GetOrdersAndItems stored procedure takes a long time to complete while usp_AddOrder or usp_AddXMLOrder run.
You need to ensure that usp_GetOrdersAndItems completes as quickly as possible.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SERIALIZABLE.
B. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON statement.
C. Set the isolation level of the usp_AddOrder stored procedure to SERIALIZABLE.
D. Set the isolation level of the usp_GetOrdersAndItems stored procedure to SNAPSHOT.
E. Set the isolation level of the usp_AddOrder stored procedure to SNAPSHOT.
F. Execute the ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION OFF statement.
Answer: B,D
Q3. Topic 8)
You are a SQL Server 2014 Developer. A database that you work on contains two tables that are defined as follows:
Product is an important table that has sensitive audit requirements.
You need to create a trigger that supports the following requirements:
1.
Every row that is inserted or updated in Product will reflect its actual LastUpdatedDate and LastUpdatedBy values in the Product table.
2.
Any row that is updated or deleted must write a new record reflecting the OLD values into the ProductAudit table.
3.
Any error that occurs during the course of the trigger's execution must prevent the changes from happening.
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:
Q4. Your network contains a server that has SQL Server 2014 installed. You create a table by using the following script:
You need to recommend a solution to ensure that each combination of ProductName and ProductManufacturer is not duplicated.
What should you recommend creating?
A. A UNIQUE constraint
B. A filtered index
C. A columnstore index
D. A CHECK constraint
Answer: A
Q5. You execute the following code. After populating the Employees table with 10,000 rows, you execute the following query:
You need to reduce the amount of time it takes to execute the query.
What should you do?
A. Partition the table and use the JobTitle column for the partition scheme.
B. Change SUBSTRING(JobTitle,l,l) = 'C to JobTitle LIKE 'c%’
C. Change SUBSTRING (JobTitle, 1,1] = 'c' to LEFT(JobTitle ,1) = 'c'.
D. Replace IX_Employees with a clustered index.
Answer: B
Q6. You execute the following code: You need to select the task that has an IsFinished value of true from the Project that has an Id value of 1.
Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Q7. You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required.
What should you add to usp_SelectSpeakersByName?
A. A table variable
B. An OFFSET-FETCH clause
C. The ROWNUMBER keyword
D. A recursive common table expression
Answer: B
Explanation:
http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-serverpaging-methods/ http://msdn.microsoft.com/en-us/library/ms188385.aspx http://msdn.microsoft.com/en-us/library/ms180152.aspx http://msdn.microsoft.com/en-us/library/ms186243.aspx http://msdn.microsoft.com/en-us/library/ms186734.aspx http://www.sqlserver-training.com/how-to-use-offset-fetch-option-in-sql-server-order-byclause/-http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/11/30/using-offset-and-fetch/
Q8. You are creating a stored procedure named usp1. Usp1 will create a table that will be used during the execution of usp1. Only usp1 will be allowed to access the table.
You need to write the code required to create the table for usp1. The solution must minimize the need to recompile the stored procedure.
Which code segment should you use to create the table?
A. CREATE TABLE oneTable
B. CREATE TABLE ##oneTable
C. CREATE TABLE #oneTable
D. DECLARE oneTable TABLE
Answer: B
Q9. Topic 8)
You create a table that contains the following script:
You need to prevent duplicate values in the EmployeeID field.
Which five code segments should you use?
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:
Q10. You administer an instance of SQL Server 2014.
You are tasked with tuning a common set of queries. You have the results of several test executions, along with query plans. The schema and the data for all database object(s) used remain unchanged between executions. The QueryTime column is defined as a computed column that uses the GETDATE() system function. The query plans and results are shown below: You need to make an initial diagnosis of the situation, based solely on this input
Which two statements can you make about the performance characteristics of this query? Each correct answer presents a complete solution. Choose two.
A. The queries would perform better if the index named AccountNumber included the Name and QueryTime column.
B. The queries would perform worse if the index named AccountNumber included the NameColumn.
C. The queries would perform better if the index named AccountNumber included the Name column.
D. The object Account is a table, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount.
E. The object Account is an indexed view, with an index having a leading column of AccountNumber and a Clustered Index named PKAccount.
F. The object Account is a view, joining the Account-AccountNumber and Account.PKAccount objects together.
Answer: B,D