1

I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing. I am getting this warning in an execution plan and when i checked this,i learned that i get this warning for a column in a view i use in that query. So i checked that view and i see that CAST(colname AS INT) on that column to convert it into INT. So explicitly conversion is implemented,then why am i getting this error?

Any suggestion?Am i wrong in understanding that warning.?

For the downvote, I posted this question because,i did my research why implicit conversion is happening and i got to know from different posts ,that if we use a CAST or CONVERT we can avoid that.I have used a CAST to convert,but still that warning is there.That is the reason i posted this question.

Please find the execution plan below.

Execution plan

Here is the SQL View

CREATE VIEW [dbo].[FormPermissions_test]
AS
SELECT DISTINCT Form.Id, permitted.AgencyOri, permitted.UserId
        FROM Report  WITH (NOLOCK)    
        INNER JOIN Form  WITH (NOLOCK)
        on Form.ReportId = Report.Id
        INNER JOIN 
        ( SELECT DISTINCT Report.Id, granted.AgencyOri, 
        CAST(granted.[User] as int) as UserId 
                FROM Report WITH (NOLOCK)       
                INNER JOIN Form WITH (NOLOCK)
                ON Form.ReportId = Report.Id
                INNER JOIN IncidentDetailsPages Page WITH (NOLOCK)
                ON Page.FormId = Form.Id
                INNER JOIN IncidentDetailsPage_GrantedUserDetails granted WITH (NOLOCK)
                ON Page.Id = granted.PageId
                WHERE GETDATE() <= COALESCE(
                CASE WHEN ISDATE(granted.ExpirationDate) != 0 THEN  CAST(granted.ExpirationDate AS DATETIME)
                ELSE NULL END,GETDATE())
                    AND granted.[User] IS NOT NULL
                    AND granted.AgencyOri != ''
        ) permitted     
    ON permitted.Id = Report.Id
UNION
        SELECT DISTINCT Form.Id, InternalAffairs.AgencyOri, InternalAffairs.UserId
        FROM Report  WITH (NOLOCK)   
        INNER JOIN Form  WITH (NOLOCK)
        on Form.ReportId = Report.Id
        INNER JOIN
    ( SELECT UserId, AgencyOri
      from [dbo].UserPermissions WITH (NOLOCK)
      where GETDATE() BETWEEN StartDate AND COALESCE( ExpirationDate, GETDATE() )
      --AND IsInvalid != TRUE
      AND ( PersonaGroup = 'ADMIN' OR Claim = 'MANAGEINTERNALAFFAIRSINCIDENT' )) InternalAffairs
    ON Report.AgencyOri = InternalAffairs.AgencyOri
    where SecurityLevel = 'InternalAffairs'
UNION
        SELECT DISTINCT Form.Id, Protected.AgencyOri, Protected.UserId
        FROM Report  WITH (NOLOCK)    
        INNER JOIN Form  WITH (NOLOCK)
        on Form.ReportId = Report.Id
        INNER JOIN
    ( SELECT UserId, AgencyOri
      from [dbo].UserPermissions WITH (NOLOCK)
      where GETDATE() BETWEEN StartDate AND COALESCE( ExpirationDate, GETDATE() )
      --AND IsInvalid != TRUE
          AND ( Claim IN ('SEARCHPROTECTEDINCIDENT', 'SEARCHPROTECTEDINCIDENTLIMITEDINFO', 'SEARCHPROTECTEDINCIDENTFULLINFO'))) Protected
    ON Report.AgencyOri = Protected.AgencyOri
    where SecurityLevel = 'Protected'
UNION
    SELECT DISTINCT Form.Id, foia.AgencyOri, foia.UserId
        FROM Report  WITH (NOLOCK)    
        INNER JOIN Form  WITH (NOLOCK)
        on Form.ReportId = Report.Id
        CROSS JOIN  
    ( SELECT DISTINCT UserId, AgencyOri
      from [dbo].UserPermissions WITH (NOLOCK)
      where GETDATE() BETWEEN StartDate AND COALESCE( ExpirationDate, GETDATE() )
      --AND IsInvalid != TRUE
          AND ( PersonaGroup IN ('FOIA') )) foia
      where SecurityLevel = 'Protected'
UNION
        SELECT DISTINCT Form.Id, Private.AgencyOri, Private.UserId
        FROM Report  WITH (NOLOCK)    
        INNER JOIN Form  WITH (NOLOCK)
        on Form.ReportId = Report.Id
        INNER JOIN
    ( SELECT UserId, AgencyOri
      from [dbo].UserPermissions WITH (NOLOCK)
      where GETDATE() BETWEEN StartDate AND COALESCE( ExpirationDate, GETDATE() )
          AND ( PersonaGroup IN ('STANDARD', 'SUPERVISOR'))) Private
    ON Report.AgencyOri = Private.AgencyOri
    where SecurityLevel = 'Private'
WITH CHECK OPTION
GO
  • any chance you can post the plan to pastetheplan.com – Bob Klimes Oct 22 '18 at 16:31
  • added the plan @BobKlimes – user9516827 Oct 22 '18 at 17:00
  • Can you include the view definition? It's not apparent what's happening in there from the query plan. – Erik Darling Oct 22 '18 at 17:07
  • add view definition @sp_BlitzErik – user9516827 Oct 22 '18 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.