Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

SQL 2005 Results

Results on SQL Server 2012

SQL 2012 Results

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 <X64> (Build 9200: )
share|improve this question

migrated from stackoverflow.com Mar 2 at 20:27

This question came from our site for professional and enthusiast programmers.

    
Try to put to spaces at that '' as Unit6. Union ill try to return a varchar(n) if ehe finds a string with n chars –  jean Feb 4 at 18:31
    
@jean that doesn't reproduce the problem. Making that change would return two spaces in the spanner without adding a UNION. –  GunnerFan420 Feb 4 at 18:44
    
Is there a collation difference between the two servers? There is likely an implicit conversion occurring which casts your first select '' to a char(2) based on the schema of the second select which references tblTest –  Nathan Skerl Feb 4 at 18:52
    
@NathanSkerl Checked it out and both servers have a Server Collation of SQL_Latin1_General_CP1_CI_AS but thanks for the thought. –  GunnerFan420 Feb 4 at 19:11
    
It could also be a collation difference at the column level, but in any case I would look for the CONVERT_IMPLICIT operation in the execution plan on the offending server to rule it out –  Nathan Skerl Feb 4 at 19:20

1 Answer 1

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 <X64> (Build 9200: ) (Hypervisor)

(1 row(s) affected)
share|improve this answer
    
I'm using two exact copies of the database so the ANSI Padding Status for all columns in all tables are identical. –  GunnerFan420 Feb 4 at 18:41
    
Actually it doesn't matter when the column is NOT NULL anyway, it should always be padded to the length of the column. –  BlackICE Feb 4 at 18:47
    
I'm not sure how should always be but I'd expect it would be consistent between server versions. No idea why you didn't see the same results. –  GunnerFan420 Feb 4 at 19:00
    
idk either, I haven't found a setting yet that gives me the results you're seeing. –  BlackICE Feb 4 at 19:01
    
what's really weird is that even your query results say len(unit6) is 0, but something is still inside the spanner –  BlackICE Feb 4 at 19:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.