Microsoft-Developing SQL Databases (2)

Your company runs end-of-the-month accounting reports. While the reports run, other financial records are updated in the database. Users report that the reports take longer than expected to run. You need to reduce the amount of time it takes for the reports to run. The reports must show committed data only. What should you do?

You have multiple stored procedures inside a transaction. You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out. What should you do?

You run the following Transact-SQL statements: Records must only be added to the Orders table by using the view. If a customer name does not exist, then a new customer name must be created. You need to ensure that you can insert rows into the Orders table by using the view.

You run the following Transact-SQL statement: There are multiple unique OrderID values. Most of the UnitPrice values for the same OrderID are different. You need to create a single index seek query that does not use the following operators: Nested loop Sort Key lookup Which Transact-SQL statement should you run?

You are developing an ETL process to cleanse and consolidate incoming data. The ETL process will use a reference table to identify which data must be cleansed in the target table. The server that hosts the table restarts daily. You need to minimize the amount of time it takes to execute the query and the amount of time it takes to populate the reference table. What should you do?

You are designing a stored procedure for a database named DB1. The following requirements must be met during the entire execution of the stored procedure: The stored procedure must only read changes that are persisted to the database. SELECT statements within the stored procedure should only show changes to the data that are made by the stored procedure. You need to configure the transaction isolation level for the stored procedure. Which Transact-SQL statement or statements should you run?

You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products. Every evening stores, must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the StoreID, ProductID, Qtysold, Totprodsales, and Datesold columns. You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date. What should you include in your solution?

You have the following stored procedure: The Numbers table becomes unavailable when you run the stored procedure. The stored procedure obtains an exclusive lock on the table and does not release the lock. What are two possible ways to resolve the issue? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

You have a database that users query frequently. The users report that during peak business hours, the queries take longer than expected to execute. A junior database administrator uses Microsoft SQL Server Profiler on the database server to trace the session activities. While performing the trace, the performance of the database server worsens, and the server crashes. You need to recommend a solution to collect the query run times. The solution must minimize the impact on the resources of the database server. What should you recommend?

You have a relational data warehouse that contains 1 TB of data. You have a stored procedure named usp_sp1 that generated an HTML fragment. The HTML fragment contains color and font style. You need to return the HTML fragment. What should you do?