SR0015: Extract deterministic function calls from WHERE predicates
RuleId |
SR0015 |
Category |
Microsoft.Performance |
Breaking Change |
Non-breaking |
Cause
A WHERE predicate contains one or more deterministic function calls.
Rule Description
In a WHERE predicate, a function call is deterministic if its value does not depend on the selected data. Such calls could cause unnecessary table scans, which decrease database performance.
How to Fix Violations
To resolve this issue, you can assign the result of the call to a variable that you use in the WHERE predicate.
When to Suppress Warnings
You might suppress this warning if the table or tables that the WHERE predicate references will never contain more than a few rows.
Example
In the first example, the stored procedure includes a deterministic function call, ABS(@param1), in the WHERE predicate. In the second example, a temporary variable holds the result of the call.
CREATE PROCEDURE [dbo].[Procedure2WithWarning]
@param1 INT = 0,
AS
BEGIN
SELECT [c1], [c2], [c3], [SmallString]
FROM [dbo].[Table1]
WHERE [c2] > ABS(@param1)
END
CREATE PROCEDURE [dbo].[Procedure2Fixed]
@param1 INT = 0,
AS
BEGIN
DECLARE @AbsOfParam1 INT
SET @AbsOfParam1 = ABS(@param1)
SELECT [c1], [c2], [c3], [SmallString]
FROM [dbo].[Table1]
WHERE [c2] > @AbsOfParam1
END