DF116: Non-scalar subquery is used instead of a scalar subquery.

The topic describes the DF116 T-SQL code analysis rule.

Category

EXECUTION RULES

Message

Non-scalar subquery is used instead of a scalar subquery.

Description

Using a non-scalar result in a context that anticipates a scalar result can result in errors.

Additional information

Non-scalar subqueries return multiple rows, which can cause performance degradation, especially if they are used in contexts where a single value is expected. The database engine must process and evaluate all rows returned by the subquery, potentially resulting in unnecessary overhead.

Noncompliant code example

IF (SELECT Value FROM dbo.DemoTable WHERE Code = 'Demo') = 1
SELECT 'Data found'
GO
 
SELECT Code
,(SELECT Value FROM dbo.DemoTable WHERE Code = 'Demo')
FROM dbo.DemoTable
GO

Compliant solution

IF (SELECT TOP(1) Value FROM dbo.DemoTable WHERE Code = 'Demo') = 1
SELECT 'Data found'
GO
 
SELECT Code
    ,(SELECT TOP(1) Value FROM dbo.DemoTable WHERE Code = 'Demo')
FROM dbo.DemoTable
GO
 
IF (SELECT max(Value) FROM dbo.DemoTable WHERE Code = 'Demo') = 1
SELECT 'Data found'
GO
 
SELECT Code
    ,(SELECT max(Value) Value FROM dbo.DemoTable WHERE Code = 'Demo')
FROM dbo.DemoTable
GO