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

Question

I believe the answer to this question is "no" but I'm interested in the community opinion. A varchar or nvarchar value should automatically 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.)

Answer 1

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

Answer 2

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'