UNION ALL implicit conversion for VARCHAR(MAX) column Is an implicit conversion in a UNION ALL when one of the data type is VARCHAR(MAX) something to worry about? 

e.g.

DECLARE @value1 VARCHAR(1)= 'a';
DECLARE @value2 VARCHAR(MAX)= 'a';

SELECT @value1
UNION ALL
SELECT @value2; 


If you look at the execution plan there will be a CONVERT_IMPLICIT warning:


  (Scalar Operator(CONVERT_IMPLICIT(varchar(max),[@value1],0))), (Scalar
  Operator([@value2]))


Is this going to behave as a normal conversion i.e. convert all the VARCHAR(1) columns to VARCHAR(MAX) and have an impact on performance? Is there a way to avoid it other than changing the table structure
Have a look at MS DOCS about Guidelines for Using Union:

Quoted from docs:


All select lists in the statements that are being combined with UNION must have the same number of expressions (column names, arithmetic expressions, aggregate functions, and so on).
Corresponding columns in the result sets that are being combined with UNION, or any subset of columns used in individual queries, must be of the same data type, have an implicit data conversion possible between the two data types, or have an explicit conversion supplied. For example, a UNION between a column of datetime data type and one of binary data type will not work unless an explicit conversion is supplied. However, a UNION will work between a column of money data type and one of int data type, because they can be implicitly converted.  




 declare @v1 varchar(1) = 'a';
 declare @v2 varchar(max) = 'a';

 set statistics profile on;
 select @v1 
 union all
 select @v2;

 select cast(@v1 as varchar(10))
 union all
 select cast(@v2 as varchar(10));
 GO




Rows | Executes | StmtText                                                                      | StmtId | NodeId | Parent | PhysicalOp    | LogicalOp     | Argument                                                  | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList  | Warnings | Type     | Parallel | EstimateExecutions
:--- | :------- | :---------------------------------------------------------------------------- | -----: | -----: | -----: | :------------ | :------------ | :-------------------------------------------------------- | :------------ | :----------- | :--------- | :---------- | ---------: | :--------------- | :---------- | :------- | :------- | :------- | :-----------------
2    | 1        | select @v1 &lt;br&gt;union all&lt;br&gt;select @v2                                        |      1 |      1 |      0 | null          | null          | null                                                      | null          | 2            | null       | null        |       null | 2.157E-06        | null        | null     | SELECT   | False    | null              
2    | 1        |   |--Constant Scan(VALUES:((CONVERT_IMPLICIT(varchar(max),[@v1],0)),([@v2]))) |      1 |      2 |      1 | Constant Scan | Constant Scan | VALUES:((CONVERT_IMPLICIT(varchar(max),[@v1],0)),([@v2])) | null          | 2            | 0          | 2.157E-06   |       4035 | 2.157E-06        | [Union1001] | null     | PLAN_ROW | False    | 1                 


Rows | Executes | StmtText                                                                                   | StmtId | NodeId | Parent | PhysicalOp    | LogicalOp     | Argument                                                               | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList  | Warnings | Type     | Parallel | EstimateExecutions
:--- | :------- | :----------------------------------------------------------------------------------------- | -----: | -----: | -----: | :------------ | :------------ | :--------------------------------------------------------------------- | :------------ | :----------- | :--------- | :---------- | ---------: | :--------------- | :---------- | :------- | :------- | :------- | :-----------------
2    | 1        | select cast(@v1 as varchar(10))&lt;br&gt;union all&lt;br&gt;select cast(@v2 as varchar(10))            |      2 |      1 |      0 | null          | null          | null                                                                   | null          | 2            | null       | null        |       null | 2.157E-06        | null        | null     | SELECT   | False    | null              
2    | 1        |   |--Constant Scan(VALUES:((CONVERT(varchar(10),[@v1],0)),(CONVERT(varchar(10),[@v2],0)))) |      2 |      2 |      1 | Constant Scan | Constant Scan | VALUES:((CONVERT(varchar(10),[@v1],0)),(CONVERT(varchar(10),[@v2],0))) | null          | 2            | 0          | 2.157E-06   |         16 | 2.157E-06        | [Union1002] | null     | PLAN_ROW | False    | 1                 



dbfiddle here