데이터베이스 가 이미 정의되어 있는 기존 데이터베이스 인 경우 DML 작업 (실제로 이미 존재 했음)에 대한 잠재적 성능 영향을 넘어서 데이터베이스 데이터 정렬 변경 작업에 심각한 영향을 미칩니다. 성능과 기능에 실제로 영향을 미치며, 이러한 변경은 의도 된 목표를 달성하지 못했을뿐만 아니라 (적어도 일관성이 없음) 다음과 같은 측면에서 동작이 변경되거나 새 테이블이 생성 될 때 동작이 변경 될 수 있습니다. 데이터의 순서와 방정식.

Paul은 이미 답변에서 여러 유형의 데이터 정렬간에 성능과 동작의 차이점에 대한 좋은 설명과 예를 제공 했으므로 여기서는 반복하지 않겠습니다. 그러나 몇 가지 사항에는 추가 세부 정보가 필요하며 새 DB의 데이터 정렬을 설정하는 대신 기존 DB의 데이터 정렬을 변경하는 현재 시나리오와 관련하여 추가해야 할 몇 가지 다른 점이 있습니다.

1. 이진 데이터 정렬은 대소 문자를 구분하는 것 이상입니다. 모두 대소 문자를 구분 합니다! 따라서 이진 데이터 정렬 ( _BIN또는로 끝나는)을 사용하면 _BIN2비교가 악센트 구분, 가나 구분, 폭 구분 및 글루텐에 민감합니다 (적어도 요즘 추세 인 것처럼 보입니다. ;-)). 이것이 이번 변경의 바람직한 영향 이었습니까? 최종 사용자가 이러한 행동 변화를 기대하고 있습니까?

2. 데이터 정렬은 비교뿐만 아니라 정렬에도 영향을줍니다. 이진 데이터 정렬은 각 바이트 의 ASCII또는 UNICODE바이트 값 ( 각각 VARCHAR또는 에 따라 다름)을 기준으로 정렬됩니다 . 따라서 이진 데이터 정렬을 선택하면 해당 문화권의 알파벳에 따라 각 문자 (헝가리어 등의 일부 언어의 문자도 2 개 문자)를 정렬하는 언어 / 문화 별 가중치 규칙을 포기하게됩니다 . 따라서 “ch”가 자연스럽게 “k” 뒤에 오면 이진 데이터 정렬을 사용하지 않습니다. 다시, 이것이이 변경을하는 데 바람직한 영향 이었습니까? 최종 사용자가 이러한 행동 변화를 기대하고 있습니까?NVARCHAR

3. 응용 프로그램에 대한 특정 하위 호환성 요구 사항이없는 한 , 물론 이진 데이터 정렬을 원한다고 가정하면 데이터 정렬 BIN2대신 BIN데이터 정렬 을 사용해야합니다 . BIN2데이터 정렬은 SQL Server 2005에서 도입 등에 대한 MSDN 페이지에 따라 한 BIN 및 BIN2 데이터 정렬 사용에 대한 지침 :

   “_BIN”으로 끝나는 SQL Server의 이전 이진 데이터 정렬은 유니 코드 데이터에 대해 불완전한 코드 간 비교를 수행했습니다. 이전 SQL Server 이진 데이터 정렬은 첫 번째 문자를 WCHAR로 비교 한 다음 바이트 단위로 비교했습니다.

   …

   [_BIN2] 이진 데이터 정렬로 마이그레이션하여 실제 코드 포인트 비교를 활용할 수 있으며 새 응용 프로그램 개발에 새 이진 데이터 정렬을 사용해야합니다.

   또한 _BIN2데이터 정렬 Ordinal은 StringComparison Enumeration 옵션의 동작과 편리하게 일치하므로 해당 옵션을 사용하여 .NET 코드에서 수행 한 비교 및 ​​정렬은 SQL Server 내에서 수행되는 동일한 작업과 동일한 결과를 얻을 수 있습니다 (사용시) _BIN2정렬, 물론).

