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 YEAR() or UPPER(), the optimizer cannot leverage the sorted index to quickly locate rows.
Takeaway
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)
1 Comment. Leave new
Long time learner from your videos and posts, but first time post! :-)
I never thought of this… and it makes sense. So, let’s say I (regularly 😬) do this:
WHERE CAST(FooDateTime as Date) BETWEEN ‘2023-01-01’ AND ‘2023-01-31’
I’m passing in the BETWEEN dates as params, and I do this so I can get all records in the month of January…
This might not use an index because it’s casting the FooDateTime to a date first? So a better solution might be
WHERE FooDateTime BETWEEN ‘2023-01-01’ and DATEADD(DAY, 1, ‘2023-01-31’)?
(again, dates passed in as params)