Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012
Check the ANSI_PADDING setting:


  Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.


http://msdn.microsoft.com/en-us/library/ms187403.aspx



I just tried on both SQL Server 2005 and 2012 and I don't get those results:

Unit5 Unit6 Len_Unit6   Spanner
----- ----- ----------- --------
13          0           --||--

(1 row(s) affected)


Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) 
    Dec 10 2010 10:56:29 
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

(1 row(s) affected)


Unit5 Unit6 Len_Unit6   Spanner
----- ----- ----------- --------
13          0           --||--

(1 row(s) affected)


Microsoft SQL Server 2012 (SP1) - 11.0.3349.0 (X64) 
    Mar  8 2013 17:33:56 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.2 &lt;X64&gt; (Build 9200: ) (Hypervisor)

(1 row(s) affected)