What is the value of 0x8FFF in nvarchar comparison?

Question

In SQL Server, values nvarcharrepresent Unicode code code strings - I understand that by default, using UTF-16, values ​​outside are 0xFFFFrepresented as pairs of surrogates.

I wanted to set a default string value for the nvarcharUDF parameter , which would contain special characters. T-SQL does not allow hex-escape sequences in string literals; you must use the CHAR()OR functions NCHAR()to specify characters by their code values, however you must use a literal for the default values ​​for the parameter: you cannot use NCHAR(). However, I remembered that SQL Server also does an implicit conversion from varbinaryto nvarchar, therefore:

CREATE FUNCTION DoSomething(
    @foo nvarchar(50) = '\x0008', -- not supported by T-SQL syntax
    @bar nvarchar(50) = NCHAR(8), -- forbidden: defaults must be a literal
    @baz nvarchar(50) = 0x008     -- success!
)

I wanted to change the parameters to represent the comparison range, and I wanted the default values ​​to represent the maximum possible range of values, allowing me to use Static SQL for the search function without the need OPTION(RECOMPILE)or now, a discredited template (@foo IS NULL OR Table.Foo = @foo).

So, I changed my function to this:

CREATE FUNCTION DoSomething(
    @fooMin nvarchar(50) = 0x0000,
    @fooMax nvarchar(50) = 0xFFFF
)
/* SELECT goes here */
WHERE
    Foo BETWEEN @fooMin AND @fooMax

I suggested that it 0xFFFFwould be high enough to accommodate any (practical) Unicode text thrown into the system I am building.

However, to my surprise, the operator BETWEENalways returned false. I was wondering if there could be anything with the operand of the upper bound, so I changed it to 0x7FFF, and it worked fine.

I tried the 0x8FFFfollowing and it worked too.

But 0x9FFF, then 0x9000failed.

, Unicode 0x8FFF - 0x9000. , 0x0000 - 0xFFFF 0x900 CJK: https://en.wikipedia.org/wiki/Plane_(Unicode)#/media/File:Roadmap_to_Unicode_BMP.svg UTF-16 0xD800 0xDC00 - 0x900.

:

SELECT N'HELLO', 0xFF, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0xFF THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x0FFF, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x0FFF THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x1000, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x1000 THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x6000, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x6000 THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x6FFF, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x6FFF THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x7000, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x7000 THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x7FFF, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x7FFF THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x8000, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x8000 THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x8FFF, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x8FFF THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x9000, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x9000 THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0x9FFF, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0x9FFF THEN 'yup' ELSE 'no' END )
UNION ALL
SELECT N'HELLO', 0xFFFF, ( CASE WHEN N'HELLO' BETWEEN 0x0000 AND 0xFFFF THEN 'yup' ELSE 'no' END )

:

HELLO   0xFF    yup
HELLO   0x0FFF  no
HELLO   0x1000  no
HELLO   0x6000  no
HELLO   0x6FFF  yup
HELLO   0x7000  yup
HELLO   0x7FFF  yup
HELLO   0x8000  no
HELLO   0x8FFF  yup
HELLO   0x9000  no
HELLO   0x9FFF  no
HELLO   0xFFFF  no

, , 0x7FFF - 0x8000, .

, , little-endian big-endian, , **FF, true, , N'H'.

Answer 1

:

select CASE WHEN convert(varbinary(82), N'HELLO') BETWEEN 0x0000 AND 0xffff THEN 'yup' ELSE 'no' END
select CASE WHEN convert(varbinary(82), N'HELLO') BETWEEN 0x4800 AND 0xffff THEN 'yup' ELSE 'no' END
select CASE WHEN convert(varbinary(82), N'HELLO') BETWEEN 0x4800 AND 0x4801 THEN 'yup' ELSE 'no' END

declare @x1 nvarchar(2) = 0x4800, @x2 nvarchar(2) = 0xFFFF;
declare @l1 nvarchar(2) = reverse(convert(varbinary(2), @x1));
declare @l2 nvarchar(2) = reverse(convert(varbinary(2), @x2));
select CASE WHEN N'HELLO' BETWEEN @l1 AND @l2 THEN 'yup' ELSE 'no' END