Geek Answers Handbook

What is the difference between sorting NVarchar, Bin, Bin2?

All 3 options are case and accent sensitive and support Unicode. According to the documentation:

  • NVarchar sorts and compares data based on "dictionaries for the corresponding language or alphabet" (?)

  • Bin sorts and compares data based on "bit patterns" (?)

  • Bin2 sorts and compares data based on "Unicode code points for Unicode data" (?)

To make complex things simple, can I say that Bean is an improvement to NVarchar, and Bin2 is an improvement to Bean; and if I'm not limited to backward compatibility, is it always recommended to use Bin2, or at least Bin, to get the best performance?

==================================================== ======================== I will try to explain my self again.

Take a look:

If Object_ID('words2','U') Is Not Null Drop Table words2;
Create  Table words2(word1 NVarchar(20),
                    word2 NVarchar(20) Collate Cyrillic_General_BIN,
                    word3 NVarchar(20) Collate Cyrillic_General_BIN2);

Insert
Into    words2
Values  (N'ھاوتایی',N'ھاوتایی',N'ھاوتایی'),
        (N'Συμμετρία',N'Συμμετρία',N'Συμμετρία'),
        (N'אבַּג',N'אבַּג',N'אבַּג'),
        (N'対称性',N'対称性',N'対称性');
Select * From words2;

All 3 options support all kinds of alphabet, regardless of what sorting is.

The question is, what is the practical difference between the three options? Suppose I want to store private names in different alphabets, which option can I use? I assume that I will need to find specific names (select .. From .. Where ..), order names (select .. from .. Order By ..).

+4
source share
2 answers

  All 3 options are case and accent sensitive and support Unicode.

NVARCHAR - (, INT, DATETIME ..), . Unicode UCS-2/UTF-16 (Little Endian). UCS-2 UTF-16 - U+ 0000 - U+ FFFF ( 0 - 65535). UTF-16 U+ 10000 ( ), ( ), UCS-2. , . , , , _SC, UTF-16 , _SC. _SC SQL Server 2012, ; .

:

  1. NVARCHAR, , ( ). , , COLLATE, .
  2. , , -sensitive. , , . "" (, , , Kana SQL Server 2017: ), / . . : , -sensitive.

, , , SQL Server Locale/LCID ( , ) , VARCHAR.

"" /, , ( , LCID). , - , . , , , , u , 2 , u , , , .

_BIN _BIN2 , . _BIN ( , , ), _BIN2 ( SQL Server 2005) "" ( , _BIN2 , , ). , - , SQL Server "Little Endian", ( : UTF-16, INT, BIGINT ..) . . , U+ . (, BIN BIN2).

, , Unicode/NVARCHAR. , . , "BINARY" - VARCHAR.

, , ?

VARCHAR, ( ) , , 8- ASCII, 256 ( - , , - /). NVARCHAR , Unicode, , , .

NVARCHAR , . , , , . , . , . , , . , , , , : SQL- CJK (, S.O.).

, _BIN2, URL. URL , / . QueryString . URL , http://domain.tld/page.ext?var1=val http://domain.tld/page.ext?var1=VAL, . -sensitive , Latin1_General_100_BIN2, ( ) ü u + ( ü).

( , ):

DBA.StackExchange:

+3

nvarchar - , "BIN" "BIN2" - . .

nvarchar:

nchar nvarchar (Transact-SQL)

String, , nchar , nvarchar, Unicode UNICODE UCS-2.

https://msdn.microsoft.com/en-GB/library/ms186939(v=sql.105).aspx

nvarchar , , . .

COLLATE (Transact-SQL)

, .

https://msdn.microsoft.com/en-us/library/ms184391(v=sql.105).aspx

, SQL- (nvarchar, varchar, nchar, char) ​​ , ..

, "Latin1_General_CI_AI". . , :

  • Höller, höller, Holler, holler

, , ( ), , -.

, "CI_AS" ( ).

"_BIN" , .

:

nvarchar ( varchar), , , .

, . , "word1" - !

, nvarchar . - , .

, , "Latin1_General_CI_AI", , A A á â .., - .

, "word1" Aaáâ, SELECT * FROM words2 WHERE word1 = 'aaaa' .

"_BIN" , , . , "_BIN" . 2: - , , . .

, .

+2

More articles:


All Articles