I have previously written a blog post, SQL SERVER Performance: Functions in WHERE Clause, which got lots of attention and comments. Few even asked me to build a video on this topic. Hence, I decided to make a Function in the WHERE Clause – SQL in Sixty Seconds 201.
Here is the video:
Here is the summary of the video.
Why Functions Hurt Performance
Functions in WHERE clauses hinder performance because indexes store data based on the raw column values. By applying a function like
UPPER(), the optimizer cannot leverage the sorted index to quickly locate rows.
Avoid using functions on columns in SQL WHERE clauses when possible. Filter directly on the columns for much faster query performance.
I also offer a Comprehensive Database Performance Health Check service. The Comprehensive Database Performance Health Check is a highly sought-after service that addresses SQL Server performance issues. Clients consistently choose this comprehensive solution when they encounter performance challenges. As the sole expert behind this service, I am here to collaborate with you in building a tailored action plan for resolution, which typically takes 2 to 4 hours, depending on the complexity of your system. You can hire me if your system faces SQL Server Performance Tuning issues.
Reference: Pinal Dave (https://blog.sqlauthority.com)