Double space replaced with single space by dbms ?!

This is driving me bananas. Can't find any info on this anywhere...
SQL 2000 seems to replace double space with a single space when I set
a varchar field to " " (2spaces), it only stores " " (1space). Why
on earth would microsoft do this? If I save 2 spaces - I WANT TO SEE
2 SPACES!!!!

Can anyone help? Is this a database setting? Is this due to using
varchar?

Any help appreciated.

Colin Hale[posted and mailed, please reply in news]

Colin Hale (colinhale@.radiusplc.co.uk) writes:
> This is driving me bananas. Can't find any info on this anywhere...
> SQL 2000 seems to replace double space with a single space when I set
> a varchar field to " " (2spaces), it only stores " " (1space). Why
> on earth would microsoft do this? If I save 2 spaces - I WANT TO SEE
> 2 SPACES!!!!
> Can anyone help? Is this a database setting? Is this due to using
> varchar?

Could you please post a repro that demonstrates the problem? That is
a script with a CREATE TABLE statement, INSERT statements of the data.
Or if you add the data through some client code, please include
that client code.

I get double spaces from this script:

CREATE TABLE a(a varchar(10))
go
INSERT a VALUES ('a a')
go
select * from a

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It is trailing spaces that are the problem.
However I have found the answer Microsoft Knowledge Base Article -
296559. Even though ansi padding set to on for the database, once an
ALTER COLUMN command has been issued against the column, ansi padding
is then switched off !!! Seems the only way around this is to
recreate the table and transfer the data, unless anyone else has any
ideas?