I would like someone to explain to me how each of the two queries below would generate their execution plan in SQL Server where my function
[dbo].[GetAdditionalProjectDatesForCalendar] is an ITVF (inline table-valued function):
sp_executesql N'SELECT * FROM [dbo].[GetAdditionalProjectDatesForCalendar] (@Parameter1,@Parameter2)', @Parameter1 = 'Value1', @Parameter2 = 'Value2'
SELECT * FROM [dbo].[GetAdditionalProjectDatesForCalendar] ('Value1','Value2')
The Dynamic SQL is what's generated from a C# application that I'm working on and while it performs just as well as the Inline SQL most of the time it performs poorly for certain values of
I've read up on how this is caused by Parameter Sniffing and I've tried using
option(recompile) with the Dynamic SQL but that hasn't improved the performance entirely.
I feel like I need to find a way to force C# to create the Inline SQL so that it will use the exact execution plan for the given parameters instead of creating one on the fly with
option(recompile). I also think I can do this by writing my dynamic sql without using parameterization but I know that would introduce a SQL Injection risk.