Q1. 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 

View Answer

Q2. You need to design a solution that meets the refactoring requirements. Which type of object should you include in the solution? 

A. An indexed view 

B. An aggregate function 

C. A distributed view 

D. A table-valued function 

View Answer

Q3. You have a server that has SQL Server 2014 installed. The server contains 100 user databases. 

You need to recommend a backup solution for the user databases. 

The solution must meet the following requirements: 

Perform a transaction log backup every hour. 

Perform a full backup of each database every week. 

Perform a differential backup of each database every day. 

Ensure that new user databases are added automatically to the backup solution. 

What should you recommend? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. Policy-Based Management 

B. A Data Definition Language (DDL) trigger 

C. SQL Server Agent jobs 

D. A maintenance plan 

View Answer

Q4. 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 

View Answer

Q5. You execute the following code: 

You create a nonclustered index named IX_CustomerName on the CustomerName column. 

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 CustomerName column for the partition scheme. 

B. Replace IX_CustomerName with a clustered index. 

C. Replace LEFT(CustomerName ,1) = 'a' with CustomerName LIKE 'a%'. 

D. Replace LEFT(CustomerName ,1) = 'a' with SUBSTRING(CustomerName ,1,1) - 'a'. 

View Answer

Q6. 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. 

View Answer

Q7. You use SQL Server 2014. The physical server is a dedicated database server that has 120GB of RAM available. There is approximately 50GB of storage space available on a slow local disk. 

You create a new stored procedure. You decide you need to temporarily hold approximately 300,000 rows from two tables, from which you will compute two complex business scores. 

The stored procedure will use temporary storage defined as follows: The code will make several passes through the data, applying complex calculations before writing the data to a permanent disk-based table in the same database from which it reads the data. 

For this stored procedure, you need to deal with temporary data in the most efficient way to minimize physical disk pressure. 

What should you do? 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 

View Answer

Q8. 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. SCHEMABINDING 

B. VIEW_METADATA 

C. DISTINCT 

D. DEFAULT 

View Answer

Q9. You need to recommend a solution that meets the concurrency problems. 

What should you include in the recommendation? 

A. Modify the stored procedures to use the SERIALIZABLE isolation level. 

B. Modify the order in which usp_AcceptCandidate accesses the Applications table and the Candidates table. 

C. Modify the order in which usp_UpdateCandidate accesses the Applications table and the Candidates table. 

D. Modify the stored procedures to use the REPEATABLE READ isolation level. 

View Answer

Q10. During performance testing, you discover that database INSERT operations against the Inventory table are slow. 

You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations. 

What should you recommend? 

A. Partition the nonclustered index. 

B. Partition the Inventory table.snapshot replication 

C. Create a column store index.Master Data Services 

D. Drop the clustered index.change data capture 

View Answer