I have a TVF function which has the below code in it.I stripped down the function and has the logic as shown below.
WITH X AS (SELECT cis.ProductionStatus AS CurrentDiaryStatus, (SELECT DiaryDateTime FROM Diary r WHERE (r.BatchNumber = 'N0000065') AND r.ItemCode = '33568') AS DiaryDateTime FROM CurrentDiaryStatus cis INNER JOIN Diary r WITH (NOLOCK) ON (cis.DiaryId = r.Id) WHERE (r.BatchNumber = 'N0000065') AND r.ItemCode = '33568') SELECT X.CurrentDiaryStatus, X.DiaryDateTime, CA4.DiaryMaxDate FROM X OUTER APPLY (VALUES(DATEADD(DAY, 30, X.DiaryDateTime))) CA1(DiaryMaxDate) OUTER APPLY (VALUES(DATEADD(DAY, 30 * CEILING(( IIF(CAST(GETDATE() AS TIME) > CAST(CA1.DiaryMaxDate AS TIME), 1, 0) + DATEDIFF(DAY, CA1.DiaryMaxDate, GETDATE()) ) / 30.0), CA1.DiaryMaxDate))) CA2(DiaryMaxDate) OUTER APPLY (SELECT datevalue FROM dbo.CastOfDate((SELECT TOP 1 pj.Date FROM ProductionJournalPage_ProductionJournal pj INNER JOIN ProductionJournalPages p ON pj.PageId = p.Id INNER JOIN Event e ON p.EventId = e.Id INNER JOIN Diary r WITH (NOLOCK) ON (e.DiaryId = r.Id) WHERE (r.BatchNumber = 'N0000065' AND r.ItemCode = '33568') AND pj.ReviewType = 'Sup Monthly Review' ORDER BY pj.Date DESC))) CA3(ProductionJournalDate) OUTER APPLY (VALUES ( CASE WHEN ( DATEADD(DAY, 30, X.DiaryDateTime) < GETDATE() AND ( CA3.ProductionJournalDate IS NULL OR DATEADD(DAY, 30, CA3.ProductionJournalDate) < GETDATE() ) AND DATEADD(DAY, 14, CA2.DiaryMaxDate) > DATEADD(DAY, 30, GETDATE()) ) THEN DATEADD(DAY, -30, CA2.DiaryMaxDate) WHEN( ( CA3.ProductionJournalDate IS NOT NULL ) AND ( DATEADD(DAY, 30, CA3.ProductionJournalDate) >= CA2.DiaryMaxDate ) ) THEN DATEADD(DAY, 30, CA2.DiaryMaxDate) ELSE CA2.DiaryMaxDate END )) CA4(DiaryMaxDate);
I am returning DiaryMaxDate based on some calculation and it all works fine except the below issue. I see that the execution plan for this query has Explicit conversion warnings.The conversion is happening for the date column pj.Date column. The table ProductionJournalPage_ProductionJournal has the column as varchar and so i am converting the pj.Date column to Datetime using another TVF functiuon CastOfDate. I have the column pj.Date converted to datetime and called as CA3(ProductionJournalDate) and i am using it in the outer apply for some calculations.
Even though i am converting it explicitly,why am i getting conversion warnings again ,saying that SQL has to convert it.??
This is the conversion warning from SQL execution plan.
Type conversion in expression (CONVERT(datetime,CONVERT_IMPLICIT(varchar(50),[Expr1031],0),0)) may affect "CardinalityEstimate" in query plan choice
Expr1031 is the fully qualified column name for the Date.
CastOfDate is another TVF function which cast string date values to datetime. Here is its definition.
CREATE FUNCTION [dbo].[CastofDate] ( @stringvalue varchar(50) ) RETURNS TABLE AS RETURN ( SELECT "DateValue" = CASE WHEN ISDATE(@stringvalue) != 0 THEN CAST(@stringvalue AS DATETIME) ELSE NULL END ); GO
I know that i can ignore this warnings if the performance is not bad,but i have couple of functions similar to this which calculates few dates and i am referencing those dates in another query which is a complex one and i there i see lots(~20) of these implicit conversion warnings and makes the query slow.
To repeat the question again
Even though i am converting it explicitly,why am i getting conversion warnings again ,saying that SQL has to convert it again.?
I don't think it is duplicate because ,I am explicitly converting the field to avoid the conversion sql server has to do or to avoid the implicit conversion also i am not inserting any values instead i am reading the data.