Why does SQL Server apply RTRIM before Insert?

Question

I have a table with a Unique Key on columns Pfx, Bse and Sfx. While inserting data, it seems to me like SQL Server is internally applying an RTRIM and causing an issue with my Sfx column which has a space in the second row. Is it possible to prevent this RTRIM or am I missing something?

INSERT INTO Part (Seq, Pfx, Bse, Sfx, Stat, Desc, Cr_date, Cr_User)
SELECT 1 SEQ, '2R83' AS PFX, '6477' BSE, 'AA' SFX, 1 STAT, 'SPLIT MASS FLYWHEEL' DESCR, GETDATE() CR_DT, 'USERID' CR_US 
UNION ALL
SELECT 2, '2R83', '6477', 'AA ', 1, 'SPLIT MASS FLYWHEEL', GETDATE(), 'USERID';

The error message doesn't seem to be trimming the data and retains the space.

Violation of UNIQUE KEY constraint 'NNMP0672'. The duplicate key value is (2R83, 6477, AA )

Answer 1

Are the columns CHAR or VARCHAR?

This likely has to do with what the ANSI_PADDING setting was at the time that the column was created. If ANSI_PADDING is set to OFF then VARCHAR columns are automatically trimmed when inserted into the column. CHAR can be a little more tricky when it is defined to allow NULL values, but in general it always pads columns to be of the maximum length for the column. So, in short, you'll probably want VARCHAR columns with ANSI_PADDING set ON.

Keep in mind that the ANSI setting applies when the column is created, so you will have to drop and recreate the table or at least the column to accomplish this.

As others have said though, it's generally a pretty bad idea to rely on hidden or whitespace characters to differentiate between keys in a table. The fact that your import is failing here might mean something else besides the fact that there is a difference in trailing spaces - maybe this is bad data in the source system that should be corrected when you import it so that you never have the issue in the first place. Treat the problem, not the symptom ;)

Also, this may sound like a personal preference, but since we're no longer in the days when column names were limited to 8 characters, you might want to be a bit more descriptive with your column names rather than Pfx, Bse, etc. Spell words out and be descriptive. I've found that this makes development and debugging much easier. I realize that you're converting a legacy system, so maybe it's difficult (or not possible at this time) to do that, but if you can then I would highly suggest it.

Here's a link to the documentation on ANSI_PADDING if you want more information: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql

Answer 2

In this link:

https://support.microsoft.com/en-gb/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces

It says that in order to compare two strings of different lengths, the shorter string is padded with spaces, therefore your 'AA' in the first row becomes 'AA ' for comparison.

Example:

create table dbo.Strings (
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    S_VC VARCHAR(100) NULL
)

insert strings (S_VC)
values  ('Robert '),
        ('Robert')

select  ID, S_VC, datalength(S_VC) Data_Len, len(S_VC) [Len]
from    strings

select  *
from    strings s1 inner join strings s2
        on s1.S_VC = s2.S_VC

Answer 3

I really do not recommend what I am about to propose. But, you can accomplish what you want by using an explicit unique index and a computed column.

Note that spaces at the end of string are generally ignored. This is considered a good thing because we don't see them. WYSIWYG (what-you-see-is-what-you-get) is often a reasonable approach. Spaces at the end of strings are ignored for LEN() as well as for comparisons, for instance.

But, you can still calculate the length by appending a character and subtracting. So the following will allow you to have spaces at the end count as separate distinct values:

alter table t add s_len as (len(s + 'x') - 1);

create unique index t_s_slen on t(s, s_len);

Here is a SQL Fiddle that illustrates this in action. Of course, you need to remove the unique constraint on the column alone.

Answer 4

What is your table definition? (ie what data types)

It might suit you better to use NVARCHAR data types

See here, as it explains why VARCHAR types use the ANSI standard and ignore whitespace on the end of these data types

Answer 5

Comparison is based on rtrim but they are different

declare @tV table (name varchar(10) primary key);
insert into @tV values ('bob'), ('alice'), ('ted'), ('al '), (' al');
select *, len(name) as ln, DATALENGTH(name) as dl
from @tV;

    name       ln          dl
---------- ----------- -----------
 al        3           3
al         2           3
alice      5           5
bob        3           3
ted        3           3

You could use this to pad the space with _

set nocount on;
declare @al1 varchar(10) = 'al';
declare @al2 varchar(10) = 'al ';
select @al1, len(@al1), DATALENGTH(@al1), left((rtrim(@al1) + '____'), DATALENGTH(@al1))
     , @al2, len(@al2), DATALENGTH(@al2), left((rtrim(@al2) + '____'), DATALENGTH(@al2));
select 'equal' where  @al1 = @al2;
select 'not equal' where  @al1 <> @al2;
select 'equal' where  @al1 = @al2;
select 'equal' where  left((rtrim(@al1) + '____'), DATALENGTH(@al1)) = left((rtrim(@al2) + '____'), DATALENGTH(@al2));

---------- ----------- ----------- -------------- ---------- ----------- ----------- --------------
al         2           2           al             al         2           3           al_


-----
equal


---------


-----
equal


-----