>If you guarantee that you use case-insensitive server collation (which is default installation) you don't need to use UPPER() function. If there is a possibility of case-sensitive installation, you need to use COLLATE (and I think if we add it, it will make it back non-sargable - need to verify it).
That is correct. I do not need to have the UPPER. I will make an adjustment at one place where I was still using it. In most other places, I was not using it. I also did a test with the execution plan. It does return a completely different result when I remove the UPPER. So, I guess on the long run, on some specific queries, I could have seen a slow down. It just didn't happen until now even with a few million records. So, maybe the difference is not that big. But, I would prefer to fix it so to make sure it will not cause a problem on the long run.