NEW QUESTION 125You need to create a database object that meets the following requirements:– accepts a product identifies as input– calculates the total quantity of a specific product, including quantity on hand and quantity on – order– caches and reuses execution plan– returns a value– can be called from within a SELECT statement– can be used in a JOIN clauseWhat should you create?

A. a temporary table that has a columnstore indexB. a user-defined table-valued functionC. a memory-optimized table that has updated statisticsD. a natively-complied stored procedure that has an OUTPUT parameter

Answer: BExplanation:A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

NEW QUESTION 126Drag and DropYou have a database containing the following tables:ServersErrorsYou have a user-defined, scalar function named IPLookup that takes a DNS name as a parameter and returns the IP address of the server. You have an additional user-defined, scalar function named DNSLookup, that takes an IP address as a parameter and returns a DNS name. You create a view named vwErrors by running the following Transact-SQL statement:You need to insert data by using the view. How should you complete the Transact-SQL statement? (To answer, drag the appropriate Transact-SQL segments to the correct location. Each Transact-SQL segments may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)

NEW QUESTION 127HotspotYou query a database that includes two tables: Project and Task. The Project table includes the following columns:The Task table includes the following columns:Users report performance issues when they run the following query:You need to improve query performance and limit results to projects that specify an end date. How should you complete the Transact-SQL statement? (To answer, select the appropriate Transact-SQL segments in the answer area.)

NEW QUESTION 128You are building a stored procedure that will update data in a table named Table1 by using a complex query as the data source. You need to ensure that the SELECT statement in the stored procedure meets the following requirements:– Data being processed must be usable in several statements in the stored procedure.– Data being processed must contain statistics.What should you do?

A. Update Table1 by using a common table expression (CTE).B. Insert the data into a temporary table, and then update Table1 from the temporary table.C. Place the SELECT statement in a derived table, and then update Table1 by using a JOIN to the derived table.D. Insert the data into a table variable, and then update Table1 from the table variable.

NEW QUESTION 129You have a disk-based table that contains 15 columns. You query the table for the number of new rows created during the current day. You need to create an index for the query. The solution must generate the smallest possible index. Which type of index should you create?

A. clusteredB. filtered nonclustered with a getdate() predicate in the WHERE statement clauseC. hashD. nonclustered with compression enabled

Answer: BExplanation:A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes. Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary.https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes

NEW QUESTION 130HotspotYou need to develop a Transact-SQL statement that meets the following requirements:– The statement must return a custom error when there are problems updating a table.– The error number must be the value 50555.– The error severity level must be 14.– A Microsoft SQL Server alert must be triggered when the error condition occurs.Which Transact-SQL segment should you use for each requirement? (To answer, select the appropriate Transact-SQL segments in the answer area.)

NEW QUESTION 131HotspotYou have two tables as shown in the following image:You need to analyze the following query. (Line numbers are included for reference only.)Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

NEW QUESTION 132Drag and DropYou have a database containing the following tables:ServersErrorsYou have a user-defined, scalar function named IPLookup that takes a DNS name as a parameter and returns the IP address of the server. You have an additional user-defined, scalar function named DNSLookup, that takes an IP address as a parameter and returns a DNS name. You create a view named vwErrors by running the following Transact-SQL statement:You need to insert data by using the view. How should you complete the Transact-SQL statement? (To answer, drag the appropriate Transact-SQL segments to the correct location. Each Transact-SQL segments may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)