태그 보관물: constraint

constraint

SQL Server가 시스템 생성 제약 조건 이름에서 충돌을 만들 수 있습니까? 위해 버전

SQL Server 2008 데이터베이스에 클러스터되지 않은 수백만 개의 테이블을 만드는 응용 프로그램이 있습니다. SQL Server 2014 (클러스터)로 업그레이드하려고하지만로드 상태에서 오류 메시지가 표시됩니다.

“데이터베이스에 이미 ‘PK__tablenameprefix__179E2ED8F259C33B’라는 오브젝트가 있습니다.”

이것은 시스템 생성 제약 조건 이름입니다. 임의로 생성 된 64 비트 숫자처럼 보입니다. 테이블 수가 많기 때문에 충돌이 발생할 수 있습니까? 1 억 개의 테이블이 있다고 가정하면 다음 테이블을 추가 할 때 1 조에서 1 조 이하의 충돌 확률을 계산하지만 균일 한 분포를 가정합니다. SQL Server가 충돌 가능성을 높이기 위해 버전 2008과 2014 사이에서 이름 생성 알고리즘을 변경했을 수 있습니까?

다른 중요한 차이점은 2014 인스턴스가 클러스터 쌍이라는 점이지만 위의 오류가 발생하는 이유에 대한 가설을 세우는 데 어려움을 겪고 있습니다.

추신 : 그렇습니다. 수백만 개의 테이블을 만드는 것이 미친 것입니다. 이것은 내가 통제 할 수없는 블랙 박스 타사 코드입니다. 광기에도 불구하고 2008 버전에서는 작동했지만 현재 2014 버전에서는 작동하지 않습니다.

편집 : 면밀한 검사에서 생성 된 접미사는 항상 179E2ED8로 시작하는 것처럼 보입니다. 즉, 임의의 부분은 실제로는 32 비트 수이며 충돌 가능성은 새 테이블이 추가 될 때마다 1에서 50에 불과합니다. 내가보고있는 오류율과 훨씬 더 가깝습니다!



답변

SQL Server가 시스템 생성 제약 조건 이름에서 충돌을 만들 수 있습니까?

제약 조건 유형과 SQL Server 버전에 따라 다릅니다.

CREATE TABLE T1
(
A INT PRIMARY KEY CHECK (A > 0),
B INT DEFAULT -1 REFERENCES T1,
C INT UNIQUE,
CHECK (C > A)
)

SELECT name,
       object_id,
       CAST(object_id AS binary(4)) as object_id_hex,
       CAST(CASE WHEN object_id >= 16000057  THEN object_id -16000057 ELSE object_id +2131483591 END AS BINARY(4)) AS object_id_offset_hex
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('T1')
ORDER BY name;

drop table T1

예제 결과 2008

+--------------------------+-----------+---------------+----------------------+
|           name           | object_id | object_id_hex | object_id_offset_hex |
+--------------------------+-----------+---------------+----------------------+
| CK__T1__1D498357         | 491357015 | 0x1D498357    | 0x1C555F1E           |
| CK__T1__A__1A6D16AC      | 443356844 | 0x1A6D16AC    | 0x1978F273           |
| DF__T1__B__1B613AE5      | 459356901 | 0x1B613AE5    | 0x1A6D16AC           |
| FK__T1__B__1C555F1E      | 475356958 | 0x1C555F1E    | 0x1B613AE5           |
| PK__T1__3BD019AE15A8618F | 379356616 | 0x169C85C8    | 0x15A8618F           |
| UQ__T1__3BD019A91884CE3A | 427356787 | 0x1978F273    | 0x1884CE3A           |
+--------------------------+-----------+---------------+----------------------+

결과 예 2017

+--------------------------+------------+---------------+----------------------+
|           name           | object_id  | object_id_hex | object_id_offset_hex |
+--------------------------+------------+---------------+----------------------+
| CK__T1__59FA5E80         | 1509580416 | 0x59FA5E80    | 0x59063A47           |
| CK__T1__A__571DF1D5      | 1461580245 | 0x571DF1D5    | 0x5629CD9C           |
| DF__T1__B__5812160E      | 1477580302 | 0x5812160E    | 0x571DF1D5           |
| FK__T1__B__59063A47      | 1493580359 | 0x59063A47    | 0x5812160E           |
| PK__T1__3BD019AE0A4A6932 | 1429580131 | 0x5535A963    | 0x5441852A           |
| UQ__T1__3BD019A981F522E0 | 1445580188 | 0x5629CD9C    | 0x5535A963           |
+--------------------------+------------+---------------+----------------------+

