Thank you all of you for your answers. I am sorry I am not able to provide code or exec plans, but I thought they were not relevant for my...

Duncan:

I thought about parameter sniffing, but all resource I found about it was related to stored procedures. Nevertheless, I tried wrapping it in stored procedure that would do exactly what...

In general I agree with you. However, what amazed me the most is the difference between calling function with variables and direct values. Current workaround is to use dynamic sql...

Hi Gail,

This is inline table valued function. It looks pretty much like this:

CREATE FUNCTION [dbo].[SomeTVF]

( @param1 varchar(50)

, @param2 datetime

, @param3 datetime

) RETURNS TABLE AS

RETURN

(

WITH cte as

(

..

)

SELECT cols

FROM dbo.SomeTVF2(@param1, @param2) t1

LEFT...