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_id
by 에서 오프셋 된 부호있는 정수 카운터를 나타냅니다 -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을 넘어갑니다.