Filters in the Where clause which are considered bad for performance (and avoidable)

[Isaac]

Up to this point, I've had a sort of "general rule" for myself. A rule of thumb if you will, not specifically defensible in every situation, but one that I thought was true often enough to just avoid doing it rather than take the effort to assess each time.

And that is, to avoid using Functions in the Where clause, such as:

where Year(columnName) = 2022
or
where IsNull(columnName,'')=''

Preferring instead to replace each of those, respectively, with something likely about like this:
where (columnName between '2022-01-01' and '2022-12-31 11:59:59.999')
and
where columnName is null or columnName = ''

Because it was my understanding, doubtless a comment made by someone more skilled than I a long time ago, that such functions used in the Where clause will often require a complete table scan. (which kind of makes sense to my brain logically).

However, I was just reviewing a junior colleague's query to make suggestions, and their Where clause has where datepart(year,columnname) = 2022

Wanting to be more sure before I said anything, I checked the execution plan, and I don't see anything that indicates to me a labor-intensive occurrence connected to that filter.

Am I missing anything, or has SQL Server 'improved' that area, was I always wrong the whole time, or does it "just depend" a bit more than I thought?

 

[ebs17]

A calculation on a table field, i.e. also a function on the table field, prevents the use of an index and is therefore fundamentally a worse choice.

 

[Isaac]

Oops, let me add one more thing.

Here are the properties of that Node from the actual execution plan

A calculation on a table field, i.e. also a function on the table field, prevents the use of an index and is therefore fundamentally a worse choice.

Ok, thank you for that reminder. This only applies I think if the index is actually on the column that the calculation is being done on - i.e. it doesn't necessarily prevent the utilization of any and all indexes during the query run? At least the column in question would need to be included in an index in order for the performance loss to exist right?

 

[ebs17]

Of course, this only affects the index on the field used.
Whether an index is actually used also depends on the show plan created by the optimizer. By some preceding optimized actions, this index can be considered unimportant.

 

[SQL_Hell]

It is because the row count is small, so the filtering isn't an issue. Date functions also tend to be quite efficient, if it was a returned rowcount of 10,000 for example from a table of millions and it was a string function used in the JOIN then you would see the table scanning type behaviour.