0

I have a select statement in SQL Server that must compare a large set of rows against a single calculated date.

The calculation itself is negligible; just looking for the prior Monday. Given that the column this calculated date will be matched against is indexed, is there any difference whether I pass in the calculated date from application code as a parameter or use a scalar function to calculate the date?

Said another way: should I favor using a param or calling a scalar function, or are they basically equal in this scenario?

1 Answer 1

1

It sounds like a situation where you could potentially fall prey to - or benefit from - parameter sniffing. In a nutshell, SQL Server will use parameter/variable values when compiling an execution plan in order to determine optimal index usage (based on column statistics). Depending on the value that's passed in, you could get very different execution plans, and very poor performance if the "wrong" value is used later. (A nice article about it.).

I'd personally calculate the date within the procedure using a scalar function, unless there's a need for whatever is calling this procedure to specify the date itself. Then you could always use the scalar function in the procedure to fall back on a default value. As long as parameter sniffing isn't giving you surprising performance changes, it's mostly just a matter of application design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.