Microsoft 70-461 Bundle 2021

NEW QUESTION 1

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter.
You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.
Which approach should you use?

• A. A SELECT statement that includes CASE
• B. Cursor
• C. BULK INSERT
• D. View
• E. A user-defined function

Answer: E

Explanation:
SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.

NEW QUESTION 2

You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.
Which code segment should you use?
To answer, type the correct code in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
DECLARE @XML1 XML(Sales.InvoiceSchema)
Reference:
http://msdn.microsoft.com/en-us/library/ms176009.aspx

NEW QUESTION 3

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?

• A. CROSS APPLY
• B. INNER JOIN
• C. OUTER JOIN
• D. PIVOT

Answer: A

Explanation:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSSAPPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
References: https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx

NEW QUESTION 4

A local bank uses a SQL Server database to manage accounts. You are developing a stored procedure that contains multiple Transact-SQL INSERT statements.
The stored procedure must use transaction management to handle errors.
You need to ensure that the stored procedure rolls back the entire transaction if a run-time occurs. Which Transact-SQL statement should you add to the stored procedure?

• A. SET ARITHABORT ON
• B. SET NOEXEC ON
• C. SET TRANSACTION ISOLATION LEVEL ON
• D. SET XACT_ABORT ON

Answer: D

Explanation:
SET XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2021

NEW QUESTION 5

You generate a daily report according to the following query:

You need to improve the performance of the query. What should you do?

• A. Drop the UDF and rewrite the report query as follows: WITH cte(CustomerID, LastOrderDate) AS (SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate] FROM Sales.SalesOrderGROUP BY CustomerID)SELECT c.CustomerName FROM cteINNER JOIN Sales.Customer c ON cte.CustomerID = c.CustomerID WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE())
• B. Drop the UDF and rewrite the report query as follows: SELECT c.CustomerNameFROM Sales.Customer c WHERE NOT EXISTS (SELECT s.OrderDate FROM Sales.SalesOrder sWHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())AND s.CustomerID = c.CustomerID)
• C. Drop the UDF and rewrite the report query as follows: SELECT DISTINCT c.CustomerNameFROM Sales.Customer cINNER JOIN Sales.SalesOrder s ON c.CustomerID = s.CustomerID WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE())
• D. Rewrite the report query as follows: SELECT c.CustomerNameFROM Sales.Customer cWHERE NOT EXISTS (SELECT OrderDate FROM Sales.ufnGetRecentOrders(c.CustomerID, 90)) Rewrite the UDF as follows:CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime) RETURNS TABLE AS RETURN (SELECT OrderDateFROM Sales.SalesOrder sWHERE s.CustomerID = @CustomerIDAND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE())

Answer: A

NEW QUESTION 6

You administer a Microsoft SQL Server 2012 database.
The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)


Unless stated above, no columns in the Employee table reference other tables.
Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.
You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a Primary Key constraint for this table?

• A. DateHired
• B. DepartmentID
• C. EmployeeID
• D. EmployeeNum
• E. FirstName
• F. JobTitle
• G. LastName
• H. MiddleName
• I. ReportsToID

Answer: C

NEW QUESTION 7

You create a view based on the following statement:

You grant the Select permission to User1.
You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.
Which Transact-SQL statement should you use?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

NEW QUESTION 8

You have three tables that contain data for dentists, psychiatrists, and physicians. You create a view that is used to look up their email addresses and phone numbers.
The view has the following definition:

You need to ensure that users can update only the phone numbers and email addresses by using this view. What should you do?

• A. Alter the vie
• B. Use the EXPAND VIEWS query hint along with each SELECT statement.
• C. Create an INSTEAD OF UPDATE trigger on the view.
• D. Drop the vie
• E. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
• F. Create an AFTER UPDATE trigger on the view.

Answer: B

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms187956.aspx

NEW QUESTION 9

You generate a daily report according to the following query:

You need to improve the performance of the query. What should you do?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: A

NEW QUESTION 10

You are developing an SQL Server database for an automobile manufacturer. The manufacturer maintains the list of vehicles sold and vehicles that have been recalled. The tables are shown below:

You have the following Transact-SQL code. Line numbers are included for reference only.

You must update the VehiclesToRecall table with the list of vehicles that were recalled in 2014. You must maximize the performance of the operation.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Reference: MERGE (Transact-SQL) https://msdn.microsoft.com/en-us/library/bb510625.aspx

NEW QUESTION 11

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?

• A. GLOBAL FORWARD_ONLY
• B. LOCAL FAST_FORWARD
• C. LOCAL STATIC READ_ONLY FORWARD_ONLY
• D. GLOBAL STATIC

Answer: B

Explanation:
FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.
References: https://msdn.microsoft.com/en-us/library/ms180169.aspx

NEW QUESTION 12

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 a query that returns each author on a separate row in XML format. Which XML method should you use?

• A. Value()
• B. Nodes()
• C. Query()
• D. Exist()

Answer: B

Explanation:
The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.
The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression, so that subsequent queries can navigate relative to these context nodes.

NEW QUESTION 13

You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition:

