collation ansi padding and trailing blanks

Hi,

This might sound obvious, or a newbie question, but how are trailing blanks treated by SQL2005 on varchar columns?

I have a column where two rows only differ by a trailing blank. If write a select and a where clause on the column, anly trailing blanks seem to be trimmed. I tried the ansi padding setting but it doesn't change anything. Is it a question of collation? I have default collation on the server set to SQL_Latin1_General_CP1_CI_AS...

The problem also seems to arise when I try to create a unique index on the column, where both values are considered equivalent...

I give here a sample based on the BOL for set ansi_padding. I was expecting each of the select statements below to retrun only one row...

Cany somebody please explain why they all return two rows?

PRINT 'Testing with ANSI_PADDING ON'

SET ANSI_PADDING ON;

GO

CREATE TABLE t1 (

charcol CHAR(16) NULL,

varcharcol VARCHAR(16) NULL,

varbinarycol VARBINARY(8)

);

GO

INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);

INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t1

where varcharcol='Trailing blank';

GO

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t1

where varcharcol='Trailing blank ';

GO

PRINT 'Testing with ANSI_PADDING OFF';

SET ANSI_PADDING OFF;

GO

CREATE TABLE t2 (

charcol CHAR(16) NULL,

varcharcol VARCHAR(16) NULL,

varbinarycol VARBINARY(8)

);

GO

INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);

INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);

INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t2

where varcharcol='Trailing blank';

GO

SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',

varbinarycol

FROM t2

where varcharcol='Trailing blank ';

GO

DROP TABLE t1

DROP TABLE t2

ANSI padding setting only affects the storage and how the trimming of blanks is performed for non-unicode data. It doesn't change the search semantics. SQL Server will always ignore trailing blanks / spaces for equality searches. If you perform the same using LIKE then trailing blanks will be considered. If you do the query below after inserting the data, you will see how the storage differs when ANSI_PADDING is ON and OFF.

select datalength(charcol), datalength(varcharcol)

from t1

select datalength(charcol), datalength(varcharcol)

from t2