SQL Q&A: Non-Clustered Indexes, Retain Permissions Data

아티클
08/24/2016

SQL Q&A비클러스터형 인덱스 및 사용 권한 유지

Saleem Hakani and Dan Carollo

비클러스터형 인덱스의 정의 및 사용법

Q: 비클러스터링 인덱스란 무엇이며 장점과 단점은 무엇입니까?

A: 비클러스터형 인덱스는 일반적으로 책의 뒷부분에 있는 색인과 비슷합니다. 책에 포함된 모든 정보가 색인에 주제별로 표시되며 책의 여러 부분에 있을 수 있는 관련 정보를 찾을 수 있도록 도와 주는 포인터가 페이지 번호 형식으로 기재됩니다. 또한 색인의 데이터는 책의 본문에 나타나는 순서와 동일한 순서로 나열되지 않습니다. 이 점이 비클러스터형 인덱스와 동일합니다. 테이블에 클러스터형 인덱스가 있는 경우에는 항목의 순서를 지정할 수 있습니다. 그렇지 않은 경우에는 순서를 확인할 방법이 없습니다.

비클러스터형 인덱스에는 두 가지 제한이 있습니다. 즉, 인덱스에는 16개의 열만 포함될 수 있으며 인덱스 키의 최대 크기는 900바이트를 초과할 수 없습니다. 이 사실은 무엇을 의미할까요? 샘플 MovieList 데이터베이스의 Movie 테이블에서 MovieTitle NVarchar(50), DirectorName NVarchar(50), ShortStory NVarchar(400) 열을 인덱싱하려고 할 때 어떤 작업이 수행되는지 살펴보겠습니다.

다음 문을 실행하여 테이블을 만든다고 가정하겠습니다.

Use MovieList;
CREATE INDEX Movie_IDX ON Movie(MovieTitle, DirectorName, ShortStory);

그러면 "경고! 최대 키 길이는 900바이트입니다. 인덱스 'Movie_IDX'의 최대 길이는 1000바이트입니다. 큰 값을 조합한 일부 경우에는 삽입/업데이트 작업을 수행할 수 없습니다."와 같은 오류 메시지가 나타납니다. "nvarchar" 데이터 형식은 각 문자에 대해 2바이트를 사용하므로 위의 세 열이 포함된 인덱스는 900바이트의 크기 제한을 초과합니다.

SQL Server® 2005가 출시된 지금은 INCLUDE 절에 열을 추가하는 방법으로 이 문제를 해결할 수 있습니다. 이것은 크기 제한과 열 제한을 모두 해결하려고 할 때 매우 유용한 기능입니다. 다음 문을 실행하면 제한을 해결할 수 있습니다.

CREATE INDEX Movie_IDX ON Movie(MovieTitle, DirectorName) INCLUDE (ShortStory);

인덱스 생성 도중 INCLUDE 절을 사용하면 데이터베이스 엔진에서 인덱스 키 열의 수 또는 인덱스 키 크기를 계산할 때 키가 아닌 열을 고려하지 않는다는 점을 유념하십시오.

비클러스터형 인덱스에 대한 자세한 내용을 보려면 "비클러스터형 인덱스"(msdn2.microsoft.com/aa174537)와 "비클러스터형 인덱스 사용"(msdn2.microsoft.com/aa933130)을 참조하십시오. 인덱스를 최적화하기 위한 팁에 대해서는 sql-server-performance.com/optimizing_indexes.asp를 참조하십시오.

사용 권한 데이터 보존

Q: 구독이 다시 초기화될 때 사용 권한이 손실되지 않도록 하려면 어떻게 해야 합니까? 저는 스냅숏이 다시 초기화될 때 부여된 사용 권한이 모두 손실되는 문제를 여러 번 경험했습니다.

A: 기본적으로 구독 데이터베이스의 모든 개체는 구독이 다시 초기화될 때마다 삭제되었다 다시 생성됩니다. 하지만 두 가지 방법으로 이 문제를 해결할 수 있습니다.

첫 번째로, 다시 초기화한 후 모든 사용 권한을 다시 적용할 수 있습니다. 사용 권한을 수동으로 설정할 때는 구독을 다시 초기화한 후 즉시 이를 사용할 수 있도록 개체/명령문 수준의 모든 사용 권한을 스크립트로 만들어 따로 저장하십시오.

두 번째로, 구독을 다시 초기화할 때마다 개체를 삭제하지 않도록 구독을 구성합니다. 이렇게 하려면 SP_CHANGEARTICLE 시스템 저장 프로시저를 사용하여 매개 변수 @PROPERTY에 대한 PRE_CREATION_CMD의 값과 매개 변수 @Value에 대한 NONE, DELETE 또는 TRUNCATE의 값을 구성할 수 있습니다.

또한 대상 개체 섹션의 아티클 속성 대화 상자에서 "기존 개체를 변경하지 않고 유지, 데이터를 삭제합니다. 아티클에 행 필터가 있으면 필터에 대응하는 데이터만 삭제합니다. 기존 개체의 모든 데이터를 잘라냅니다." 값을 선택합니다. 이 작업은 테스트 환경에서 수행해야 하며 도움이 필요한 경우 최신 버전의 SQL Server 온라인 설명서에서 최신 정보를 확인하십시오.

Saleem Hakani는 14년 이상의 데이터베이스 시스템 경력을 지닌 수석 데이터베이스 엔지니어 겸 선임 문제 해결 엔지니어이자 국제 Microsoft SQL Server 커뮤니티 운영자입니다. 현재 외부 SQL Server 커뮤니티 웹 사이트인 sqlcommunity.com을 운영하고 있으며 Saleem@sqlcommunity.com으로 연락할 수 있습니다.

Dan Carollo는 Microsoft의 Windows 맬웨어 방지 연구 및 대응 팀에서 근무하는 운영 엔지니어 겸 SQL Server DBA로 SQL Server MCT 자격증을 보유하고 있습니다.

추가 리소스