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?