Problematic slashes and questions marks in unique index values

Question

What is it about the Phone values inserted below that SQL Server is treating them as identical in a unique index?

CREATE TABLE Phone
(
 Id int identity(1, 1) primary key,
 Phone nvarchar(448) not null
)
go
create unique index IX_Phone on Phone(Phone)
with (data_compression = page);
go
insert into Phone Values ('?281/?263-?8400');
insert into Phone Values ('‎281/‎263-‎8400');
select * from Phone;
drop table Phone;

I receive an error message:

Msg 2601, Level 14, State 1, Line 13 Cannot insert duplicate key row in object 'dbo.Phone' with unique index 'IX_Phone'. The duplicate key value is (?281/?263-?8400).

Answer 1

Your problem is that you passes unicode strings as non-unicode.

Your '‎281/‎263-‎8400' is a string of 15 characters, not 12, there are 3 non printable 8206 symbols, Left-to-right mark

select len('‎281/‎263-‎8400'); -- 15 !!!

Try this code where I pass the unicode values as unicode and see that there is no problem at all:

CREATE TABLE dbo.Phone
(
 Id int identity(1, 1) primary key,
 Phone nvarchar(448) not null
)
go
create unique index IX_Phone on Phone(Phone)
with (data_compression = page);
go
insert into Phone(phone) Values (N'?281/?263-?8400');
insert into Phone(phone) Values (N'‎281/‎263-‎8400');
select * from Phone;

And this is what your string '‎281/‎263-‎8400' really contains (dbo.nums is my table that contains natural numbers):

declare @t table(col1 nvarchar(100), col2 nvarchar(100));
insert into @t values (N'?281/?263-?8400', N'‎281/‎263-‎8400'); 
select n, unicode(substring(col1, n, 1)), unicode(substring(col2, n, 1))
from @t cross join dbo.nums
where n <= 15;

enter image description here

Now what happens when you pass your unicode string as non unicode.

Your non-printable symbol 8206 is transformed into ?, that is how non-unicode strings works: every character that cannot be find in the corresponding codepage and represented as ascii code is substituted with question mark.

So for example if you use Latin Collation and try to compare Hebrew and Cyrillic characters (the same number of characters) as varchar they always be equal just because they are transformed to question marks while comparing as nvarchar they are different:

enter image description here

So if now you'll try to insert these 2 values (Hebrew + Cyrillic) into your Phone table passing them as non-unicode (without using N), only one of them will be inserted, and the other will be rejected by unique constraint. And if you'll try to select from Phone, '?????' will be returned

• sepupic: @SqlWorldWide what does not work, sorry?
• sepupic: @SqlWorldWide, one more time: please, do not retype the original string, it contains 15 characters, not 12 as your "retyped" string
• sepupic: @SqlWorldWide I still don't understand your question. If you change the column type to varchar you gain absolutely nothing because the 8206 character still will be transformed into "?". The problem is not in destination table column type, the problem is that someone passes UNICODE as not unicode and some characters are turned to "?". Exactly those characters that cannot be represented using ascii code
• cocogorilla: thank you so much, also unicode(substring(col1, n, 1)) was key for my real world use case in which my stored procedure was using a substring and causing problems

Answer 2

I am not sure why '‎281/‎263-‎8400' is returning the same value as '?281/?263-?8400'. To fix it though, just change your INSERT commands so that they are using unicode values:

insert into Phone Values (N'?281/?263-?8400');
insert into Phone Values (N'‎281/‎263-‎8400');

• cocogorilla: unfortunately, that isn't going to help me much because this is failing in context of a merge statement in a routine and these values are coming through as parameters
• Tara Kizer: @cocogorilla, then you should edit your question with that information and show what you are actually doing.
• cocogorilla: Will do, give me some time to mock it up