4. _BIN2이전 버전과의 호환성 동작을 유지하기위한 특정 요구 사항이없는 한 데이터 정렬 과 관련하여 방금 언급 한 것과 유사한 이유로 SQL Server 관련 데이터 정렬이 아닌 Windows 데이터 정렬을 사용해야합니다 (예 : SQL_이제 시작 데이터 정렬). “끈적 거리는”;-)).

5. 유니 코드 데이터 (예 : N데이터 유형이 NChar또는 로 지정된 응용 프로그램 코드에서 접두사가 있거나 SQL Server로 오는 문자열 NVarChar)를 사용할 때 한 데이터 정렬을 사용하는 것과 다른 데이터 정렬을 사용하는 경우 NCHAR또는 NVARCHAR문자열 필드 를 삽입하거나 업데이트하는 데 어떻게 차이가 있는지 알 수 없습니다 .

   유니 코드가 아닌 데이터를 사용하여, 또는 비 유니 코드 필드, 다음 특정 조합 (데이터베이스 또는 필드)에 삽입하거나 갱신하는 힘 에 삽입하거나 업데이트 할 때 삽입 / 업데이트되는 문자를 번역해야하거나 매핑 할 수없는 경우 작은 역할을 할 수 있습니다. 데이터 정렬에 의해 정의 된 코드 페이지에서 지정한대로 물론이 잠재적 인 문제는 유니 코드가 아닌 데이터 또는 데이터 형식을 사용할 때마다 발생하며 DB 데이터 정렬 변경 시나리오에는 해당되지 않습니다. 이 변경은 문자열 리터럴에 영향을 미칩니다 (DB 데이터 정렬이 필드 데이터 정렬과 다른 경우 이미 문제가되었을 수 있음). 그러나 DB 데이터 정렬을 변경하지 않더라도 다른 DB 또는 SQL Server 외부 (클라이언트 코드) 외부에서 들어오는 데이터는 문자를 포함 할 수 있으며 특정 인코딩 일 수 있습니다.

