Home Forums SQL Server 2008 T-SQL (SS2K8) How to find the year to date value for a given date RE: How to find the year to date value for a given date

  • aaron.reese - Tuesday, September 26, 2017 8:14 AM

    @ Kevin,

    You can't leave a statement like that hanging 🙂  You need to explain WHY it is a bad idea.

    Nah. Everyone should know who I am and just push the "I Believe" button!! 😎

    Functions around columns in the WHERE clause carry several often horrifically bad performance implications. Among them are preventing acquisition of valid statistics, and when the optimizer is guessing BAD things can happen with your query plan. They also void the use of an index seek where it is appropriate, and the corresponding index scan (sometimes even a full table scan when combined with bad estimated row counts) carry several BAD things such as hammering the IO system, flushing useful hot pages out of the buffer pool (potentially crushing the entire SQL Server's performance - not just your database application), and the scan taking a shared read lock on the entire index thus dropping your concurrency in the toilet for that object. Add in the CPU burn from actually computing said function(s) on ALL rows, often combined with large HASH joins and your CPU takes a pounding as well. 

    Hopefully that is enough for those few that DON'T know who I am and push the "I Believe" button to jump through any hoops necessary to avoid functions around columns in the WHERE clause. Just in case it isn't, I will add that in my almost 25 years of consulting on SQL Server (and ONLY SQL Server) this issue is the #2 performance problem I have seen in aggregate (with mismatched datatypes being #1 and suboptimal indexing being a close #3).

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service