Why does using Format vs Right to apply padding cause estimated number of rows to dramatically change? I was working on a query at work, that had a left join like



cast(cola as varchar) + '-' + right('000' + cast(colb as varchar), 3) = x


The actual execution plan for this query was fairly close, est 269 vs actually 475.  

Changing the right +padding to use format(colb, '000') results in a a huge mis-estimate of the number of rows, off by at least 4 million, which causes the query to take 10-15 times as long.

I understand why a mis-estimate would cause a problem, but I don't understand why using Format would cause a less accurate estimate.
FORMAT returns nvarchar which has a higher data type precedence than the compared varchar column. In addition the imprecise row count estimate, the implicit conversion of the compared varchar column to nvarchar will prevent indexes on that column from be used efficiently

Try casting the FORMAT result to varchar.