0

When we have the following table:

CREATE TABLE Test1 (
    Name NVARCHAR(50) NOT NULL
    , Code SMALLINT NOT NULL
)

With only 1 row for demo purpose:

INSERT INTO Test1(Name, Code) 
VALUES ( 'ABC', 1)

And the following queries:

SELECT CONCAT(Name, ' - ', Code)
FROM Test1

SELECT CONCAT(Name, ' - ', CONVERT(NVARCHAR(2), Code))
FROM Test1


SELECT CONCAT(Name, ' - ', CAST(Code AS NVARCHAR(2)))
FROM Test1


SELECT CONCAT(Name, ' - ', FORMAT(Code, '0'))
FROM Test1

The first 3 queries have a warning:

Type conversion in expression (...) may affect "CardinaltiyEstimate" in query plan choice.

But why is FORMAT behaving differently ? What happens behind the scenes to eliminate the implicit conversion?

Is there any other way then format so you are not impacted by the implicit conversion?

Queryplan

Preben Huybrechts
  • 344
  • 1
  • 10
  • Using format to eliminate a conversion warning is not a great trade-off. At scale, you will notice you are no better off, and most likely worse off. Please read this post in full and also consider whether the conversion warning is actually a problem you need to solve (if you really want to avoid it, you can always have the presentation layer handle any presentation layer-ish things like concatenation). – Aaron Bertrand Jul 16 '20 at 13:26
  • The warning is not talking about an implicit conversion. It just warn you that the "type conversion in your expression" may affect the cardinality estimator. As Aaron mentionned, this warning may not be something you need to be worried about. – Dominique Boucher Jul 16 '20 at 13:44

0 Answers0