2

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

  • 1
    This question is a bit too vague - what do you mean something to worry about? How many rows will this be performed on? What's the structure of the query using this etc? – George.Palacios Apr 13 '18 at 9:56
3

Yes, this can impact performance. Not because of the conversion itself, but because of memory requirements. Let's fire up a quick example:

CREATE TABLE dbo.t1 (i int IDENTITY(1,1) PRIMARY KEY, c1 varchar(10));
CREATE TABLE dbo.t2 (i int IDENTITY(1,1) PRIMARY KEY, c1 varchar(10));
CREATE TABLE dbo.t3 (i int IDENTITY(1,1) PRIMARY KEY, c1 varchar(max));
GO

SET NOCOUNT ON;
GO

INSERT dbo.t1(c1) SELECT TOP (1000) LEFT(name,10) FROM sys.all_columns ORDER BY name;
INSERT dbo.t2(c1) SELECT TOP (1000) LEFT(name,10) FROM sys.all_columns ORDER BY name;
INSERT dbo.t3(c1) SELECT TOP (1000) LEFT(name,10) FROM sys.all_columns ORDER BY name;
GO 10

Now, let's run a simple UNION ALL query, that does something that requires memory (like a sort), between these tables and compare 1 UNION 2 vs. 1 UNION 3 vs. 3 UNION 1 (the last one to show that order doesn't matter):

SELECT           i, c1 FROM dbo.t1
UNION ALL SELECT i, c1 FROM dbo.t2
ORDER BY c1;

SELECT           i, c1 FROM dbo.t1
UNION ALL SELECT i, c1 FROM dbo.t3
ORDER BY c1;

SELECT           i, c1 FROM dbo.t3
UNION ALL SELECT i, c1 FROM dbo.t1
ORDER BY c1;

Now if we look at the plans, they look similar enough, and in an isolated test on an otherwise idle machine, they even perform quite similarly:

enter image description here

enter image description here

enter image description here

enter image description here

And you will see similar things in Management Studio - it will tell you that all three plans have an equal share of the cost of the batch (33.3%) because they're doing the same amount of work and returning the exact same data. Which is all true.

However, there's a problem here that is not immediately obvious. First, you'll find a warning on the root node operator (SELECT). It tells you about the implicit conversion that you've already noted, but it also mentions an excessive memory grant.

Next, if you change "Line Widths By" from "Rows" to "Data Size (MB)" (something you can only do in Plan Explorer), you will see quite different diagrams. The first one doesn't change in any real discerning way, but the second one shows some alarmingly large data flows.

enter image description here

enter image description here

enter image description here

Where does all this extra data come from? Estimated row size. SQL Server assumes that the amount of data that's in any variable-width column is going to be populated, on the average row, 50%. The cap for varchar/nvarchar is 8K, even for max, so on the plans where all of the rows from both tables have to be upsized to varchar(max), SQL Server assumes that the string column will have 4K of data on every row, which you can see if you hover over the large connector lines:

enter image description here

And the tooltip for the SELECT operator shows the grant details, and the ginormous delta between the amount of memory SQL Server thought it would need and the amount it actually needed:

enter image description here

You'll see a similar symptom even if you only pull a single row from the table with the max column:

SELECT                   i, c1 FROM dbo.t1
UNION ALL SELECT TOP (1) i, c1 FROM dbo.t3
ORDER BY c1;

So, yes, you'll potentially see performance problems that have nothing to do with the conversion itself. In this specific example the end result isn't affected, and the memory hogging has no ill effect, but imagine if you had 20 or 100 or 5,000 concurrent users running this query.

3

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 <br>union all<br>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))<br>union all<br>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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.