Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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