You need to create an audit record only when either the MobileNumber or HomeNumber column is updated. Which Transact-SQL query should you use?

• A. CREATE TRIGGER TrgPhoneNumberChangeON Customers FOR UPDATE ASIF COLUMNS_UPDATED (HomeNumber, MobileNumber)- - Create Audit Records
• B. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATEASIF EXISTS( SELECT HomeNumber from inserted) OR EXISTS (SELECT MobileNumber FROM inserted)- - Create Audit Records
• C. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATEASIF COLUMNS_CHANGED (HomeNumber, MobileNumber)- - Create Audit Records
• D. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATEASIF UPDATE (HomeNumber) OR UPDATE (MobileNumber)- - Create Audit Records

Answer: D

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/bb510663.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms186329.aspx

NEW QUESTION 14

You have a data warehouse that contains the data for all the customers of your company.
You need to create a query dynamically generates a SELECT statement from a table named CUSTOMERS. The SELECT statement must generate a full list of columns.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer
area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.



Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
XML PATH

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
In line 7 add XML PATH to get thefollowing line: FOR XML PATH (' ')), 1, 1, ' ') +
Here is how it works:
1. Get XML element string with FOR XML
Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained inthe PATH argument. For example, if we were to run the following statement:
SELECT ',' + name
FROM temp1
FOR XML PATH ('')
By passing in a blank string (FOR XML PATH('')), we get the following instead:
,aaa,bbb,ccc,ddd,eee
2. Remove leading commawith STUFF
The STUFF statement literally "stuffs” one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.
SELECT abc = STUFF( ( SELECT ',' +NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1
Note: The full code will be: SELECT 'SELECT' + STUFF ((
SELECT ', [' + name + ']' FROM
WHERE id = OBJECT_ID('Customers') AND
…name <> 'me'
FOR XML PATH (' ')), 1, 1, ' ') +
'FROM[Customers] '
References:
http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server

NEW QUESTION 15

You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit button.)

You need to create a query that meets the following requirements:
References columns by using one-part names only.
Groups aggregates only by SalesTerritoryID, and then by ProductID.
Orders the results in descending order by SalesTerritoryID and then by ProductID in descending order for both.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
SELECT SalesTerritoryID, ProductID,
AVG(UnitPrice), MAX(OrderQty) MAX(DiscountAmount) FROM Sales.Details
GROUP BY SalesTerritoryID, ProductID
ORDER BY SalesTerritoryID DESC, ProductID DESC

NEW QUESTION 16

You need to create a table named OrderDetails that contains the following columns: LineItemTotal, ListPrice, and Quantity. LineItemTotal stores the product of ListPrice and Quantity for each row.
Construct the Transact-SQL for creating the table by using the following guidelines:
Ensure that the calculation for a line item total is not run every time the table is queried.
Do not use any object delimiters.
Store LineItemTotal as the last column in the table.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the Transact-SQL in the answer area that resolves the problem and meets the stated goals or requirements. You can add Transact-SQL within the Transact-SQL segment that has been provided as well as below it.



Use the ‘Check Syntax’ button to verify your work. Any syntax or spelling errors will be reported by line and character position.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
1. CREATE TABLE OrderDetails 2. (
3. ListPrice money NOT NULL,
4. Quantity int NOT NULL,
5. LineItemTotal AS (ListPrice * Quantity) PERSISTED 6. )
To line 5 add: LineItemTotal AS (ListPrice * Quantity) PERSISTED

NEW QUESTION 17

You have a database named Sales that contains the tables shown in the exhibit. (Click the Exhibit button.)

You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure.
You need to create a stored procedure named usp_Customers for App1 that returns only the number of rows specified by the @Count parameter.
The solution must NOT use BEGIN, END, or DECLARE statements.
Part of the correct Transact-SQL statement has been provided in the answer area. Complete the Transact-SQL statement

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
CREATE PROCEDURE usp_Customers @Count int SELECT TOP(@Count)
Customers.LastName FROM Customers
ORDER BY Customers.LastName

NEW QUESTION 18

Your database contains a table named Products that has columns named ProductID and Name.
You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time. You need to view rows 31 through 45.
Which Transact-SQL query should you create?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: C

Explanation:
The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
Example: Skip first 10 rows from the sorted resultset and return next 5 rows.
SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
References: https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

NEW QUESTION 19

You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.
The table contains a unique constraint on the combination of ProductName and CreatedDateTime. You need to modify the Products table to meet the following requirements:
Remove all duplicates of the Products table based on the ProductName column.
Retain only the newest Products row.
Which Transact-SQL query should you use?

• A. WITH CTEDupRecords AS(SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName HAVING COUNT(*) > 1)DELETE pFROM Products pJOIN CTEDupRecords cte ON
• B. ProductName = cte.ProductNameAND p.CreatedDateTime > cte.CreatedDateTime
• C. WITH CTEDupRecords AS(SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName HAVING COUNT(*) > 1)DELETE pFROM Products pJOIN CTEDupRecords cte ON cte.ProductName = p.ProductNameAND cte.CreatedDateTime > p.CreatedDateTime
• D. WITH CTEDupRecords AS(SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName)DELETE pFROM Products pJOIN CTEDupRecords cte ON
• E. ProductName = cte.ProductName
• F. WITH CTEDupRecords AS(SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName FROM ProductsGROUP BY ProductName HAVING COUNT(*) > 1)DELETE pFROM Products pJOIN CTEDupRecords cte ON
• G. ProductName = cte.ProductName

Answer: B

NEW QUESTION 20

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?

• A. Exist()
• B. Nodes()
• C. Query()
• D. Value()

Answer: D

Explanation:
References:
https://docs.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-2021

NEW QUESTION 21

You develop a Microsoft SQL Server 2012 database.
You need to create a batch process that meets the following requirements:
Returns a result set based on supplied parameters.
Enables the returned result set to perform a join with a table. Which object should you use?

• A. Inline user-defined function
• B. Stored procedure
• C. Table-valued user-defined function
• D. Scalar user-defined function

Answer: C

NEW QUESTION 22

You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.
You need to retrieve the students who scored the highest marks for each subject along with the marks. Which Transact-SQL query should you use?

• A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarksGROUP BY StudentCode
• B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS RankFROM StudentMarks
• C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) ASValueFROM StudentMarks GROUP BY StudentCode
• D. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarksGROUP BY StudentCode
• E. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS RankFROM StudentMarks) tmp WHERE Rank = 1
• F. SELECT StudentCode AS Code, Marks AS Value FROM (SELECT StudentCode, Marks AS Marks,RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS RankFROM StudentMarks) tmp WHERE Rank = 1
• G. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS RankFROM StudentMarks) tmp WHERE Rank = 1
• H. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks,RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS RankFROM StudentMarks) tmp WHERE Rank = 1

