Why would SqlServer select statement select rows which match and rows which match and have trailing spaces

Question

I have a table created with:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestFeature1](
[Id] [nvarchar](50) NOT NULL,
[Leng] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

I inserted data with this:

insert into TestFeature1 (id,leng) values ('1',100);
insert into TestFeature1 (id,leng) values ('1 ',1000);

When I select from the table with this:

select * from TestFeature1 where id='1';

this returns 2 rows

1   100
1   1000

why would this be? why is it returning the version with the space on the end even when the query specifies that the value is only a 1 on its own, with no space?

Answer 1

To rework my answer, LEN() is unsafe to test ANSI_PADDING as it is defined to return the length excluding trailing spaces, and DATALENGTH() is preferable as AdaTheDev says.

What is interesting is that ANSI_PADDING is an insertion-time setting, and that it is honoured for VARCHAR but not for NVARCHAR.

Secondly, if returning a column with trailing spaces, or using the '=' for equality, there seems to be an implicit truncation of trailing space that occurs.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[TestFeature1](
[Id] [varchar](50) NOT NULL,
[Leng] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

GO

insert into TestFeature1 (id,leng) values ('1',100); insert into TestFeature1 (id,leng) values ('1 ',1000);

-- verify no spaces inserted at end
select '['+id+']', * from TestFeature1
select datalength(id), * from TestFeature1
go

DROP TABLE [dbo].[TestFeature1]
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[TestFeature1](
[Id] [nvarchar](50) NOT NULL,
[Leng] [decimal](18, 0) NOT NULL
) ON [PRIMARY]

GO

insert into TestFeature1 (id,leng) values ('1',100); insert into TestFeature1 (id,leng) values ('1 ',1000);

-- verify spaces inserted at end, and ANSI_PADDING OFF was not honoured by NVARCHAR
select '['+id+']', * from TestFeature1
select datalength(id), * from TestFeature1
go

Answer 2

Interestingly, works if you use LIKE:

select * from TestFeature1 where id LIKE '1'

Edit: after a bit more research I found others have had the same conversation as us. See here. That particular comment is half way through the discussion. But the outcome was as we have found, either use LIKE as demonstrated above, or add a 2nd condition to check the DATALENGTH of the column and supplied value are the same. I prefer the LIKE route.

Answer 3

Look up this thread. Makes for a very interesting read about this topic.

Raj

Answer 4

I really don't like a ID to be such a large variable length column, and I'd try to avoid that. However, if you really need this to work, add a computed column to your table where you prefix and suffix the column with pipes like:

ALTER TABLE TestFeature1 ADD
    IDx  AS '|'+ID+'|' PERSISTED

This will force the entire string to be compared to each other, since there will never be any leading or trailing characters. You can index this too.

you'd use code like this:

select * from TestFeature1 where id='|1|';
select * from TestFeature1 where id='|1 |';