Blog

Scalar Functions and CASE: Strings, Date/Time, Type Conversion and Sargability

Scalar functions are handy, but wrapped around a column in WHERE they make a query non-sargable: on 200,000 rows a YEAR()/MONTH() filter took 402 logical reads, the same month asked with a half-open range took 21. We cover CONCAT/CONCAT_WS, LEN versus DATALENGTH, CAST/CONVERT/TRY_CAST, how an nvarchar parameter loses the index through CONVERT_IMPLICIT, DATEDIFF counting boundaries, and the type and ELSE traps of CASE.