6. 매우 중요!!! 데이터베이스 기본 데이터 정렬을 변경할 때 기존 테이블의 기존 문자열 필드에 지정된 데이터 정렬은 변경 되지 않지만 새 필드는 데이터베이스 기본값의 데이터 정렬을 갖습니다 (COLLATE ). 이는 세 가지 방식으로 쿼리에 영향을줍니다.

   1) 기존 필드 중 하나를 새 필드 중 하나에 쿼리하면 데이터 정렬 불일치 오류가 발생합니다.

   USE [master];
   GO
   
   IF (DB_ID(N'ChangeCollationTest') IS NOT NULL)
   BEGIN
       PRINT 'Dropping [ChangeCollationTest] DB...';
       ALTER DATABASE [ChangeCollationTest]
           SET SINGLE_USER
           WITH ROLLBACK IMMEDIATE;
   
       DROP DATABASE [ChangeCollationTest];
   END;
   GO
   
   PRINT 'Creating [ChangeCollationTest] DB...';
   CREATE DATABASE [ChangeCollationTest]
       COLLATE SQL_Latin1_General_CP1_CI_AS;
   GO
   
   USE [ChangeCollationTest];
   GO
   
   CREATE TABLE [CollateTest-SQL_Latin1_General_CP1_CI_AS]
                (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT, Col2 NVARCHAR(50));
   SELECT *
   FROM   sys.columns sc
   WHERE  sc.[object_id] = OBJECT_ID(N'[CollateTest-SQL_Latin1_General_CP1_CI_AS]');
   -- "collation_name" for both fields shows: SQL_Latin1_General_CP1_CI_AS
   GO
   
   USE [master];
   GO
   ALTER DATABASE [ChangeCollationTest]
       COLLATE Latin1_General_BIN2;
   GO
   USE [ChangeCollationTest];
   GO
   
   CREATE TABLE [CollateTest-Latin1_General_BIN2]
                (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT, Col2 NVARCHAR(50));
   SELECT *
   FROM   sys.columns sc
   WHERE  sc.[object_id] = OBJECT_ID(N'[CollateTest-Latin1_General_BIN2]');
   -- "collation_name" for both fields shows: Latin1_General_BIN2
   GO
   
   
   SELECT *
   FROM   dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] ctSQL
   INNER JOIN  dbo.[CollateTest-Latin1_General_BIN2] ctWIN
           ON  ctWIN.Col1 = ctSQL.Col1;

   보고:

   Msg 468, Level 16, State 9, Line 4
   Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
   "Latin1_General_BIN2" in the equal to operation.

   2) 문자열 리터럴 또는 변수와 비교되는 기존 테이블의 기존 필드 (이전 기본 데이터 정렬로 설정 )의 술어 / 필터는 오류가 발생하지 않지만 SQL Server의 데이터 정렬을 동일하게해야하기 때문에 성능에 영향을 줄 수 있습니다. 문자열 리터럴 또는 변수를 필드의 데이터 정렬로 자동 변환합니다. “실제 실행 계획 포함”(Control-M)을 사용하도록 설정 한 다음 위의 쿼리를 이미 실행했다고 가정하면 다음을 실행합니다.

   SELECT *
   FROM   dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] ctSQL
   WHERE  ctSQL.Col1 = N'a';
   -- Unspecified collations on string literals and variables assume the database default
   -- collation. This mismatch doesn't cause an error because SQL Server adds a
   -- "[Col1]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)" but it can hurt performance.
   
   SELECT *
   FROM   dbo.[CollateTest-Latin1_General_BIN2] ctWIN
   WHERE  ctWIN.Col1 = N'a';
   -- No CONVERT_IMPLICIT; plan shows "[Col1]=[@1]".

   3) 와, 암시 적 변환의 말하기, 통지는 데이터베이스의 기본 데이터 정렬의 묵시적 정렬과 문자열 리터럴 (방법 : Latin1_General_BIN2변환됩니다) 하지 테이블에 필드. 이 필터가 대소 문자를 구분하지 않는지 (이전 데이터 정렬) 또는 대소 문자를 구분하는지 (새로운 데이터 정렬)에 대한 추측이 있습니까? 다음을 실행하여 확인하십시오.

   INSERT INTO dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] (Col1)
   VALUES (N'a'), (N'A');
   
   SELECT ctSQL.Col1
   FROM   dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] ctSQL
   WHERE  ctSQL.Col1 = N'a';

   보고:

   Col1
   ----
   a
   A

   도! 로 인해이 쿼리에 약간의 성능 저하가있을뿐만 아니라 CONVERT_IMPLICIT()원하는 대소 문자 구분 방식으로 동작하지 않습니다.

   Ergo, 이미 테이블이있는 DB에서 데이터 정렬이 변경되면 성능과 기능이 모두 영향을받습니다.

   데이터 정렬이 새 DB에서 설정되는 경우 Paul은 이진 데이터 정렬이 빠르지 만 예상하거나 원하는 방식으로 정렬되지 않는 방법을 설명하여 이미이를 다루었습니다.

---

또한 조건별로 데이터 정렬을 항상 지정할 수 있습니다. 부씩 절에 추가 할 수있는 WHERE조건 ORDER BY문자열을 받아 어떤 장소를, 대부분.

예 1 (여기서 조건) :

SELECT tmp.col AS [SQL-CaseSensitive]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
WHERE tmp.col > 'a' COLLATE SQL_Latin1_General_CP1_CS_AS;

SELECT tmp.col AS [Windows-CaseSensitive]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
WHERE tmp.col > 'a' COLLATE Latin1_General_CS_AI;

보고:

SQL-CaseSensitive
-----------------
b
B

Windows-CaseSensitive
-----------------
A
b
B

예 2 (주문) :

SELECT tmp.col AS [Windows-CaseSensitive]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
ORDER BY tmp.col COLLATE Latin1_General_CS_AI;

SELECT tmp.col AS [Windows-Binary]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
ORDER BY tmp.col COLLATE Latin1_General_BIN2;

보고:

Windows-CaseSensitive
-----------------
a
A
b
B

Windows-Binary
-----------------
A
B
a
b

예 3 (IF 문) :

