Why are zero length spaces being returned from a Union All against an empty table in SQL Server 2005 but not SQL Server 2012 Supporting an existing VB6 app with a SQL Server 2005 back end and we're migrating our database server to SQL Server 2012. Created a backup of the production database and restored it into a dev database on both servers. Other than the server version the databases are identical and the Compatibility Level is still set to 90 (SQL Server 2005).

When I run a query on SQL Server 2005, similar to the example below, a string is returned with extra spaces for any blank fields. If I run the same query on SQL Server 2012 there are no extra spaces being returned. As far as I can tell it seems to be an issue with doing a UNION ALL against a table with no records and a char data type but I wanted to ask anyway.

For the below example you'd need to create a table with one NOT NULL, Char(2) column named Unit6. Leave the table empty with no records once created.

CREATE TABLE [dbo].[tblTest](
    [Unit6] [char](2) NOT NULL
) ON [PRIMARY]


When I run the following query I receive two different results depending on the server version:

SELECT 
   Unit5, Unit6,
   LEN(Unit6) as Len_Unit6, 
   '--|' + Unit6 + '|--' as Spanner
FROM
   (SELECT      
       '13' as Unit5,
       '' as Unit6
    UNION ALL
    SELECT      
        '',
        Unit6 
    FROM tblTest) as MyData


Results on SQL Server 2005



Results on SQL Server 2012



Hopefully you can see the Spanner column shows two spaces when run on 2005 vs zero spaces when run on 2012. 

This is a major issue as the app runs queries similar to the above example and then later uses string functions to cut up a single string for database inserts and updates. Without the spaces which are being included on SQL Server 2005 the string functions are carving up the wrong pieces which causes any inserts or updates to fail.

Any information would be most helpful. Thank you!

Server Versions

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
Nov 24 2008 13:01:59 
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) 
Oct 25 2013 19:04:40 
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 &lt;X64&gt; (Build 9200: )
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)