CODEWITHSUNDEEP
sql-servercastingimplicit-conversion
Chris Wood
Chris Wood

Reputation: 575

removing implicit conversion in SQL Server

I am attempting to eliminate an implicit conversion from the database.

I added the CAST statement below on the UpdatedByID field as this was a Char(10), it is joining to an INT in the FROM statement. But i'm still getting the Implicit conversions. I must be missing something obvious ?? 

        SELECT DISTINCT ba.BookingID INTO #temp1
        FROM BookedItemsAudit_tbl bia  
        INNER JOIN BookingAudit_tbl ba ON bia.BookingAuditID = ba.BookingAuditID    
        INNER JOIN User_tbl u ON  u.UserId  = CAST(ba.UpdatedById AS INT)
        WHERE bia.BookedItemTypeId IN (1, 58, 60)   
        AND u.UserId = 5

here is the cut down schema

BOOKINGAUDIT_TBL
Column_name     Type        Length
BookingAuditID  int         4
BookingID       int         4
UpdatedByID     CHAR        10
UpdatedBy       varchar     50
UpdatedDate     datetime    8
TotalMargin     money       8
BookingStatusId int         4

 USER_TBL
 Column_name    Type        Length
 UserId         int         4
 FullName       varchar     100
 UserName       varchar     20

full query and schema in this link

SQL fiddle

attached is the error message when the hint appears in the execution plan

Implicit conversion after casting to INT

Upvotes: 4

Views: 2320

Answers (1)

Martin Smith
Martin Smith

Reputation: 453909

The warning is about your explicit conversion not an implicit conversion. The tooltip you show doesn't mention CONVERT_IMPLICIT

CAST(ba.UpdatedById AS INT) shows up in the plan as CONVERT(int,ba.UpdatedById ,0) and it is warning you about that (it prevents an index seek on ba.UpdatedById).

To stop seeing this warning you would need to fix your schema so you are joining on columns of the same datatype.

Upvotes: 6

Related Questions