Microsoft Querying Microsoft SQL Server 2012 Set 3

You are maintaining a Microsoft SQL Server database. You run the following query: You observe performance issues when you run the query. You capture the following query execution plan: You need to ensure that the query performs returns the results as quickly as possible. Which action should you perform?

You are developing a database in SQL Server 2012 to store information about current employee project assignments. You are creating a view that uses data from the project assignment table. You need to ensure that the view does not become invalid if the schema of the project assignment table changes. What should you do?

You are maintaining a Microsoft SQL Server database that stores order information for an online store website. The database contains a table that is defined by the following Transact-SQL statement: You need to ensure that purchase order numbers are used only for a single order. What should you do?

You are working with a table that has an XML column that contains information about books. Each book may have an associated price. You need to write with a query that returns the price of each book as a non-xml value. Which XML method should you use?

You plan to write a query for a new business report that will contain several nested queries. You need to ensure that a nested query can call a table-valued function for each row in the main query. Which query operator should you use in the nested query?

You are designing a table for a SQL Server database. The table uses horizontal partitioning. You have the following requirements:
Each record in the table requires a unique key.
You must minimize table fragmentation as the table grows.
You need to choose the appropriate data type for the key value.
What should you do?

You develop a stored procedure for a wholesale glass manufacturing company. The stored procedure uses a cursor to read all row-based combinations of the following tables:
You observe that the stored procedure returns results very slowly.
You need to improve the execution speed of the stored procedure.
Which cursor type should you use?

You have several SQL Server queries. You plan to optimize the queries to improve performance. You run the queries in SQL Server Management Studio. You need to compare query runs to expose the indexing issues of the queries. Which three actions should you perform from SQL Server Management Studio? Each correct answer presents part of the solution.

You have a vendor application that uses a scalar function. You discover that the queries for the application run slower than expected. You need to gather the runtime information of the scalar function. What are two possible ways to achieve this goal? Each correct answer presents a complete solution.

You need to build a table structure for a stored procedure to support data entry from a website form. The website form must meet the following requirements: Users must validate their age as 18 or older to use the website. Users who leave the date of birth field blank, or who enter an invalid date, must receive an error message. Which two actions should you perform? Each correct answer presents part of the solution.