Should I ever need to call RTRIM() on a varchar or nvarchar value?

I believe the answer to this question is "no" but I'm interested in the community opinion. A varchar or nvarchar value should automa开发者_如何转开发tically trim trailing whitespace, so I don't believe I should ever have to call RTRIM() on such a value. Does any expert have a reason that I would need to?

(In case the tags do not make it clear, I'm referring specifically to Microsoft SQL Server.)

If ANSI_PADDING is ON then trailing spaces will be stored even with varchar/nvarchar data types, so yes.

You may not need to rtrim to get the values out in a simple select, but if you want to concatentate the values (such as combining first and last names to show the full name) you may need to.

Run this test to see what I mean:

create table #temp (test varchar (10))

insert #temp
values ('test   ')
insert #temp
values ('test2 ')
insert #temp
values ('test    ')
insert #temp
values ('test')

select test + '1' from #temp
select rtrim(test) +'1' from #temp
select * from #temp where test = 'test'

In theory, yes, because of SET ANSI_PADDING which is ON by default and will ON always in future.

To be honest, I tend to RTRIM on write because of this to avoid having on read which happens far more often. It only has to happen once to spoil your day...

SQL Server (and most other SQL DBMSs) really suck when it comes to stuff like this:

insert into Blah values ('careful ');
insert into Blah values ('careful');

Presume there's an id column or something

The values will compare to be the same, will reportedly have the same length, but will not actually have the same data. A concatenation

select Bar + 'something' from Blah

and one will have a space, the other will not.

The (n)varchar only utilizes the amount of space used, so it should not include white space. It is typically used when removing the extra space from a char field that is over allocated, i.e. a char(30) with only 10 characters.

There is a strange case with the LIKE operator. For example:

select 1 where convert(nvarchar(10), 'a') like convert(nvarchar(10), '%a ')

won't return a result.

It depends, for example the Delphi Client dataset and midas.dll used with it (at least version 7 and prior (don't know now) used to have bug that if a the length of the data in a Nvarchar field was less than the one specified, they used to get padded.

Wasn't so much a problem in the database side, but in clients it caused us no little amount of trouble.