I've been using Brent Ozar's tools for a while, especially the sp_Blitz procedure.
However, there is one recommendation about implicit conversion that I don't get, no matter what I try.
Please help me to understand how to eliminate the implicit conversion in the execution plan.
This is the query in question:
(@dealStatus tinyint,@dealType tinyint,@takeCity nvarchar(20),@firstRow int,@lastRow int)
SELECT id,title,details,userId,condition,regDate,updateDate,image,imageVer,city,recordCount FROM ( SELECT deals.id,deals.title,details,userId,condition,regDate,updateDate,image,imageVer,city, COUNT(0) OVER () AS recordCount,CAST(ROW_NUMBER() OVER (ORDER BY sortOrder DESC,updateDate DESC) AS int) AS rowNum FROM deals (NOLOCK) INNER JOIN subcategories (NOLOCK) ON subcategory=subcategories.id INNER JOIN cities (NOLOCK) ON city=cities.name WHERE dealStatus=@dealStatus AND dealType=@dealType AND userId>0 AND livingArea=@takeCity AND (middleCategory NOT IN (801,805) OR subcategory=80012) ) a WHERE rowNum BETWEEN @firstRow AND @lastRow
I put the execution plan XML in a reply to this post. Sorry I had to trim most of it because I couldn't find a way to attach a file.