sql - 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