Question about the UNION operator and its implicit data type conversions Is there a limit to the number of UNION operators to combine query results?

I have an odd situation where I have a table called TransmittalSheet TS that have a double (1 to Many) relationships with each of the 2 tables: (DocumentInfo and EquipmentJobPlan tables), they both have almost the same exact column names and data types. 
Each primary key (TransmittalID)  that’s coming from the parent table TS above, have two corresponding foreign keys in each of the children tables above. FK pair columns are called: (Docs_Implementing, Docs_Obsolescing) and (JobPlan_TS_Implementing and JobPlan_TS_Obsolescing).

I need to query if each Transmittal have any documents or equipmentJobPlans are associated with it (either Imp or Obs for each table association), so I believe I need to have 3 UNION operators like this:

Select A, B, C , ‘Imp Doc’ as DocType
From DocumentInfo As Di INNER JOIN TransmittalSheet TS 
ON Di.DocumentTransmittal_IMP = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Obs Doc’ as DocType
From DocumentInfo As Di INNER JOIN TransmittalSheet TS
ON Di.DocumentTransmittal_OBS = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Imp JP’ as DocType
From EquipmentJobPlan As EQ  INNER JOIN TransmittalSheet TS
ON EQ.JobPlanTransmittal_IMP = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’
Union
Select A, B, C , ‘Obs JP’ as DocType
From EquipmentJobPlan As EQ  INNER JOIN TransmittalSheet TS
ON EQ.JobPlanTransmittal_OBS = TS.TransmittalID
Where TransmittalNumber = ‘TS- 0001’


Is there a better way to do this?  Using SQL Server 2008R2.

Also, it seems that Union is doing some data conversion for column C for example: both columns are capturing document versions. It has a data type of varchar(75) in the DocumentInfo table, but it has a smallDateTime in the JobPlan table, , should I have an explicit conversion in place or the implicit Coversion that the Union is doing will be OK? Thanks
For one, you can certainly simplify your query, for example (I've added schema prefixes, which you should always use):

Select A, B, C
  From dbo.DocumentInfo As Di INNER JOIN dbo.TransmittalSheet TS 
  ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
  Where TransmittalNumber = 'TS- 0001'
UNION
Select A, B, C
  From dbo.EquipmentJobPlan As EQ dbo.INNER JOIN TransmittalSheet TS
  ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
  Where TransmittalNumber = 'TS- 0001';


Or perhaps even:

SELECT A, B, C
  FROM dbo.TransmittalSheet TS
  LEFT OUTER JOIN dbo.DocumentInfo As Di 
  ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
  LEFT OUTER JOIN dbo.EquipmentJobPlan As EQ
  ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
WHERE TS.TransmittalNumber = 'TS- 0001' 
AND COALESCE(Di.KeyColumn, EQ.KeyColumn) IS NOT NULL
GROUP BY A, B, C;


(I don't know if the GROUP BY is necessary, because I don't know if you are using UNION on purpose for the intention of removing duplicates. Though I do recommend putting table aliases on all column references, so readers have a clue which columns come from which tables. You may need to use COALESCE here and choose which table takes precedence for any of the output columns if A, B or C don't come from TransmittalSheet.)

Now, you say that when you use a UNION you get a conversion error, because in one of the tables column C is a varchar and in another it is a smalldatetime. Your options are:


To fix that. If you are considering those columns to be the same and compatible, they should be of the same data type and contain like data. Which means changing the data type after you remove any data that can't be directly converted to smalldatetime.
If the varchar column needs to have different data that is not compatible with smalldatetime, then perhaps it is best to present that as a different output column in your UNION query.
After that, you can apply an explicit cast on the smalldatetime type so that it can be converted to varchar, instead of the way SQL Server will do it (which is to try to convert the string to a date). If you want to only present valid smalldatetime data, then you can CAST the other way, but use a CASE expression so that the conversion is only attempted on valid data. The implicit conversion can follow the rules of data type precedence, but if the varchar column contains any value that can't be converted to smalldatetime, it fails with Msg 295, as you've discovered. This can happen even if those rows don't appear in the output - the conversion can occur before the rows are filtered out (I've written about this here, here, here and here and it was reported as a bug on Connect (RIP). And because you're using UNION instead of UNION ALL, you may be more likely to hit invalid values because the additional DISTINCT SORT operations this (usually) adds, which can change the way SQL Server will optimize the overall plan.


So, if you can't benefit from the re-writes above (or if they produce different results for some reason), and you can't do 1. or 2., then I would re-write the UNION version either like this:

-- against the table where C is smalldatetime:
SELECT A, B, CONVERT(CHAR(10), C, 120)
FROM ...

-- or against the table where C is varchar:
SELECT A, B, CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM ...


A more localized example you can play with:

CREATE TABLE dbo.foo(A INT, B INT, C VARCHAR(75));
CREATE TABLE dbo.bar(A INT, B INT, C SMALLDATETIME);    
INSERT dbo.foo(A,B,C) VALUES(1,2,'three'),(2,4,'20150101');
INSERT dbo.bar(A,B,C) VALUES(1,2,'20050101'),(2,4,'20050101');
GO

-- these both work of course:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1;
SELECT A,B,CONVERT(SMALLDATETIME,C) FROM dbo.foo WHERE ISDATE(C) = 1;
GO

-- fails:
SELECT A,B,C FROM dbo.foo
UNION
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds, but may not always:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1
UNION 
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds:
SELECT A, B, C = CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM dbo.foo
UNION 
SELECT A, B, C FROM dbo.bar;

-- succeeds:
SELECT A, B, C FROM dbo.foo
UNION 
SELECT A, B, C = CONVERT(VARCHAR(10), C, 120) FROM dbo.bar;
GO

DROP TABLE dbo.foo, dbo.bar;


Finally, please don't CAST( AS Char) with no length, or any other variable type.