Index IGNORE_DUP_KEY 옵션 TEST

No	방법	문제점
1	클러스터 Key IGNORE_DUP_KEY 옵션	온라인 Reindex가 되지 않음 -DBA 상에서 관리 이슈 있음 성능 이슈 -  Data 비교를 하기 때문에 일반 보다는 비용이 더 소요 됨 성능 TEST No1 참고
2	유니크 Index를 생성하고 IGNORE_DUP_KEY 옵션	No 1의 문제점 해결 동일 Key에 Index가 더 생성되는 것이며,  No1 번과 마찬가지로 데이터 입력시 Sort 비용,  Semi Join 비용,  Buffer Pool 공간 사용 비용이 증가하여 기본 예상 비용 보다  3배 정도 초과 운영됨 데이터를 삽입 하는 동안 정합성을 유지 하기 위해  입력 Data Range 에 대한 Shared range, Update Lock를 발생 함 동시 변경시 문제가 있을 수 있고,  SQL 아키텍쳐상 Lock이  Escalation 될 수 있으므로 Table Lock으로 발전 될 수 있는 가능 성 있음 성능 Test No2 참고
3	쿼리 문을 수정 하는 방법	Insert 문을 수정해서 입력 하는 방법 - EXCEPT 방식, Merage 방식 -- Insert 문 수정  with except INSERT dbo.PKDUP (pk) select  i from dbo.DUMP_INSERT where i between 10000 and 130000 except select pk from dbo.PKDUP   -- merge 문 사용 MERGE dbo.PKDUP  AS b USING (SELECT i FROM dbo.DUMP_INSERT WHERE i BETWEEN 10000 AND 130000) AS s ON s.i = b.pk WHEN NOT MATCHED THEN INSERT (pk) VALUES (s.i); 성능 TEST No3 참고
4	INSTEAD OF trigger 사용 방법	3번 으로 하면 가장 좋으나 대량의 bulk 성으로 입력을 해야 하는 경우 대안이 될 수 있을 것 같음 해당 트리거는 테이블당 하나만 생성 할 수 있고 INSTEAD 트리거가 제약 조건에 위배 되면 AFTER이 실행 되지 않음을 알아야 함 성능 Test No4 참고

CREATE TABLE dbo.PK_DUP (     pk int NOT NULL,  CONSTRAINT PK_PK_DUP PRIMARY KEY (pk) WITH (IGNORE_DUP_KEY = ON) ); GO select * from sys.indexes where object_id = object_id ('PK_DUP') -- Unique values INSERT dbo.PK_DUP (pk) VALUES (1), (3), (5); GO -- key 3 already exists INSERT dbo.PK_DUP (pk) VALUES (2), (3), (4); /* 중복 키가 무시되었습니다. (2개 행이 영향을 받음) */ select * from PK_DUP /* pk 1 2 3 4 5 */ /**  Problem 1 :  Rebild를 할  수 없다. **/ ALTER INDEX PK_PK_DUP ON dbo.PK_DUP REBUILD WITH (FILLFACTOR = 90, ALLOW_ROW_LOCKS = OFF, ONLINE = ON, IGNORE_DUP_KEY = ON); GO /* 메시지 1979, 수준 16, 상태 1, 줄 1 인덱스 옵션 ignore_dup_key는 PRIMARY KEY 또는 UNIQUE 제약 조건을 적용하므로 이 인덱스 옵션을 사용하여 인덱스 'PK_PK_DUP'을(를) 변경할 수 없습니다. */

[성능 TEST No2]

-- 새로운 Idea -- 유니크 key를 만들어서 처리 한다. CREATE TABLE dbo.PKDUP (pk int NOT NULL CONSTRAINT PK_PKDUP PRIMARY KEY); GO INSERT dbo.PKDUP (pk) VALUES (1), (2), (3); GO -- New constraint (or index) with IGNORE_DUP_KEY, added ONLINE ALTER TABLE dbo.PKDUP  ADD CONSTRAINT UIDX_PKDUP  UNIQUE NONCLUSTERED (pk) WITH (IGNORE_DUP_KEY = ON, ONLINE = ON); select * from sys.indexes where object_id = object_id ('PKDUP')   -- key 3을 입력 한다. INSERT dbo.PKDUP (pk) VALUES (3), (4), (5); GO /*중복 키가 무시되었습니다. */ SELECT pk FROM dbo.PKDUP; /* pk 1 2 3 4 5 */ -- Problem 1 해결 ALTER INDEX PK_PKDUP ON dbo.PKDUP REBUILD WITH (FILLFACTOR = 90, ALLOW_ROW_LOCKS = OFF, ONLINE = ON); GO ALTER INDEX UIDX_PKDUP ON dbo.PKDUP REBUILD WITH (FILLFACTOR = 100, ALLOW_ROW_LOCKS = OFF, ONLINE = ON); GO No1의 문제는 해결 되나  성능 상의 이슈로  입력 하는 데이터와 기존 데이터를 비교 하는 추가 적인 작업 단계가 늘어나면서 예상 비용이 3배 정도 증가 함.

[성능 TEST No3]

CREATE TABLE DUMP_INSERT ( i int ) set nocount on declare @i int  set @i =1 while (@i < 10000 ) begin              insert into DUMP_INSERT  values ( @i )              set @i = @i + 1 end insert into PKDUP select * from DUMP_INSERT set nocount on declare @i int  set @i =90000 while (@i < 110001 ) begin              insert into DUMP_INSERT  values ( @i )              set @i = @i + 1 end -- 유니크 Index 삭제 alter table dbo.PKDUP drop constraint  UIDX_PKDUP set nocount on declare @i int  set @i =120001 while (@i < 150001 ) begin              insert into DUMP_INSERT  values ( @i )              set @i = @i + 1 end     -- Insert 문 수정  with except INSERT dbo.PKDUP (pk) select  i from dbo.DUMP_INSERT where i between 10000 and 130000 except select pk from dbo.PKDUP   n  여전히  Buffer pool 공간은 사용 하지만 예상 비용은 0.96         -- merge 문 사용 MERGE dbo.PKDUP  AS b USING (SELECT i FROM dbo.DUMP_INSERT WHERE i BETWEEN 10000 AND 130000) AS s ON s.i = b.pk WHEN NOT MATCHED THEN INSERT (pk) VALUES (s.i);   -    비용은 증가하나 Buffer pool 공간은 사용하지 않음

성능 TEST No4]

-- INSTEAD OF trigger -- INSTEAD OF 트리거는 트리거 문의 표준 동작을 무시합니다. -- 따라서 이 트리거를 사용하여 하나 이상의 열에서 오류나 값을 확인하고 행을 삽입, 업데이트 또는 삭제하기 전에 추가 동작을 수행할 수 있습니다. -- INSTEAD OF 트리거가 제약 조건을 위한 하면 AFTER 트리거는 실행되지 않습니다. CREATE TRIGGER  tI_PKDUP ON DBO.PKDUP INSTEAD OF INSERT AS BEGIN              SET NOCOUNT ON              INSERT dbo.PKDUP (pk)              SELECT PK FROM INSERTED              except select pk from dbo.PKDUP END GO INSERT dbo.PKDUP (pk) SELECT i FROM dbo.DUMP_INSERT WHERE i BETWEEN 10000 AND 130000   -    Mearge랑 비슷한 예상 비용이 발생 함. 트리거 사용을 권하지는 않지만 대량 데이터 입력으로 인해 No3 번이 불가능 할 때 고려해 볼 만 함.