Problem
Rule 12 checks whether a function wraps a column. Its column check, ColumnReferenceRegex in PlanAnalyzer.cs, needs a dotted name, such as [db].[dbo].[T].[c] or [v].[X].
On SQL Server 2022 (16.0.4255.1), the column of a table variable with no alias has no dotted name. The plan shows only the column name:
| Query |
Predicate in the plan |
Warnings |
SELECT X FROM @tv WHERE ABS(X) = 1 |
abs([X])=(1) |
Scan With Predicate, Table Variable |
SELECT v.X FROM @tv AS v WHERE ABS(v.X) = 1 |
abs(@tv.[X] as [v].[X])=(1) |
Non-SARGable Predicate, Table Variable |
Both queries put a function on the column. Only the second query gets the Non-SARGable warning.
The comment on ColumnReferenceRegex says that a table-variable column renders as [@tv].[col]. SQL Server 2022 does not render it that way. With an alias, it renders @tv.[X] as [v].[X], and the alias part matches. Without an alias, it renders [X]. I did not check older versions.
Steps to reproduce
-
Run this batch and capture the actual plan of the SELECT:
DECLARE @tv table (X integer NOT NULL, S varchar(20) NOT NULL);
INSERT @tv (X, S) VALUES (1, 'a'), (2, 'b');
SELECT X FROM @tv WHERE ABS(X) = 1;
-
Run planview analyze on the plan.
A possible fix
A name in one pair of brackets is not always a column. [Expr1003] is an expression, and [@p] is a parameter. So the check must not count every such name as a column. A narrower rule applies only to a scan of a table variable. There, count a name in one pair of brackets in the predicate as a column of that table variable. The plan shows the table variable in the scan's object: <Object Table="[@tv]">.
Found while working on #558.
Problem
Rule 12 checks whether a function wraps a column. Its column check,
ColumnReferenceRegexinPlanAnalyzer.cs, needs a dotted name, such as[db].[dbo].[T].[c]or[v].[X].On SQL Server 2022 (16.0.4255.1), the column of a table variable with no alias has no dotted name. The plan shows only the column name:
SELECT X FROM @tv WHERE ABS(X) = 1abs([X])=(1)SELECT v.X FROM @tv AS v WHERE ABS(v.X) = 1abs(@tv.[X] as [v].[X])=(1)Both queries put a function on the column. Only the second query gets the Non-SARGable warning.
The comment on
ColumnReferenceRegexsays that a table-variable column renders as[@tv].[col]. SQL Server 2022 does not render it that way. With an alias, it renders@tv.[X] as [v].[X], and the alias part matches. Without an alias, it renders[X]. I did not check older versions.Steps to reproduce
Run this batch and capture the actual plan of the SELECT:
Run
planview analyzeon the plan.A possible fix
A name in one pair of brackets is not always a column.
[Expr1003]is an expression, and[@p]is a parameter. So the check must not count every such name as a column. A narrower rule applies only to a scan of a table variable. There, count a name in one pair of brackets in the predicate as a column of that table variable. The plan shows the table variable in the scan's object:<Object Table="[@tv]">.Found while working on #558.