Developing Microsoft SQL Server Databases — Question 48

You execute IndexManagement.sql and you receive the following error message: "Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, != ,<, <= , >, > = or when the subquery is used as an expression."
You need to ensure that IndexManagement.sql executes properly.
Which WHILE statement should you use at line 18?

Answer options

• A. WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)
• B. WHILE @counter < (SELECT SUM(RowNumber) FROM @indextable)
• C. WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)
• D. WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)

Correct answer: D

Explanation

The correct answer is D because it ensures that the loop continues as long as @counter is less than the total count of RowNumber in @indextable, which is a single value. Options A, B, and C either use aggregation incorrectly or lead to multiple values being returned, which would not resolve the original error.