Trailing spaces are ignored in SQL Server 2008

zvin

5.00/5 (2 votes)

Jun 23, 2011

CPOL

16960

When comparing data stored in databases, you don't need to use RTRIM()

Create a table:

CREATE TABLE dbo.TestTrailingSpaces
(
   id		int identity(1,1) primary key,
   SomeName	varchar(20)
)

Insert some values for the test:

insert into dbo.TestTrailingSpaces (SomeName)
values('aaa ')
insert into dbo.TestTrailingSpaces (SomeName)
values('bbb     ')

Now run these Select statements and all of them will return you values:

select * from dbo.TestTrailingSpaces where SomeName = 'aaa'
select * from dbo.TestTrailingSpaces where SomeName = 'aaa     '
select * from dbo.TestTrailingSpaces where SomeName = 'bbb'

Summary: Trimming on both sides is redundant, because trailing blanks are ignored by "=". Consider this, trimming the column prevents an index seek, this could be vital for performance.