Trailing spaces are ignored in SQL Server 2008

zvin

5.00/5 (2 votes)

23 Jun 2011CPOL

17K

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

Create a table:

SQL

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

Insert some values for the test:

SQL

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:

SQL

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)