IF ('A' = 'a') SELECT 1 AS [DatabaseDefault-CaseInsensitive?];
-- if the DB is not case-sensitive or binary, returns 1

IF ('A' = 'a' COLLATE Latin1_General_BIN2) SELECT 2 AS [Windows-Binary];

보고:

DatabaseDefault-CaseInsensitive?
--------------------------------
1

{nothing}

예 4 (함수 입력 매개 변수와 연관 됨) :

SELECT  UNICODE(N'?') AS [UCS-2],
        UNICODE(N'?' COLLATE Latin1_General_100_CI_AS_SC) AS [UTF-16];
-- This character is a Unicode supplemental character and is not part of the
-- default UCS-2 encoding. In order for built-in functions to handle these
-- characters correctly, either the DB default collation needs to end in
-- "_SC" (available as of SQL Server 2012), or use as shown here.
-- See the character in more detail here: http://unicode-table.com/en/1F0A1/

보고:

UCS-2    UTF-16
------   -------
55356    127137

UCS-2 값 55,356은 “서로 게이트 쌍”의 두 값 중 첫 번째 값이므로 부분적으로 정확합니다. 그러나 _SC데이터 정렬을 명시 적으로 지정하지 않으면 UNICODE()함수는 각 문자를 2 바이트 값으로 만 볼 수 있으며 2 바이트 2 바이트 대리 쌍을 올바르게 처리하는 방법을 모릅니다.

---

최신 정보

위의 모든 예를 사용하더라도 일반적으로 간과되고 이진 비교 / 배열에 의해 무시되는 대소 문자 구분 비교의 한 측면은 유니 코드의 일부인 정규화 (구성 및 분해)입니다.

예 5 (이진 비교가 대소 문자를 구분 하지 않는 경우) :

대소 문자를 구분하여 비교하면 다른 문자와 함께 다른 유니 코드 코드 포인트로 이미 존재하는 다른 문자를 구성하는 문자를 결합 할 수 있습니다. 대소 문자 구분 비교는 표시 할 문자를 만드는 데 사용되는 코드 포인트가 아니라 표시 가능한 문자를 처리합니다.

SELECT 'Equal' AS [Binary],
       NCHAR(0x00FC) AS [ü],
       N'u' + NCHAR(0x0308) AS [u + combining diaeresis]
WHERE  NCHAR(0x00FC) COLLATE Latin1_General_100_BIN2
    =  N'u' + NCHAR(0x0308) COLLATE Latin1_General_100_BIN2
-- No result as they are a different number of code points,
-- as well as being different code points.

SELECT 'Equal' AS [Case-Sensitive],
       NCHAR(0x00FC) AS [ü],
       N'u' + NCHAR(0x0308) AS [u + combining diaeresis]
WHERE  NCHAR(0x00FC) COLLATE Latin1_General_100_CS_AS -- ü
    =  N'u' + NCHAR(0x0308) COLLATE Latin1_General_100_CS_AS -- u + combining diaeresis
-- Result set returned, even being a different number of code points AND Accent Sensitive,
-- due to normalization

보고:

Binary            ü     u + combining diaeresis
-------          ---   -------------------------
{nothing}

Case-Sensitive    ü     u + combining diaeresis
---------------  ---   -------------------------
Equal             ü     ü

대소 문자를 구분하여 비교하면 넓은 문자를 넓은 문자와 동일하게 사용할 수 있습니다.

IF (N'ｓｏｆｉａ' = N'sofia' COLLATE Latin1_General_100_BIN2)
  SELECT 'Values are the same' AS [Binary]
ELSE
  SELECT 'Values are different' AS [Binary];


IF (N'ｓｏｆｉａ' = N'sofia' COLLATE Latin1_General_100_CS_AS)
  SELECT 'Values are the same' AS [Case-Sensitive]
ELSE
  SELECT 'Values are different' AS [Case-Sensitive];

보고:

Binary
---------------
Values are different


Case-Sensitive
---------------
Values are the same

에르고 :

이진 ( _BIN및 _BIN2) 데이터 정렬은 대소 문자를 구분 하지 않습니다 !

---

답변