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.

Answer 1

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.