Answer: F

NEW QUESTION 23

You are developing a SQL Server database for an order management system. The database contains a table that is defined by the following Transact-SQL statement:

Transactions must commit if there are no errors. Transactions must roll back if constraint violations occur. You need to create the Transact-SQL script to insert new orders.
How should you complete the relevant Transact-SQL script? To answer, select the appropriate Transact-SQL statements from each list in the answer area.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: SET XACT_ABORT ON;
XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
When SET XACT_ABORT is ON, if a Transact-SQL statement raises arun-time error, the entire transaction is terminated and rolled back.
Box 2: IF (XACT_STATE()) =-1
If XACT_STATE has the value of -1, then the current request has an active user transaction, but an error has occurred that has caused the transaction to beclassified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction.
Box 3: IF (XACT_STATE()) =1
If XACT_STATE has the value of 1, then the current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ms189797.aspx

NEW QUESTION 24

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.
<CUSTOMERS Name="Customer A" Country="Australia">
<ORDERS OrderID="1" OrderDate="2001-01-01" Amount="3400.00" />
<ORDERS OrderID="2" OrderDate="2002-01-01" Amount="4300.00" />
</CUSTOMERS>
Which Transact-SQL query should you use?

• A. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML RAW
• B. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS
• C. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO
• D. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS
• E. SELECT Name, Country, OrderId, OrderDate, AmountFROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO
• F. SELECT Name, Country, OrderId, OrderDate, AmountFROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML AUTO, ELEMENTS
• G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1FOR XML PATH ('Customers')
• H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

Answer: E

NEW QUESTION 25

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:

Which two actions should you perform? Each correct answer presents part of the solution.

• A. Add the CHECK constraint to the table structure.
• B. Add the DATEPART function to the stored procedure.
• C. Add the DEFAULT constraint to the table structure.
• D. Add the SYSDATETIMEOFFSET function to the stored procedure.
• E. Add the ISDATE function to the stored procedure.

Answer: DE

Explanation:
SYSDATETIMEOFFSET returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
ISDATE returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0. References:https://msdn.microsoft.com/en-us/library/bb677334.aspx

NEW QUESTION 26

You develop a Microsoft SQL Server 2012 database. You create a view that performs the following tasks: Joins 8 tables that contain up to 500,000 records each.
Performs aggregations on 5 fields.
The view is frequently used in several reports.
You need to improve the performance of the reports. What should you do?

• A. Convert the view into a table-valued function.
• B. Convert the view into a Common Table Expression (CTE).
• C. Convert the view into an indexed view.
• D. Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary table.

Answer: C

Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms191432.aspx

NEW QUESTION 27

You develop a SQL Server database for a large multinational company.
You must develop a stored procedure to loop through employee information and return data for one employee at a time.
How should you complete the stored procedure? To answer, drag the appropriate Transact-SQL segment to the correct locations in the answer area. Each Transact-SQL segment 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.

• A. Mastered
• B. Not Mastered

Answer: A

Explanation:
Box 1: CURSOR VARYING OUTPUT
Transact-SQL stored procedures can use the cursor data type only forOUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.
Box2: FORWARD_ONLY STATIC Box 3: CLOSE
Box 4: DEALLOCATE
References:https://technet.microsoft.com/en-us/library/ms175498(v=sql.105).aspx

NEW QUESTION 28
......