The section of using functions on indexes could do with more explicit and deeper explanation. When you use the function on the index it becomes a full scan of the data instead as the query runner has to run the function on every row and column, effectively removing any benefit of the index.

Unfortunately I learned this the hard way!

Some well known docs on the topic- https://use-the-index-luke.com/sql/where-clause/obfuscation

The given solution (create an indexed UPPER(name) column) is not the best way to solve this, at least not on MS SQL Server. Not sure if this is equally supported in other databases, but the better solution is to create a case-insensitive computed column:

  ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;

The blog has a typo. The first line needs to have the text in uppercase:

> query WHERE name = ‘ABC’

> create an indexed UPPER(name) column

The point is that the index itself is already on the data with the function applied. So it's not a full scan, the way the original query was.

Of course, in this particular example you just want to use a case-insensitive collation to begin with. But the general concept is valid.

"Unfortunately I learned this the hard way!" ... Seems to be the motto of SQL developers.

Otoh, it seems a fairly stable language (family of dialects?) so finding the pitfalls has long leverage