Почему оператор SqlServer select выбирает строки, которые совпадают, и строки, которые совпадают и имеют завершающие пробелы

Question

У меня есть таблица, созданная с:

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

Я вставил данные с этим:

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

Когда я выбираю из таблицы с этим:

select * from TestFeature1 where id='1';

это возвращает 2 строки

1   100
1   1000

с чего бы это? почему он возвращает версию с пробелом в конце, даже если в запросе указано, что значение равно только 1, без пробела?

Answer 1

Интересно, работает, если вы используете как:

select * from TestFeature1 where id LIKE '1'

Изменить: после небольшого исследования я обнаружил, что у других был тот же разговор, что и у нас. Смотрите здесь. Этот конкретный комментарий находится на полпути к обсуждению. Но результат был, как мы обнаружили, либо используйте LIKE, как показано выше, либо добавьте 2-е условие, чтобы проверить, что DATALENGTH столбца и предоставленное значение совпадают. Я предпочитаю как маршрут.

Answer 2

Чтобы переделать мой ответ, LEN() небезопасно тестировать ANSI_PADDING, так как он определен так, чтобы возвращать длину, исключая конечные пробелы, и DATALENGTH() предпочтительнее, как говорит AdaTheDev.

Интересно то, что ANSI_PADDING - это установка времени вставки, и что она соблюдается для VARCHAR, но не для NVARCHAR.

Во-вторых, если вы возвращаете столбец с конечными пробелами или используете '=' для равенства, кажется, что происходит неявное усечение конечных пробелов.

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 3

Мне действительно не нравится, чтобы идентификатор был таким большим столбцом переменной длины, и я постараюсь избежать этого. Однако, если вам действительно нужно, чтобы это работало, добавьте вычисляемый столбец в вашу таблицу, где вы добавите префикс и добавьте суффикс к столбцу с такими каналами:

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

Это заставит всю строку сравнивать друг с другом, так как никогда не будет начальных или конечных символов. Вы можете индексировать это тоже.

вы бы использовали код, подобный этому:

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

Answer 4

Посмотрите эту тему. Делает для очень интересного чтения об этой теме.

Радж