This changed the run time of the query from about a minute to a few seconds. So this shows that a RBAR table valued function can be much more efficient than using a lookup ref table. BTW the time taken to generate the TVF with 31 days is a few micro seconds and is totally insignificant compared to the rest of the query.
So there is an example where an RBAR TVF is much more efficient than a lookup table.
We have to take your word for it as it is impossible to verify the initial table was set up properly. It would need to be as small as possible and be indexed on the field you match to be any good. Also the datatypes of the field you match against must be the same as that of the variable, but I will assume you know all that.
If you don't consider this:
What might seem as a constant for us humans with respect to the query will not be so for the optimizer as it plans at the batch level and reuses those plans. Thus any local variable or parameter is NOT a constant in your query plan! SQL Sever is improving in this respect, but its remains tricky and counter intuitive. What problably happended in your case is that each row of the input table is first converted before the comparison (check the queryplan)! With a TVF you only have to do such conversion once and the comparisons for each record takes place against your generated table that has a matching type as output.
RIGHT JOIN RefDates C
ON B.Date = C.Date
WHERE C.Date >= @StartDateTime
AND C.Date < @EndDateTime
why did you not use in the original query something like this?
RIGHT JOIN RefDates C
ON B.Date = C.Date and C.Date >= @StartDateTime AND C.Date < @EndDateTime
But in all honesty, I simply think you ran out of luck. I had it once with a stored procedure that had a paramterised query. No matter what I did, it was extremely slow compared to a test query with constants I ran before. All types matched and still I had no luck. Matching against parameters or local variables sometimes has these effects (only seen it twice I think with such a big effect). I did the counter intuitive, and created a dynamic SQL statement so all the parameters became constants and ran that instead, and it was rocking (and ugly)!
As for RBAR defeats persistent table, that is a conculsion I would not draw based on your experience. You can ofcourse still use a numbers table to do only one insert within your stored procedure and it would be faster then RBAR. The true improvement comes some something else, and with some tweaking you will find that to be the case.
Also not long ago I optimised a complex query that was using a list of numbers that needed splitting. I currenly have two versions of the appropiate function. An inline version and one that creates a small table in the temp DB as it is not inline. Both use a numbers function and both where much faster then the procedural version I once used. But the non-inline version was still faster in this particular query, sometimes you have you have to break queries down a little to help the optimizer make sound decissions.