Bypass SQL COLLATE

Question

Is there a way to "bypass" the SQL collate logic? I want two different bytearrays to give me two different keys no matter what and without SQL trying to collate them.

I prefer to use the nvarchar data type if possible.

The most generic COLLATE setting I have found is 'Latin1_General_100_BIN2' but even that gives me a conflict in this example:

CREATE TABLE [dbo].[test] (
    [Feature] [nvarchar](450) COLLATE Latin1_General_100_BIN2 NOT NULL,
    CONSTRAINT [PK_dbo.test] PRIMARY KEY CLUSTERED ([Feature] ASC)
)

insert into test values ('a')
insert into test values ('a ')

I get the error

Violation of PRIMARY KEY constraint 'PK_dbo.test'. Cannot insert duplicate key in object 'dbo.test'. The duplicate key value is (a ).

I am using the SQL server in MS Azure.

That's standard SQL rules - before two strings are compared for equality, the shorter string is padded with blanks. As jarlh says, nothing to do with collations. — Damien_The_Unbeliever, Oct 02 '20 at 07:00
@Damien_The_Unbeliever Is there any way to bypass this (for nvarchar)? — user628904, Oct 02 '20 at 07:02
You can have a computed column for the trimmed length, and include that column in the pk. Still a but unclear why you are doing this. — jarlh, Oct 02 '20 at 07:22

Jason Pan · Answer 1 · 2020-10-08T01:07:27.850

I do not recommend that you use the Feature field as the primary key.

If possible, you can try to use below script.

declare @tb table(id int identity,name varchar(20))
insert into @tb(name) values('a'),(' a'),('a ')
select * from @tb --where name=' a' and datalength(name)=datalength(' a')

Running result:

In the database, for spaces, currently only len and datalength can be used to filter data. When inserting data in the primary key, space characters should be filtered out. So it should not be supported.

For more details or questions, you can raise a support on the portal, and the official will give you documentation or a satisfactory answer.

Bypass SQL COLLATE

1 Answers1