기본 제한 조건의 경우, 점검 제한 조건 및 외래 키 제한 조건 자동 생성 이름의 마지막 4 바이트는 제한 조건의 오브젝트 ID의 16 진 버전입니다. 으로 objectid고유의 보장 이름은 고유해야합니다. Sybase에서도 이러한 사용tabname_colname_objectid

고유 제약 조건 및 기본 키 제약 조건 Sybase에서 사용

tabname_colname_tabindid. 여기서 tabindid는 테이블 ID와 인덱스 ID의 문자열 연결입니다.

이것도 독창성을 보장합니다.

SQL Server는이 체계를 사용하지 않습니다.

SQL Server 2008 및 2017 모두 시스템 생성 이름 끝에 8 바이트 문자열을 사용하지만 마지막 4 바이트 생성 방법에 대해 알고리즘이 변경되었습니다.

2008 년의 마지막 4 바이트는 음의 값을 최대 부호있는 정수로 줄 바꿈 하여 object_idby 에서 오프셋 된 부호있는 정수 카운터를 나타냅니다 -16000057. (의의 16000057는 이것이 연속적으로 생성 된 사이에 적용된 증분object_id 이라는 것입니다 ). 이것은 여전히 ​​독창성을 보장합니다.

2012 년부터 제약 조건의 object_id와 이름의 마지막 8 문자를 부호있는 int의 16 진수 표현으로 처리하여 얻은 정수 사이에 패턴이 전혀 보이지 않습니다.

2017 년 호출 스택의 함수 이름은 이제 이름 생성 프로세스의 일부로 GUID를 생성 함을 보여줍니다 (2008 년에는 언급이 없습니다 MDConstraintNameGenerator). 나는 이것이 임의의 근원을 제공하는 것이라고 생각합니다. 그러나 그것은 제약 조건 사이에서 변경되는 4 바이트의 GUID에서 전체 16 바이트를 사용하지 않습니다.

나는 새로운 알고리즘이 당신과 같은 극단적 인 경우 충돌 가능성이 증가함에 따라 효율성상의 이유로 수행되었다고 가정합니다.

PK의 테이블 이름 접두사와 열 이름 (마지막 8 앞에 오는 8 문자에 영향을 미치는 한)이 수만 테이블에 대해 동일해야하지만 가능한 한 재현 될 수 있기 때문에 이것은 병리학 적 사례입니다. 아래와 함께 쉽게.

CREATE OR ALTER PROC #P
AS
    SET NOCOUNT ON;

    DECLARE @I INT = 0;


    WHILE 1 = 1
      BEGIN
          EXEC ('CREATE TABLE abcdefghijklmnopqrstuvwxyz' + @I + '(C INT PRIMARY KEY)');
          SET @I +=1;
      END

GO

EXEC #P

새로 작성된 데이터베이스에 대해 SQL Server 2017에서 실행 된 예가 1 분 만에 실패했습니다 (50,931 개의 테이블이 작성된 후)

메시지 2714, 수준 16, 상태 30, 줄 15 이미 데이터베이스에 ‘PK__abcdefgh__3BD019A8175067CE’라는 개체가 있습니다. 메시지 1750, 수준 16, 상태 1, 줄 15 제약 조건 또는 인덱스를 만들 수 없습니다. 이전 오류를 참조하십시오.


답변

1 억 개의 테이블이 있다고 가정하면 1 조에서 1 조 미만의 충돌 확률을 계산합니다.

이것이 ” 생일 문제 ” 라는 것을 기억하십시오 . 주어진 단일 해시에 대해 충돌을 생성하려고 시도하지 않고 여러 쌍의 값 중 어느 것도 충돌하지 않을 확률을 측정합니다.

따라서 N 테이블에는 N * (N-1) / 2 쌍이 있으므로 약 10 16 쌍이 있습니다. 충돌 확률이 2 -64 이면 단일 쌍이 충돌하지 않을 확률은 1-2 -64 이지만 너무 많은 쌍이있을 경우 충돌이 없을 확률은 약 (1-2 -64 ) 10 16 또는 1 / 10,000 이상 예를 들어 https://preshing.com/20110504/hash-collision-probabilities/

그리고 32 비트 해시 인 경우 충돌 확률은 77k 값에서 1/2을 넘어갑니다.


답변