SQL Server 인덱스 디자인 가이드SQL Server Index Design Guide

이 항목 적용 대상: 예SQL Server예Azure SQL 데이터베이스예Azure SQL 데이터 웨어하우스 예 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

데이터베이스 응용 프로그램 병목 상태는 주로 잘못 디자인된 인덱스와 인덱스의 부족으로 인해 나타납니다.Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. 최적의 데이터베이스와 최상의 응용 프로그램 성능을 위해서는 효율적인 인덱스를 디자인하는 것이 가장 중요합니다.Designing efficient indexes is paramount to achieving good database and application performance. 이 SQL Server 인덱스 디자인 가이드에서는 응용 프로그램 요구 사항을 충족하는 효율적인 인덱스를 디자인하는 데 도움이 되는 정보와 최선의 구현 방법을 제공합니다.This SQL Server index design guide contains information and best practices to help you design effective indexes to meet the needs of your application.

이 가이드에서는 사용자가 SQL ServerSQL Server에서 사용할 수 있는 인덱스 유형에 대한 기본적인 지식이 있다고 가정합니다.This guide assumes the reader has a general understanding of the index types available in SQL ServerSQL Server. 인덱스 형식에 대한 일반적인 설명은 인덱스 유형을 참조하십시오.For a general description of index types, see Index Types.

인덱스 디자인 기본 사항Index Design Basics

인덱스는 테이블이나 뷰와 관련된 디스크상 구조로서 테이블이나 뷰의 행 검색 속도를 향상시킵니다.An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. 인덱스에는 테이블이나 뷰에 있는 하나 이상의 열로 작성되는 키가 포함됩니다.An index contains keys built from one or more columns in the table or view. 이러한 키는 SQL Server에서 키 값과 연결된 행을 빠르고 효율적으로 찾을 수 있는 구조(B-트리)에 저장됩니다.These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

데이터베이스에 적합한 인덱스를 선택하는 것은 쿼리 속도와 업데이트 비용 간의 균형을 조정해야 하는 복잡한 작업입니다.The selection of the right indexes for a database and its workload is a complex balancing act between query speed and update cost. 좁은 인덱스나 인덱스 키의 열이 적은 인덱스는 디스크 공간과 유지 관리 오버헤드를 덜 요구합니다.Narrow indexes, or indexes with few columns in the index key, require less disk space and maintenance overhead. 반대로 넓은 인덱스는 더 많은 쿼리를 처리합니다.Wide indexes, on the other hand, cover more queries. 가장 효율적인 인덱스를 찾기 위해 여러 디자인을 시험해 봐야 할 수 있습니다.You may have to experiment with several different designs before finding the most efficient index. 데이터베이스 스키마나 응용 프로그램에 영향을 주지 않고 인덱스를 추가, 수정 및 삭제할 수 있습니다.Indexes can be added, modified, and dropped without affecting the database schema or application design. 따라서 원하는 대로 여러 인덱스를 시험해 볼 수 있습니다.Therefore, you should not hesitate to experiment with different indexes.

SQL ServerSQL Server 의 쿼리 최적화 프로그램은 대부분의 경우 가장 효율적인 인덱스를 제대로 찾습니다.The query optimizer in SQL ServerSQL Server reliably chooses the most effective index in the vast majority of cases. 전체 인덱스 디자인 전략에서는 쿼리 최적화 프로그램이 가장 효율적인 인덱스를 찾을 수 있도록 다양한 인덱스를 제공하고 쿼리 최적화 프로그램의 선택을 신뢰해야 합니다.Your overall index design strategy should provide a variety of indexes for the query optimizer to choose from and trust it to make the right decision. 이렇게 하면 분석 시간이 줄어들고 다양한 상황에서 좋은 성능을 얻을 수 있습니다.This reduces analysis time and produces good performance over a variety of situations. 특정 쿼리에 대해 쿼리 최적화 프로그램이 사용하는 인덱스를 확인하려면 SQL Server Management StudioSQL Server Management Studio쿼리 메뉴에서 실제 실행 계획 포함을 선택합니다.To see which indexes the query optimizer uses for a specific query, in SQL Server Management StudioSQL Server Management Studio, on the Query menu, select Include Actual Execution Plan.

인덱스 사용이 항상 좋은 성능을 의미하지 않으며 마찬가지로 좋은 성능이 항상 효율적인 인덱스 사용을 나타내는 것은 아닙니다.Do not always equate index usage with good performance, and good performance with efficient index use. 인덱스 사용이 최상의 성능을 내는 데 항상 도움이 된다면 쿼리 최적화 프로그램의 작업은 간단할 것입니다.If using an index always helped produce the best performance, the job of the query optimizer would be simple. 실제로 인덱스를 잘못 선택하면 최상의 성능을 얻지 못할 수 있습니다.In reality, an incorrect index choice can cause less than optimal performance. 따라서 쿼리 최적화 프로그램에서는 성능을 향상시킬 경우에만 인덱스나 인덱스 조합을 선택하고 성능을 저하시킬 경우에는 인덱싱된 검색을 피해야 합니다.Therefore, the task of the query optimizer is to select an index, or combination of indexes, only when it will improve performance, and to avoid indexed retrieval when it will hinder performance.

인덱스 디자인 태스크Index Design Tasks

권장되는 인덱스 디자인 전략은 다음과 같은 태스크로 이루어집니다.The follow tasks make up our recommended strategy for designing indexes:

  1. 데이터베이스의 특징을 이해합니다.Understand the characteristics of the database itself. 예를 들어 데이터베이스가 데이터 수정이 잦은 OLTP(온라인 트랜잭션 처리) 데이터베이스인지, DSS(의사 결정 지원 시스템)인지, 주로 읽기 전용 데이터를 포함하고 매우 많은 데이터 집합을 빠르게 처리해야 하는 OLAP(데이터 웨어하우징) 데이터베이스인지를 파악해야 합니다.For example, is it an online transaction processing (OLTP) database with frequent data modifications, or a Decision Support System (DSS) or data warehousing (OLAP) database that contains primarily read-only data and must process very large data sets quickly. SQL Server 2012SQL Server 2012에서 xVelocity memory optimized columnstore 인덱스는 일반 데이터 웨어하우징 데이터 집합에 특히 적합합니다.In SQL Server 2012SQL Server 2012, xVelocity memory optimized columnstore index is especially appropriate for typical data warehousing data sets. Columnstore 인덱스는 필터링, 집계, 그룹화, 스타 조인 쿼리 등 일반 데이터 웨어하우징 쿼리에 대한 성능을 개선하여 사용자의 데이터 웨어하우징 환경을 바꿀 수 있습니다.Columnstore indexes can transform the data warehousing experience for users by enabling faster performance for common data warehousing queries such as filtering, aggregating, grouping, and star-join queries. 자세한 내용은 Columnstore 인덱스 가이드를 참조하세요.For more information, see Columnstore Indexes Guide.

  2. 가장 자주 사용하는 쿼리의 특성을 이해합니다.Understand the characteristics of the most frequently used queries. 예를 들어 두 개 이상의 테이블을 조인하는 쿼리를 자주 사용한다는 것을 알면 가장 적절한 인덱스 유형을 결정하는 데 도움이 됩니다.For example, knowing that a frequently used query joins two or more tables will help you determine the best type of indexes to use.

  3. 쿼리에 사용되는 열의 특성을 이해합니다.Understand the characteristics of the columns used in the queries. 예를 들어 인덱스는 정수 데이터 형식을 사용하는 고유 열이나 Null이 아닌 열에 이상적입니다.For example, an index is ideal for columns that have an integer data type and are also unique or nonnull columns. 열에 데이터의 잘 정의된 하위 집합이 있는 경우 SQL Server 2008SQL Server 2008 이상 버전에서 필터링된 인덱스를 사용할 수 있습니다.For columns that have well-defined subsets of data, you can use a filtered index in SQL Server 2008SQL Server 2008 and higher versions. 자세한 내용은 이 가이드의 필터링된 인덱스 디자인 지침 을 참조하십시오.For more information, see Filtered Index Design Guidelines in this guide.

  4. 인덱스를 만들거나 유지 관리할 때 성능을 향상시킬 수 있는 인덱스 옵션을 파악합니다.Determine which index options might enhance performance when the index is created or maintained. 예를 들어 기존의 큰 테이블에 클러스터형 인덱스를 만들 때 ONLINE 인덱스 옵션을 사용하는 것이 좋습니다.For example, creating a clustered index on an existing large table would benefit from the ONLINE index option. ONLINE 옵션을 사용하면 인덱스를 만들거나 새로 작성하는 동안 기본 데이터를 작업할 수 있습니다.The ONLINE option allows for concurrent activity on the underlying data to continue while the index is being created or rebuilt. 자세한 내용은 인덱스 옵션 설정을 참조하세요.For more information, see Set Index Options.

  5. 인덱스에 가장 적합한 저장 위치를 파악합니다.Determine the optimal storage location for the index. 비클러스터형 인덱스는 기본 테이블과 같은 파일 그룹이나 다른 파일 그룹에 저장할 수 있습니다.A nonclustered index can be stored in the same filegroup as the underlying table, or on a different filegroup. 인덱스의 저장 위치는 디스크 I/O 성능을 높여 쿼리 성능을 향상시킬 수 있습니다.The storage location of indexes can improve query performance by increasing disk I/O performance. 예를 들어 테이블 파일 그룹과 다른 디스크에 있는 파일 그룹에 비클러스터형 인덱스를 저장하면 동시에 여러 디스크를 읽을 수 있으므로 성능이 향상될 수 있습니다.For example, storing a nonclustered index on a filegroup that is on a different disk than the table filegroup can improve performance because multiple disks can be read at the same time.

    또는 클러스터형 인덱스와 비클러스터형 인덱스가 여러 파일 그룹에 하나의 파티션 구성표를 사용할 수 있습니다.Alternatively, clustered and nonclustered indexes can use a partition scheme across multiple filegroups. 분할을 사용하면 데이터 하위 집합을 빠르고 효율적으로 액세스하고 관리하는 동시에 전체 컬렉션의 무결성을 유지할 수 있으므로 큰 테이블이나 인덱스를 보다 쉽게 관리할 수 있습니다.Partitioning makes large tables or indexes more manageable by letting you access or manage subsets of data quickly and efficiently, while maintaining the integrity of the overall collection. 자세한 내용은 Partitioned Tables and Indexes를 참조하세요.For more information, see Partitioned Tables and Indexes. 분할을 고려할 때 인덱스를 맞춰야 하는지, 즉 본질적으로 테이블과 같은 방식으로 분할해야 하는지 또는 독립적으로 분할해야 하는지 파악합니다.When you consider partitioning, determine whether the index should be aligned, that is, partitioned in essentially the same manner as the table, or partitioned independently.

일반 인덱스 디자인 지침General Index Design Guidelines

경험이 많은 데이터베이스 관리자는 인덱스를 잘 디자인할 수 있습니다. 그러나 데이터베이스와 작업이 조금만 복잡해져도 이 태스크는 복잡하고 시간이 많이 걸리며 오류가 쉽게 발생할 수 있습니다.Experienced database administrators can design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. 데이터베이스, 쿼리 및 데이터 열의 특성을 이해하면 최적의 인덱스를 디자인할 수 있습니다.Understanding the characteristics of your database, queries, and data columns can help you design optimal indexes.

데이터베이스 고려 사항Database Considerations

인덱스를 디자인할 때 다음과 같은 데이터 베이스 지침을 고려합니다.When you design an index, consider the following database guidelines:

  • 테이블에 대한 인덱스를 많이 만들면 테이블의 데이터가 변경될 경우 인덱스도 모두 적절하게 조정되어야 하므로 INSERT, UPDATE, DELETE 및 MERGE 문의 성능이 저하될 수 있습니다.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes. 예를 들어 열이 여러 인덱스에서 사용되고 열 데이터를 수정하는 UPDATE 문을 실행하는 경우 열을 포함하는 각 인덱스뿐만 아니라 기본 테이블(힙 또는 클러스터형 인덱스)에 있는 열도 업데이트되어야 합니다.For example, if a column is used in several indexes and you execute an UPDATE statement that modifies that column's data, each index that contains that column must be updated as well as the column in the underlying base table (heap or clustered index).

    • 과도하게 업데이트되는 테이블에 대한 인덱스를 너무 많이 만들지 말고 가능한 열 수가 적은 좁은 인덱스를 만듭니다.Avoid over-indexing heavily updated tables and keep indexes narrow, that is, with as few columns as possible.

    • 많은 인덱스를 사용하면 업데이트 요구는 적지만 데이터 양이 많은 테이블의 쿼리 성능이 향상될 수 있습니다.Use many indexes to improve query performance on tables with low update requirements, but large volumes of data. 인덱스 수가 많으면 SELECT 문과 같이 데이터를 수정하지 않는 쿼리의 성능이 향상될 수 있습니다. 이는 쿼리 최적화 프로그램이 가장 빠른 액세스 방법을 결정할 때 선택할 수 있는 인덱스가 더 많기 때문입니다.Large numbers of indexes can help the performance of queries that do not modify data, such as SELECT statements, because the query optimizer has more indexes to choose from to determine the fastest access method.

  • 작은 테이블에 대한 인덱스를 만들면 비효율적일 수 있습니다. 이는 쿼리 최적화 프로그램이 간단한 테이블 스캔을 수행하는 시간보다 데이터를 검색하기 위해 인덱스를 통과하는 시간이 더 길 수 있기 때문입니다.Indexing small tables may not be optimal because it can take the query optimizer longer to traverse the index searching for data than to perform a simple table scan. 따라서 작은 테이블에 대한 인덱스는 전혀 사용되지 않을 수도 있지만 테이블의 데이터가 변경될 때마다 유지 관리되어야 합니다.Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.

  • 뷰에 집계, 테이블 조인 또는 집계와 조인의 조합이 포함되어 있으면 뷰에 대한 인덱스 성능이 크게 향상될 수 있습니다.Indexes on views can provide significant performance gains when the view contains aggregations, table joins, or a combination of aggregations and joins. 쿼리 최적화 프로그램은 뷰를 사용하기 위해 쿼리에 뷰를 명시적으로 참조할 필요가 없습니다.The view does not have to be explicitly referenced in the query for the query optimizer to use it.

  • 데이터베이스 엔진 튜닝 관리자를 사용하여 데이터베이스를 분석하고 인덱스 권장 구성을 만들 수 있습니다.Use the Database Engine Tuning Advisor to analyze your database and make index recommendations. 자세한 내용은 Database Engine Tuning Advisor을 참조하세요.For more information, see Database Engine Tuning Advisor.

쿼리 고려 사항Query Considerations

인덱스를 디자인할 때 다음과 같은 쿼리 지침을 고려합니다.When you design an index, consider the following query guidelines:

  • 쿼리의 조건자 및 조인 조건에서 자주 사용되는 열에 대해 비클러스터형 인덱스를 만듭니다.Create nonclustered indexes on the columns that are frequently used in predicates and join conditions in queries. 불필요한 열은 추가하지 마십시오.However, you should avoid adding unnecessary columns. 인덱스 열을 너무 많이 추가하면 디스크 공간 및 인덱스 유지 관리 성능이 떨어질 수 있습니다.Adding too many index columns can adversely affect disk space and index maintenance performance.

  • 인덱스를 포함하면 쿼리의 요구 사항을 만족시키는 데 필요한 모든 데이터가 인덱스 자체 내에 있기 때문에 쿼리 성능이 향상될 수 있습니다.Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. 즉, 테이블이나 클러스터형 인덱스의 데이터 페이지가 아니라 인덱스 페이지만 있으면 요청된 데이터를 검색할 수 있으므로 전체적인 디스크 I/O가 줄어듭니다.That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. 예를 들어 a , bc열에 대해 만든 복합 인덱스가 포함된 테이블에서 ab 열을 쿼리하면 지정된 데이터를 인덱스 자체에서만 검색할 수 있습니다.For example, a query of columns a and b on a table that has a composite index created on columns a, b, and c can retrieve the specified data from the index alone.

  • 여러 개의 쿼리를 사용하여 동일한 여러 행을 업데이트하는 대신 단일 문에 가능한 많은 행을 삽입하거나 수정하는 쿼리를 작성합니다.Write queries that insert or modify as many rows as possible in a single statement, instead of using multiple queries to update the same rows. 문을 하나만 사용하면 최적화된 인덱스 유지 관리가 수행될 수 있습니다.By using only one statement, optimized index maintenance could be exploited.

  • 쿼리 유형 및 쿼리에서 열이 사용되는 방법을 평가합니다.Evaluate the query type and how columns are used in the query. 예를 들어 정확히 일치하는 쿼리 유형에서 사용되는 열은 비클러스터형 또는 클러스터형 인덱스로 만들면 좋습니다.For example, a column used in an exact-match query type would be a good candidate for a nonclustered or clustered index.

열 고려 사항Column Considerations

인덱스를 디자인할 때 다음과 같은 열 지침을 고려합니다.When you design an index consider the following column guidelines:

  • 클러스터형 인덱스의 인덱스 키 길이는 짧게 유지합니다.Keep the length of the index key short for clustered indexes. 또한 클러스터형 인덱스는 고유하거나 Null이 아닌 열에 만들어지는 이점이 있습니다.Additionally, clustered indexes benefit from being created on unique or nonnull columns.

  • ntext, text, image, varchar(max), nvarchar(max)varbinary(max) 데이터 형식의 열은 인덱스 키 열로 지정할 수 없습니다.Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns. 그러나 varchar(max), nvarchar(max), varbinary(max)xml 데이터 형식은 비클러스터형 인덱스에 키가 아닌 인덱스 열로 참여할 수 있습니다.However, varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns. 자세한 내용은 이 지침에서 '포괄 열이 있는 인덱스' 섹션을 참조하십시오.For more information, see the section 'Index with Included Columns' in this guide.

  • xml 데이터 형식은 XML 인덱스의 키 열만 될 수 있습니다.An xml data type can only be a key column only in an XML index. 자세한 내용은 XML 인덱스(SQL Server)를 참조하세요.For more information, see XML Indexes (SQL Server). SQL Server 2012 SP1에서는 선택적 XML 인덱스라고 하는 새로운 유형의 XML 인덱스를 제공합니다.SQL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. 이 새 인덱스를 통해 SQL Server에서 XML로 저장된 데이터에 대해 쿼리 성능을 향상시킬 수 있어 대량의 XML 데이터 작업의 인덱싱을 훨씬 빠르게 하고 인덱스 자체의 저장 비용을 감소시켜 확장성을 향상할 수 있습니다.This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads, and improve scalability by reducing storage costs of the index itself. 자세한 내용은 SXI(선택적 XML 인덱스)를 참조하세요.For more information, see Selective XML Indexes (SXI).

  • 열이 고유한지 조사합니다.Examine column uniqueness. 동일한 열 조합에서 고유하지 않은 인덱스 대신 고유한 인덱스를 만들면 인덱스를 보다 유용하게 만드는 추가 정보가 쿼리 최적화 프로그램에 제공됩니다.A unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that makes the index more useful. 자세한 내용은 이 가이드의 고유 인덱스 디자인 지침 을 참조하십시오.For more information, see Unique Index Design Guidelines in this guide.

  • 열의 데이터 분산을 조사합니다.Examine data distribution in the column. 고유한 값이 거의 없는 열을 인덱싱하거나 그러한 열에 대해 조인을 수행하면 쿼리 실행 시간이 길어지는 경우가 많습니다.Frequently, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. 이는 데이터와 쿼리의 근본 문제이므로 일반적으로 이러한 상황을 확인하지 않고는 해결할 수 없습니다.This is a fundamental problem with the data and query, and generally cannot be resolved without identifying this situation. 예를 들어 이름이 알파벳순으로 정렬된 실제 전화 번호부에서 해당 도시의 모든 사람 이름이 Smith 또는 Jones이면 특정 사람을 신속하게 찾을 수 없습니다.For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones. 데이터 분산에 대한 자세한 내용은 Statistics를 참조하십시오.For more information about data distribution, see Statistics.

  • 잘 정의된 하위 집합이 포함된 열(예: 스파스 열, 값이 대부분 NULL인 열, 특정 범주의 값이 있는 열 및 특정 범위의 값이 있는 열)에 필터링된 인덱스를 사용해 보십시오.Consider using filtered indexes on columns that have well-defined subsets, for example sparse columns, columns with mostly NULL values, columns with categories of values, and columns with distinct ranges of values. 잘 디자인된 필터링된 인덱스는 쿼리 성능을 개선하고 인덱스 유지 관리 비용과 저장소 비용을 줄일 수 있습니다.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce storage costs.

  • 인덱스에 여러 개의 열이 포함될 경우 열 순서를 고려합니다.Consider the order of the columns if the index will contain multiple columns. 같음(=), 보다 큼(>), 보다 작음(<) 또는 BETWEEN 검색 조건의 WHERE 절에서 사용되거나 조인에 참여하는 열은 맨 앞에 배치해야 합니다.The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. 추가 열은 고유성 수준에 따라 가장 고유한 열에서 가장 고유하지 않은 열의 순서로 정렬되어야 합니다.Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

    예를 들어 인덱스가 LastName, FirstName 으로 정의되는 경우 이 인덱스는 검색 조건이 WHERE LastName = 'Smith' 또는 WHERE LastName = Smith AND FirstName LIKE 'J%'인 경우 유용합니다.For example, if the index is defined as LastName, FirstName the index will be useful when the search criterion is WHERE LastName = 'Smith' or WHERE LastName = Smith AND FirstName LIKE 'J%'. 그러나 쿼리 최적화 프로그램은 FirstName (WHERE FirstName = 'Jane')에 대해서만 검색한 쿼리에는 인덱스를 사용하지 않습니다.However, the query optimizer would not use the index for a query that searched only on FirstName (WHERE FirstName = 'Jane').

  • 계산 열의 인덱싱을 고려합니다.Consider indexing computed columns. 자세한 내용은 Indexes on Computed Columns을 참조하세요.For more information, see Indexes on Computed Columns.

인덱스 특성Index Characteristics

인덱스가 쿼리에 적합하다고 판단되면 사용자의 상황에 가장 잘 맞는 인덱스 형식을 선택할 수 있습니다.After you have determined that an index is appropriate for a query, you can select the type of index that best fits your situation. 인덱스 특성은 다음과 같습니다.Index characteristics include the following:

  • 클러스터형 인덱스와 비클러스터형 인덱스Clustered versus nonclustered

  • 고유한 인덱스와 고유하지 않은 인덱스Unique versus nonunique

  • 하나의 열로 구성된 인덱스와 여러 열로 구성된 인덱스Single column versus multicolumn

  • 인덱스의 열에서 오름차순 또는 내림차순Ascending or descending order on the columns in the index

  • 전체 테이블과 비클러스터형 인덱스 필터링Full-table versus filtered for nonclustered indexes

    FILLFACTOR 같은 옵션을 설정하면 인덱스의 초기 저장소 특성을 사용자 지정하여 성능이나 유지 관리를 최적화할 수도 있습니다.You can also customize the initial storage characteristics of the index to optimize its performance or maintenance by setting an option such as FILLFACTOR. 또한 파일 그룹이나 파티션 구성표를 사용하면 인덱스 저장소 위치를 확인하여 성능을 최적화할 수 있습니다.Also, you can determine the index storage location by using filegroups or partition schemes to optimize performance.

파일 그룹의 인덱스 배치 또는 파티션 구성표Index Placement on Filegroups or Partitions Schemes

인덱스 디자인 전략을 개발할 때는 데이터베이스와 관련된 파일 그룹에 인덱스를 배치해야 합니다.As you develop your index design strategy, you should consider the placement of the indexes on the filegroups associated with the database. 파일 그룹이나 파티션 구성표를 주의 깊게 선택하면 쿼리 성능을 향상시킬 수 있습니다.Careful selection of the filegroup or partition scheme can improve query performance.

기본적으로 인덱스는 인덱스가 생성된 기본 테이블과 동일한 파일 그룹에 저장됩니다.By default, indexes are stored in the same filegroup as the base table on which the index is created. 분할되지 않은 클러스터형 인덱스와 기본 테이블은 항상 동일한 파일 그룹 내에 위치합니다.A nonpartitioned clustered index and the base table always reside in the same filegroup. 그러나 다음과 같은 작업도 가능합니다.However, you can do the following:

  • 기본 테이블 또는 클러스터형 인덱스의 파일 그룹이 아닌 파일 그룹에 비클러스터형 인덱스를 만듭니다.Create nonclustered indexes on a filegroup other than the filegroup of the base table or clustered index.

  • 여러 파일 그룹에 걸쳐 클러스터형 및 비클러스터형 인덱스를 분할합니다.Partition clustered and nonclustered indexes to span multiple filegroups.

  • 클러스터형 인덱스를 삭제한 후 새 파일 그룹이나 파티션 구성표를 DROP INDEX 문의 MOVE TO 절에 지정하거나 CREATE INDEX 문에 DROP_EXISTING 절을 사용하여 한 파일 그룹에서 다른 파일 그룹으로 테이블을 이동합니다.Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.

    다른 파일 그룹에서 자체의 컨트롤러를 가진 여러 개의 물리적 드라이브를 사용하는 경우에는 파일 그룹에 비클러스터형 인덱스를 만들어 성능 향상을 이룰 수 있습니다.By creating the nonclustered index on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives with their own controllers. 그러면 데이터와 인덱스 정보를 여러 개의 디스크 헤드로 병렬로 읽을 수 있습니다.Data and index information can then be read in parallel by the multiple disk heads. 예를 들어 Table_A 파일 그룹의 f1Index_A 파일 그룹의 f2 가 모두 같은 쿼리에서 사용되는 경우에는 두 파일 그룹이 모두 경합 없이 충분히 활용되므로 성능 향상이 이루어질 수 있습니다.For example, if Table_A on filegroup f1 and Index_A on filegroup f2 are both being used by the same query, performance gains can be achieved because both filegroups are being fully used without contention. 하지만 Table_A 가 쿼리에서 검색되지만 Index_A 가 참조되지 않으면 f1 파일 그룹만 사용되므로However, if Table_A is scanned by the query but Index_A is not referenced, only filegroup f1 is used. 성능이 향상되지 않습니다.This creates no performance gain.

    앞으로 있을 액세스의 유형과 시점을 정확히 예측할 수는 없으므로 테이블과 인덱스를 모든 파일 그룹에 걸쳐 분산시키는 것이 더 안전합니다.Because you cannot predict what type of access will occur and when it will occur, it could be a better decision to spread your tables and indexes across all filegroups. 이렇게 하면 모든 데이터와 인덱스가 모든 디스크에 동일하게 분산되기 때문에 어떤 방식으로 데이터에 액세스하든지 모든 디스크에 액세스할 수 있습니다.This would guarantee that all disks are being accessed because all data and indexes are spread evenly across all disks, regardless of which way the data is accessed. 이는 시스템 관리자에게 더욱 간단한 방법이기도 합니다.This is also a simpler approach for system administrators.

여러 파일 그룹에 걸친 파티션Partitions Across Multiple Filegroups

여러 파일 그룹에 걸쳐 클러스터형 및 비클러스터형 인덱스를 분할하는 것을 고려할 수도 있습니다.You can also consider partitioning clustered and nonclustered indexes across multiple filegroups. 분할된 인덱스는 파티션 함수를 기준으로 가로 또는 행으로 분할됩니다.Partitioned indexes are partitioned horizontally, or by row, based on a partition function. 파티션 함수는 분할 열이라는 특정 열의 값을 기준으로 각 행이 파티션 집합에 매핑되는 방식을 정의합니다.The partition function defines how each row is mapped to a set of partitions based on the values of certain columns, called partitioning columns. 파티션 구성표는 파티션과 파일 그룹 간의 매핑을 지정합니다.A partition scheme specifies the mapping of the partitions to a set of filegroups.

인덱스를 분할하면 다음 이점이 제공됩니다.Partitioning an index can provide the following benefits:

  • 큰 인덱스를 보다 잘 관리할 수 있는 확장 가능한 시스템을 제공합니다.Provide scalable systems that make large indexes more manageable. 예를 들어 OLTP 시스템에서 파티션을 인식하며 큰 인덱스를 처리하는 응용 프로그램을 구현할 수 있습니다.OLTP systems, for example, can implement partition-aware applications that deal with large indexes.

  • 쿼리가 보다 빠르고 효율적으로 실행됩니다.Make queries run faster and more efficiently. 쿼리가 인덱스의 여러 파티션을 액세스할 때 쿼리 최적화 프로그램은 동시에 여러 파티션을 처리하고 쿼리의 영향을 받지 않는 파티션을 제외할 수 있습니다.When queries access several partitions of an index, the query optimizer can process individual partitions at the same time and exclude partitions that are not affected by the query.

    자세한 내용은 Partitioned Tables and Indexes를 참조하세요.For more information, see Partitioned Tables and Indexes.

인덱스 정렬 순서 디자인 지침Index Sort Order Design Guidelines

인덱스를 정의할 때 인덱스 키 열의 데이터를 오름차순으로 저장할지 또는 내림차순으로 저장할지 고려해야 합니다.When defining indexes, you should consider whether the data for the index key column should be stored in ascending or descending order. 오름차순이 기본값이며 이전 버전의 SQL ServerSQL Server와의 호환성을 유지합니다.Ascending is the default and maintains compatibility with earlier versions of SQL ServerSQL Server. CREATE INDEX, CREATE TABLE 및 ALTER TABLE 문의 구문은 인덱스와 제약 조건의 개별 열에서 ASC(오름차순) 및 DESC(내림차순) 키워드를 지원합니다.The syntax of the CREATE INDEX, CREATE TABLE, and ALTER TABLE statements supports the keywords ASC (ascending) and DESC (descending) on individual columns in indexes and constraints.

인덱스의 키 값 저장 순서를 지정하는 기능은 테이블을 참조하는 쿼리에 해당 인덱스에서 키 열의 다른 방향을 지정하는 ORDER BY 절이 포함된 경우에 유용합니다.Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. 이러한 경우 쿼리 계획에서 SORT 연산자를 사용할 필요가 없어지므로 쿼리의 효율성이 향상됩니다.In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient. 예를 들어 Adventure Works CyclesAdventure Works Cycles 구매 부서의 구매자가 공급업체로부터 구입하는 제품 품질을 평가해야 합니다.For example, the buyers in the Adventure Works CyclesAdventure Works Cycles purchasing department have to evaluate the quality of products they purchase from vendors. 구매자는 대부분 해당 공급업체에서 보낸 제품 중 거부율이 높은 제품을 찾으려고 합니다.The buyers are most interested in finding products sent by these vendors with a high rejection rate. 다음 쿼리와 같이 이 조건에 맞는 데이터를 검색하려면 RejectedQty 테이블의 Purchasing.PurchaseOrderDetail 열을 내림차순(큰 데이터부터 작은 데이터 순서)으로 정렬하고 ProductID 열은 오름차순(작은 데이터부터 큰 데이터 순서)으로 정렬해야 합니다.As shown in the following query, retrieving the data to meet this criteria requires the RejectedQty column in the Purchasing.PurchaseOrderDetail table to be sorted in descending order (large to small) and the ProductID column to be sorted in ascending order (small to large).

SELECT RejectedQty, ((RejectedQty/OrderQty)*100) AS RejectionRate,  
    ProductID, DueDate  
FROM Purchasing.PurchaseOrderDetail  
ORDER BY RejectedQty DESC, ProductID ASC;  

이 쿼리에 대한 다음 실행 계획은 쿼리 최적화 프로그램에서 SORT 연산자를 사용하여 ORDER BY 절로 지정된 순서로 결과 집합을 반환했음을 보여 줍니다.The following execution plan for this query shows that the query optimizer used a SORT operator to return the result set in the order specified by the ORDER BY clause.

IndexSort1

쿼리의 ORDER BY 절에 있는 것과 일치하는 키 열로 인덱스가 생성되는 경우 쿼리 계획에서 SORT 연산자를 제거하여 쿼리 계획의 효율성을 향상시킬 수 있습니다.If an index is created with key columns that match those in the ORDER BY clause in the query, the SORT operator can be eliminated in the query plan and the query plan is more efficient.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty  
ON Purchasing.PurchaseOrderDetail  
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);  

쿼리가 다시 실행된 후 다음 실행 계획은 SORT 연산자가 제거되어 새로 생성된 비클러스터형 인덱스가 사용됨을 보여 줍니다.After the query is executed again, the following execution plan shows that the SORT operator has been eliminated and the newly created nonclustered index is used.

InsertSort2

데이터베이스 엔진Database Engine 은 어느 방향으로든 동일하게 효율적으로 이동할 수 있습니다.The 데이터베이스 엔진Database Engine can move equally efficiently in either direction. ORDER BY 절에 있는 열의 정렬 방향이 반대가 되는 쿼리에도 (RejectedQty DESC, ProductID ASC) 로 정의된 인덱스를 사용할 수 있습니다.An index defined as (RejectedQty DESC, ProductID ASC) can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed. 예를 들어 ORDER BY 절 ORDER BY RejectedQty ASC, ProductID DESC 를 포함하는 쿼리에 인덱스를 사용할 수 있습니다.For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

정렬 순서는 키 열에 대해서만 지정할 수 있습니다.Sort order can be specified only for key columns. sys.index_columns 카탈로그 뷰와 INDEXKEY_PROPERTY 함수는 인덱스 열이 오름차순으로 저장되는지 또는 내림차순으로 저장되는지 보고합니다.The sys.index_columns catalog view and the INDEXKEY_PROPERTY function report whether an index column is stored in ascending or descending order.

클러스터형 인덱스 디자인 지침Clustered Index Design Guidelines

클러스터형 인덱스는 그 키 값에 기반하여 테이블에 데이터 행을 정렬하고 저장합니다.Clustered indexes sort and store the data rows in the table based on their key values. 데이터 행은 자체적으로 하나의 순서로만 정렬될 수 있으므로 테이블당 클러스터형 인덱스가 하나만 있을 수 있습니다.There can only be one clustered index per table, because the data rows themselves can only be sorted in one order. 일부 예외를 제외하고는 각 테이블에서 클러스터형 인덱스가 정의된 열은 다음과 같은 특성을 가져야 합니다.With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following:

  • 자주 사용하는 쿼리에 대해 사용할 수 있습니다.Can be used for frequently used queries.

  • 높은 수준의 고유성을 제공합니다.Provide a high degree of uniqueness.

    참고

    PRIMARY KEY 제약 조건을 만들 때는 열에 고유 인덱스가 자동으로 생성됩니다.When you create a PRIMARY KEY constraint, a unique index on the column, or columns, is automatically created. 기본적으로 이 인덱스는 클러스터링되지만 제약 조건을 만들 때 비클러스터형 인덱스로 지정할 수 있습니다.By default, this index is clustered; however, you can specify a nonclustered index when you create the constraint.

  • 범위 쿼리에서 사용할 수 있습니다.Can be used in range queries.

    클러스터형 인덱스가 UNIQUE 속성으로 생성되지 않는 경우 데이터베이스 엔진Database Engine 은 4바이트 고유 식별자 열을 테이블에 자동으로 추가합니다.If the clustered index is not created with the UNIQUE property, the 데이터베이스 엔진Database Engine automatically adds a 4-byte uniqueifier column to the table. 필요한 경우 데이터베이스 엔진Database Engine 은 고유 식별자 값을 자동으로 행에 추가하여 각 키를 고유하게 만듭니다.When it is required, the 데이터베이스 엔진Database Engine automatically adds a uniqueifier value to a row to make each key unique. 이 열과 해당 값은 내부적으로 사용되며 사용자는 보거나 액세스할 수 없습니다.This column and its values are used internally and cannot be seen or accessed by users.

클러스터형 인덱스 아키텍처Clustered Index Architecture

SQL ServerSQL Server에서 인덱스는 B-트리로 구성됩니다.In SQL ServerSQL Server, indexes are organized as B-trees. 인덱스 B-트리의 각 페이지를 인덱스 노드라고 합니다.Each page in an index B-tree is called an index node. B-트리 맨 위 노드를 루트 노드라고 합니다.The top node of the B-tree is called the root node. 인덱스의 최하위 노드를 리프 노드라고 합니다.The bottom nodes in the index are called the leaf nodes. 루트 노드와 리프 노드 사이의 인덱스 수준을 통틀어 중간 수준이라고 합니다.Any index levels between the root and the leaf nodes are collectively known as intermediate levels. 클러스터형 인덱스의 리프 노드에는 기본 테이블의 데이터 페이지가 있습니다.In a clustered index, the leaf nodes contain the data pages of the underlying table. 루트 노드와 중간 수준 노드에는 인덱스 행을 포함하는 인덱스 페이지가 있습니다.The root and intermediate level nodes contain index pages holding index rows. 각 인덱스 행에는 키 값과 함께 B-트리의 중간 수준 페이지에 대한 포인터나 인덱스 리프 수준의 데이터 행에 대한 포인터가 있습니다.Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. 인덱스의 각 수준의 페이지는 이중 연결 목록에서 연결됩니다.The pages in each level of the index are linked in a doubly-linked list.

클러스터형 인덱스에는 인덱스에서 사용하는 각 파티션에 대해 index_id가 1인 sys.partitions 행이 하나 있습니다.Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. 기본적으로 클러스터형 인덱스는 단일 파티션을 사용합니다.By default, a clustered index has a single partition. 클러스터형 인덱스가 다중 파티션을 사용하는 경우 각 파티션은 해당 특정 파티션에 대한 데이터를 포함하는 B-트리 구조를 갖습니다.When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. 예를 들어 클러스터형 인덱스가 4개의 파티션을 사용하면 파티션마다 하나씩 총 4개의 B-트리 구조가 있습니다.For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

클러스터형 인덱스의 데이터 형식에 따라 각 클러스터형 인덱스 구조에는 특정 파티션에 대한 데이터를 저장하고 관리하는 할당 단위가 하나 이상 있습니다.Depending on the data types in the clustered index, each clustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. 최소한 각 클러스터형 인덱스에는 파티션당 하나의 IN_ROW_DATA 할당 단위가 있습니다.At a minimum, each clustered index will have one IN_ROW_DATA allocation unit per partition. 또한 클러스터형 인덱스에는 LOB(Large Object) 열이 포함된 경우 파티션당 하나의 LOB_DATA 할당 단위가 있습니다.The clustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns. 8,060바이트 행 크기 제한을 초과하는 가변 길이 열이 포함된 경우 파티션당 하나의 ROW_OVERFLOW_DATA 할당 단위도 있습니다.It will also have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

데이터 체인의 페이지와 페이지 행은 클러스터형 인덱스 키의 값에 따라 정렬됩니다.The pages in the data chain and the rows in them are ordered on the value of the clustered index key. 삽입된 행의 키 값이 기존 행 간의 순서대로 정렬될 때 모든 삽입이 끝납니다.All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

이 그림에서는 단일 파티션의 클러스터형 인덱스 구조를 보여 줍니다.This illustration shows the structure of a clustered index in a single partition.

bokind2

쿼리 고려 사항Query Considerations

클러스터형 인덱스를 만들기 전에 데이터가 액세스되는 방식을 이해해야 합니다.Before you create clustered indexes, understand how your data will be accessed. 다음을 수행하는 쿼리에는 클러스터형 인덱스를 사용하십시오.Consider using a clustered index for queries that do the following:

  • BETWEEN, >, >=, < 및 <= 등의 연산자를 사용하여 일정한 범위의 값을 반환합니다.Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

    클러스터형 인덱스를 사용하여 첫 번째 값을 가진 행을 찾으면 다음의 인덱싱된 값은 반드시 물리적으로 인접해 있습니다.After the row with the first value is found by using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. 예를 들어 쿼리가 일정한 범위의 판매 주문 번호 간의 레코드를 검색하는 경우 SalesOrderNumber 열의 클러스터형 인덱스는 시작 판매 주문 번호가 포함된 행을 빠르게 찾은 후 마지막 판매 주문 번호에 도달할 때까지 테이블의 모든 연속된 행을 검색합니다.For example, if a query retrieves records between a range of sales order numbers, a clustered index on the column SalesOrderNumber can quickly locate the row that contains the starting sales order number, and then retrieve all successive rows in the table until the last sales order number is reached.

  • 큰 결과 집합을 반환합니다.Return large result sets.

  • JOIN 절을 사용하며 일반적으로 외래 키 열입니다.Use JOIN clauses; typically these are foreign key columns.

  • ORDER BY 또는 GROUP BY 절을 사용합니다.Use ORDER BY, or GROUP BY clauses.

    ORDER BY 또는 GROUP BY 절에 지정된 열에서 인덱스를 만들면 행이 이미 정렬되어 있기 때문에 데이터베이스 엔진Database Engine 이 데이터를 정렬할 필요가 없습니다.An index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the 데이터베이스 엔진Database Engine to sort the data, because the rows are already sorted. 따라서 쿼리 성능도 향상됩니다.This improves query performance.

열 고려 사항Column Considerations

특별한 상황이 아니라면 클러스터형 인덱스 키를 가능한 적은 열로 정의해야 합니다.Generally, you should define the clustered index key with as few columns as possible. 다음 중 하나 이상의 특성이 있는 열을 고려하십시오.Consider columns that have one or more of the following attributes:

  • 고유한 열이거나 고유한 값이 많음Are unique or contain many distinct values

    예를 들어 직원 ID는 직원을 고유하게 나타냅니다.For example, an employee ID uniquely identifies employees. EmployeeID 열에 클러스터형 인덱스나 PRIMARY KEY 제약 조건을 적용하면 직원 ID 번호로 직원 정보를 검색하는 쿼리 성능이 향상됩니다.A clustered index or PRIMARY KEY constraint on the EmployeeID column would improve the performance of queries that search for employee information based on the employee ID number. 또는 LastName, FirstName, MiddleName 에 클러스터형 인덱스를 생성할 수 있는데 이는 직원 레코드가 이러한 방법으로 자주 그룹화 및 쿼리되기 때문이며, 이러한 열을 결합하더라도 높은 수준의 고유성을 제공합니다.Alternatively, a clustered index could be created on LastName, FirstName, MiddleName because employee records are frequently grouped and queried in this way, and the combination of these columns would still provide a high degree of difference.

  • 순차적인 액세스Are accessed sequentially

    예를 들어 Production.Product 데이터베이스에서 제품 ID는 AdventureWorks2012AdventureWorks2012 테이블의 제품을 고유하게 나타냅니다.For example, a product ID uniquely identifies products in the Production.Product table in the AdventureWorks2012AdventureWorks2012 database. WHERE ProductID BETWEEN 980 and 999와 같이 순차 검색이 지정된 쿼리라면 ProductID에 클러스터형 인덱스를 사용하는 것이 좋습니다.Queries in which a sequential search is specified, such as WHERE ProductID BETWEEN 980 and 999, would benefit from a clustered index on ProductID. 이는 해당 키 열에 행이 정렬되어 저장되기 때문입니다.This is because the rows would be stored in sorted order on that key column.

  • IDENTITY로 정의됩니다.Defined as IDENTITY.

  • 테이블에서 검색된 데이터를 정렬하는 데 자주 사용됨Used frequently to sort the data retrieved from a table.

    해당 열의 테이블을 클러스터링, 즉 물리적으로 정렬하면 열을 쿼리할 때마다 정렬 작업 비용을 줄일 수 있습니다.It can be a good idea to cluster, that is physically sort, the table on that column to save the cost of a sort operation every time the column is queried.

    다음과 같은 특성에는 클러스터형 인덱스를 사용하지 않는 것이 좋습니다.Clustered indexes are not a good choice for the following attributes:

  • 자주 변경되는 열Columns that undergo frequent changes

    데이터베이스 엔진Database Engine 이 행의 데이터 값을 물리적인 순서로 보관해야 하기 때문에 열이 자주 변경되면 전체 행이 이동됩니다.This causes in the whole row to move, because the 데이터베이스 엔진Database Engine must keep the data values of a row in physical order. 다음은 일반적으로 데이터가 불안정한 대량 트랜잭션 처리 시스템에서 고려해야 할 사항입니다.This is an important consideration in high-volume transaction processing systems in which data is typically volatile.

  • 다양한 키Wide keys

    다양한 키는 여러 열 또는 크기가 큰 여러 열이 합쳐진 것입니다.Wide keys are a composite of several columns or several large-size columns. 클러스터형 인덱스로 구한 키 값은 모든 비클러스터형 인덱스에 의해 조회 키로 사용됩니다.The key values from the clustered index are used by all nonclustered indexes as lookup keys. 비클러스터형 인덱스 항목에는 클러스터링 키와 함께 해당 비클러스터형 인덱스에 대해 정의된 키 열도 포함되기 때문에 동일한 테이블에 정의된 비클러스터형 인덱스가 훨씬 큽니다.Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.

비클러스터형 인덱스 디자인 지침Nonclustered Index Design Guidelines

비클러스터형 인덱스에는 테이블 데이터의 저장 위치를 가리키는 행 로케이터와 인덱스 키 값이 있습니다.A nonclustered index contains the index key values and row locators that point to the storage location of the table data. 테이블 또는 인덱싱된 뷰에 비클러스터형 인덱스를 여러 개 만들 수 있습니다.You can create multiple nonclustered indexes on a table or indexed view. 일반적으로 클러스터형 인덱스를 적용할 수 없고 자주 사용되는 쿼리의 성능을 개선하도록 비클러스터형 인덱스를 디자인해야 합니다.Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.

책에서 색인을 사용하는 것처럼 쿼리 최적화 프로그램은 비클러스터형 인덱스를 검색하여 테이블에서 데이터 값의 위치를 찾고 해당 위치에서 데이터를 직접 검색하는 방식으로 데이터 값을 검색합니다.Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. 비클러스터형 인덱스에는 쿼리에서 검색하는 데이터 값에 대한 테이블의 정확한 위치를 설명하는 항목이 있기 때문에 정확히 일치하는 값을 찾는 쿼리에는 비클러스터형 인덱스가 가장 적합합니다.This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. 예를 들어 HumanResources. Employee 테이블에서 특정 관리자에게 보고하는 모든 직원을 쿼리하면 쿼리 최적화 프로그램은 IX_Employee_ManagerID가 키 열인 비클러스터형 인덱스 ManagerID 를 사용할 수 있습니다.For example, to query the HumanResources. Employee table for all employees that report to a specific manager, the query optimizer might use the nonclustered index IX_Employee_ManagerID; this has ManagerID as its key column. 쿼리 최적화 프로그램은 인덱스에서 지정된 ManagerID와 일치하는 모든 항목을 빠르게 찾을 수 있습니다.The query optimizer can quickly find all entries in the index that match the specified ManagerID. 각 인덱스 항목은 해당 데이터를 찾을 수 있는 클러스터형 인덱스나 테이블의 정확한 페이지와 행을 가리킵니다.Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. 쿼리 최적화 프로그램은 인덱스에서 모든 항목을 찾은 후 정확한 페이지와 행으로 직접 이동하여 데이터를 검색할 수 있습니다.After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.

비클러스터형 인덱스 아키텍처Nonclustered Index Architecture

비클러스터형 인덱스는 다음의 두 가지 주요 차이점을 제외하고 클러스터형 인덱스와 동일한 B-트리 구조를 갖습니다.Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:

  • 기본 테이블의 데이터 행은 비클러스터형 키 기반의 순서대로 정렬되거나 저장되지 않습니다.The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.

  • 비클러스터형 인덱스의 리프 계층은 데이터 페이지 대신 인덱스 페이지로 구성됩니다.The leaf layer of a nonclustered index is made up of index pages instead of data pages.

    비클러스터형 인덱스 행의 행 로케이터는 다음과 같이 행에 대한 포인터이거나 행에 대한 클러스터형 인덱스 키입니다.The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

  • 테이블이 힙인 경우, 즉 테이블에 클러스터형 인덱스가 없는 경우 행 로케이터는 행에 대한 포인터입니다.If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. 포인터는 파일 ID, 페이지 번호 및 페이지의 행 번호로 구성됩니다.The pointer is built from the file identifier (ID), page number, and number of the row on the page. 전체 포인터를 RID(행 ID)라고 합니다.The whole pointer is known as a Row ID (RID).

  • 테이블에 클러스터형 인덱스가 있거나 인덱스가 인덱싱된 뷰에 있는 경우 행 로케이터는 행에 대한 클러스터형 인덱스 키입니다.If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row.

    비클러스터형 인덱스에는 인덱스에서 사용하는 각 파티션에 대해 index_id >1인 sys.partitions 행이 하나 있습니다.Nonclustered indexes have one row in sys.partitions with index_id >1 for each partition used by the index. 기본적으로 비클러스터형 인덱스는 단일 파티션을 사용합니다.By default, a nonclustered index has a single partition. 비클러스터형 인덱스가 다중 파티션을 사용하는 경우 각 파티션은 해당 특정 파티션에 대한 인덱스 행을 포함하는 B-트리 구조를 갖습니다.When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition. 예를 들어 비클러스터형 인덱스가 4개의 파티션을 사용하면 파티션마다 하나씩 총 4개의 B-트리 구조가 있습니다.For example, if a nonclustered index has four partitions, there are four B-tree structures, with one in each partition.

    비클러스터형 인덱스의 데이터 형식에 따라 각 비클러스터형 인덱스 구조에는 특정 파티션에 대한 데이터를 저장하고 관리하는 할당 단위가 하나 이상 있습니다.Depending on the data types in the nonclustered index, each nonclustered index structure will have one or more allocation units in which to store and manage the data for a specific partition. 최소한 각 비클러스터형 인덱스에는 인덱스 B-트리 페이지를 저장하는 파티션당 하나의 IN_ROW_DATA 할당 단위가 있습니다.At a minimum, each nonclustered index will have one IN_ROW_DATA allocation unit per partition that stores the index B-tree pages. 또한 비클러스터형 인덱스에는 LOB(Large Object) 열이 포함된 경우 파티션당 하나의 LOB_DATA 할당 단위가 있습니다.The nonclustered index will also have one LOB_DATA allocation unit per partition if it contains large object (LOB) columns . 8,060바이트 행 크기 제한을 초과하는 가변 길이 열이 포함된 경우 파티션당 하나의 ROW_OVERFLOW_DATA 할당 단위도 있습니다.Additionally, it will have one ROW_OVERFLOW_DATA allocation unit per partition if it contains variable length columns that exceed the 8,060 byte row size limit.

    다음 그림에서는 단일 파티션의 비클러스터형 인덱스 구조를 보여 줍니다.The following illustration shows the structure of a nonclustered index in a single partition.

bokind1a

데이터베이스 고려 사항Database Considerations

비클러스터형 인덱스를 디자인할 때 데이터베이스의 특징을 고려해야 합니다.Consider the characteristics of the database when designing nonclustered indexes.

  • 자주 업데이트하지는 않지만 데이터가 많은 데이터베이스나 테이블의 경우 비클러스터형 인덱스가 많으면 쿼리 성능이 향상될 수 있습니다.Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. 전체 테이블 비클러스터형 인덱스에 비해 인덱스 유지 관리 비용을 줄이고, 인덱스 저장소 비용을 줄이고, 쿼리 성능을 향상시킬 수 있도록 데이터의 잘 정의된 하위 집합에 대한 필터링된 인덱스를 만듭니다.Consider creating filtered indexes for well-defined subsets of data to improve query performance, reduce index storage costs, and reduce index maintenance costs compared with full-table nonclustered indexes.

    읽기 전용 데이터를 주로 포함하는 의사 결정 지원 시스템 응용 프로그램 및 데이터베이스의 경우에도 비클러스터형 인덱스가 많으면 유용할 수 있습니다.Decision Support System applications and databases that contain primarily read-only data can benefit from many nonclustered indexes. 쿼리 최적화 프로그램에서 가장 빠른 액세스 방법을 결정할 때 선택할 수 있는 인덱스가 늘어나며 데이터베이스가 자주 업데이트되지 않으므로 인덱스 유지 관리로 인해 성능이 저하되지 않습니다.The query optimizer has more indexes to choose from to determine the fastest access method, and the low update characteristics of the database mean index maintenance will not impede performance.

  • 자주 업데이트되는 테이블을 포함하는 온라인 트랜잭션 처리 응용 프로그램 및 데이터베이스의 경우에는 너무 많이 인덱싱하지 않아야 합니다.Online Transaction Processing applications and databases that contain heavily updated tables should avoid over-indexing. 또한 인덱스는 가능한 적은 수의 열을 포함하는 좁은 인덱스여야 합니다.Additionally, indexes should be narrow, that is, with as few columns as possible.

    테이블에 대한 인덱스를 많이 만들면 테이블의 데이터가 변경될 경우 인덱스도 모두 적절하게 조정되어야 하므로 INSERT, UPDATE, DELETE 및 MERGE 문의 성능이 저하될 수 있습니다.Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes.

쿼리 고려 사항Query Considerations

비클러스터형 인덱스를 만들기 전에 데이터가 액세스되는 방법을 이해해야 합니다.Before you create nonclustered indexes, you should understand how your data will be accessed. 다음과 같은 특성이 있는 쿼리의 경우 비클러스터형 인덱스 사용을 고려하십시오.Consider using a nonclustered index for queries that have the following attributes:

  • JOIN 또는 GROUP BY 절을 사용하는 쿼리Use JOIN or GROUP BY clauses.

    조인 및 그룹화 작업과 관련된 열에는 비클러스터형 인덱스를 여러 개 만들고 외래 키 열에는 클러스터형 인덱스를 만듭니다.Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

  • 큰 결과 집합을 반환하지 않는 쿼리Queries that do not return large result sets.

    대형 테이블에서 행의 잘 정의된 하위 집합을 반환하는 쿼리를 처리하는 필터링된 인덱스를 만듭니다.Create filtered indexes to cover queries that return a well-defined subset of rows from a large table.

  • WHERE 절과 같이 정확히 일치하는 값을 반환하는 쿼리의 검색 조건에 자주 사용되는 열을 포함하는 쿼리Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches.

열 고려 사항Column Considerations

비클러스터형 인덱스를 만들 때 열에 대한 다음 특성을 고려하십시오.Consider columns that have one or more of these attributes:

  • 쿼리에 사용되는 열 모두 포함Cover the query.

    인덱스에 쿼리의 모든 열이 포함되어 있으면 성능이 향상됩니다.Performance gains are achieved when the index contains all columns in the query. 쿼리 최적화 프로그램이 인덱스에서 모든 열 값을 찾을 수 있으므로 테이블이나 클러스터형 인덱스에 액세스하지 않아 디스크 I/O 작업이 줄어듭니다.The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. 넓은 인덱스 키를 만드는 대신 포괄 열이 있는 인덱스를 사용하여 인덱스 범위에 해당하는 열을 늘립니다.Use index with included columns to add covering columns instead of creating a wide index key.

    테이블에 클러스터형 인덱스가 있으면 클러스터형 인덱스에 정의된 열이 자동으로 테이블의 각 비클러스터형 인덱스 끝에 추가됩니다.If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. 이를 통해 비클러스터형 인덱스 정의에 클러스터형 인덱스 열을 지정하지 않고도 쿼리에 사용되는 열이 모두 포함될 수 있습니다.This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. 예를 들어 테이블 C열에 대한 클러스터형 인덱스가 있을 경우 BA 열에 대한 비클러스터형 인덱스의 키 값 열은 B, A, 및 C가 됩니다.For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.

  • 클러스터형 인덱스가 다른 열에 사용되는 경우 성과 이름의 조합 같은 고유 값 많이 포함Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

    1과 0만으로 구성되는 경우와 같이 고유 값이 매우 적으면 대개 테이블 검색이 더 효율적이므로 대부분의 쿼리에서 인덱스를 사용하지 않습니다.If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is generally more efficient. 이 데이터 형식의 경우 적은 수의 행에서만 발생하는 고유 값에 필터링된 인덱스를 만듭니다.For this type of data, consider creating a filtered index on a distinct value that only occurs in a small number of rows. 예를 들어 대부분의 값이 0인 경우 쿼리 최적화 프로그램에서는 1을 포함하는 데이터 행에 대해 필터링된 인덱스를 사용합니다.For example, if most of the values are 0, the query optimizer might use a filtered index for the data rows that contain 1.

포괄 열을 사용하여 비클러스터형 인덱스 확장Use Included Columns to Extend Nonclustered Indexes

비클러스터형 인덱스의 리프 수준에 키가 아닌 열을 추가하여 비클러스터형 인덱스의 기능을 확장할 수 있습니다.You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. 키가 아닌 열을 포함하여 여러 쿼리를 처리하는 비클러스터형 인덱스를 만들 수 있습니다.By including nonkey columns, you can create nonclustered indexes that cover more queries. 이는 키가 아닌 열에 다음과 같은 장점이 있기 때문입니다.This is because the nonkey columns have the following benefits:

  • 키가 아닌 열은 인덱스 키 열로 사용할 수 없는 데이터 형식입니다.They can be data types not allowed as index key columns.

  • 인덱스 키 열의 수 또는 인덱스 키 크기를 계산할 때 데이터베이스 엔진Database Engine 은 키가 아닌 열을 고려하지 않습니다.They are not considered by the 데이터베이스 엔진Database Engine when calculating the number of index key columns or index key size.

    쿼리의 모든 열이 키 또는 키가 아닌 열로 인덱스에 포함될 때 키가 아닌 포괄 열이 있는 인덱스는 쿼리 성능을 상당히 향상시킬 수 있습니다.An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. 성능이 향상되는 이유는 쿼리 최적화 프로그램이 테이블 또는 클러스터형 인덱스 데이터에 액세스하지 않고 인덱스 내에서 모든 열 값을 찾을 수 있으므로 디스크 I/O 작업을 줄어들기 때문입니다.Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

참고

인덱스에 쿼리가 참조하는 모든 열이 들어 있으면 일반적으로 이 인덱스는 쿼리를 포함한다고 합니다.When an index contains all the columns referenced by the query it is typically referred to as covering the query.

키 열은 모든 수준의 인덱스에 저장되지만 키가 아닌 열은 리프 수준에만 저장됩니다.While key columns are stored at all levels of the index, nonkey columns are stored only at the leaf level.

크기 제한을 피하기 위한 포괄 열 사용Using Included Columns to Avoid Size Limits

비클러스터형 인덱스에 키가 아닌 열을 포함시키면 현재 인덱스 크기 제한인 최대 16개의 키 열 및 최대 900바이트의 인덱스 키 크기가 초과되는 것을 피할 수 있습니다.You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. 인덱스 키 열의 수 또는 인덱스 키 크기를 계산할 때 데이터베이스 엔진Database Engine 은 키가 아닌 열은 계산하지 않습니다.The 데이터베이스 엔진Database Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

예를 들어 Document 테이블에서 다음 열을 인덱싱하려는 경우를 가정해 봅니다.For example, assume that you want to index the following columns in the Document table:

Title nvarchar(50)

Revision nchar(5)

FileName nvarchar(400)

ncharnvarchar 데이터 형식은 각 문자에 대해 2바이트가 필요하므로 위의 3열이 포함된 인덱스는 900바이트의 크기 제한을 10바이트 초과하게 됩니다(455 * 2).Because the nchar and nvarchar data types require 2 bytes for each character, an index that contains these three columns would exceed the 900 byte size limitation by 10 bytes (455 * 2). INCLUDE 문의 CREATE INDEX 절을 사용하면 인덱스 키는 (Title, Revision)으로 정의되고 FileName 은 키가 아닌 열로 정의됩니다.By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (Title, Revision) and FileName defined as a nonkey column. 이 방법으로 인덱스 키 크기는 110바이트(55 * 2)가 되며 인덱스는 필요한 모든 열을 포함합니다.In this way, the index key size would be 110 bytes (55 * 2), and the index would still contain all the required columns. 다음 문은 이와 같은 인덱스를 만듭니다.The following statement creates such an index.

CREATE INDEX IX_Document_Title   
ON Production.Document (Title, Revision)   
INCLUDE (FileName);   
포괄 열이 있는 인덱스 지침Index with Included Columns Guidelines

포괄 열이 있는 비클러스터형 인덱스를 디자인하는 경우 다음 지침을 고려합니다.When you design nonclustered indexes with included columns consider the following guidelines:

  • 키가 아닌 열은 CREATE INDEX 문의 INCLUDE 절에서 정의됩니다.Nonkey columns are defined in the INCLUDE clause of the CREATE INDEX statement.

  • 키가 아닌 열은 테이블 또는 인덱싱된 뷰의 비클러스터형 인덱스에서만 정의될 수 있습니다.Nonkey columns can only be defined on nonclustered indexes on tables or indexed views.

  • text, ntextimage를 제외한 모든 데이터 형식을 사용할 수 있습니다.All data types are allowed except text, ntext, and image.

  • 결정적이면서 정확하거나 정확하지 않은 계산 열은 포괄 열이 될 수 있습니다.Computed columns that are deterministic and either precise or imprecise can be included columns. 자세한 내용은 Indexes on Computed Columns을 참조하세요.For more information, see Indexes on Computed Columns.

  • 키 열과 마찬가지로 image, ntexttext 데이터 형식에서 파생된 계산 열은 계산 열 데이터 형식이 키가 아닌 인덱스 열로 허용되는 동안 키가 아닌 포괄 열이 될 수 있습니다.As with key columns, computed columns derived from image, ntext, and text data types can be nonkey (included) columns as long as the computed column data type is allowed as a nonkey index column.

  • 열 이름은 INCLUDE 목록 및 키 열 목록 모두에서 지정될 수 없습니다.Column names cannot be specified in both the INCLUDE list and in the key column list.

  • 열 이름은 INCLUDE 목록에서 반복될 수 없습니다.Column names cannot be repeated in the INCLUDE list.

열 크기 지침Column Size Guidelines
  • 적어도 하나의 키 열을 정의해야 합니다.At least one key column must be defined. 키가 아닌 열의 최대 수는 1023입니다.The maximum number of nonkey columns is 1023 columns. 이 값은 테이블 열의 최대 수보다 1이 적습니다.This is the maximum number of table columns minus 1.

  • 키가 아닌 열을 제외한 인덱스 키 열은 최대 16개의 키 열 및 최대 900바이트의 전체 인덱스 키 크기인 기존 인덱스 크기 제한을 따라야 합니다.Index key columns, excluding nonkeys, must follow the existing index size restrictions of 16 key columns maximum, and a total index key size of 900 bytes.

  • 키가 아닌 모든 열의 전체 크기는 INCLUDE 절에 지정된 열의 크기에 의해서만 제한됩니다. 예를 들어 varchar(max) 열은 2GB로 제한됩니다.The total size of all nonkey columns is limited only by the size of the columns specified in the INCLUDE clause; for example, varchar(max) columns are limited to 2 GB.

열 수정 지침Column Modification Guidelines

포괄 열로 정의된 테이블 열을 수정하는 경우 다음 제한 사항이 적용됩니다.When you modify a table column that has been defined as an included column, the following restrictions apply:

  • 인덱스를 먼저 삭제하지 않으면 키가 아닌 열을 테이블에서 삭제할 수 없습니다.Nonkey columns cannot be dropped from the table unless the index is dropped first.

  • 다음 경우를 제외하고 키가 아닌 열은 변경할 수 없습니다.Nonkey columns cannot be changed, except to do the following:

    • 열의 Null 허용 여부를 NOT NULL에서 NULL로 변경합니다.Change the nullability of the column from NOT NULL to NULL.

    • varchar, nvarchar또는 varbinary 열의 길이를 늘립니다.Increase the length of varchar, nvarchar, or varbinary columns.

      참고

      이러한 열 수정 제한도 인덱스 키 열에 적용됩니다.These column modification restrictions also apply to index key columns.

디자인 권장 구성Design Recommendations

검색 및 조회에 사용된 열만 키 열이 되도록 인덱스 키 크기가 큰 비클러스터형 인덱스를 다시 디자인합니다.Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. 쿼리를 포함한 다른 모든 열을 키가 아닌 포괄 열로 만듭니다.Make all other columns that cover the query included nonkey columns. 이 방법을 통해 쿼리를 포함하는 데 필요한 모든 열을 가지게 되지만 인덱스 키 자체는 작으며 효과적입니다.In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

예를 들어 다음 쿼리를 포함하는 인덱스를 디자인하려는 경우를 가정해 봅니다.For example, assume that you want to design an index to cover the following query.

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode  
FROM Person.Address  
WHERE PostalCode BETWEEN N'98000' and N'99999';  

쿼리를 포함하려면 인덱스에서 각 열을 정의해야 합니다.To cover the query, each column must be defined in the index. 모든 열을 키 열로 정의할 수 있지만 키 크기는 334바이트가 됩니다.Although you could define all columns as key columns, the key size would be 334 bytes. 실제 검색 조건으로 사용된 유일한 열은 길이가 30바이트인 PostalCode 열이므로 더 나은 인덱스 디자인은 PostalCode 를 키 열로 정의하고 다른 모든 열을 키가 아닌 열로 포함시킵니다.Because the only column actually used as search criteria is the PostalCode column, having a length of 30 bytes, a better index design would define PostalCode as the key column and include all other columns as nonkey columns.

다음 문은 포괄 열이 있는 인덱스를 만들어 쿼리를 포함합니다.The following statement creates an index with included columns to cover the query.

CREATE INDEX IX_Address_PostalCode  
ON Person.Address (PostalCode)  
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
성능 고려 사항Performance Considerations

불필요한 열은 추가하지 마십시오.Avoid adding unnecessary columns. 키 또는 키가 아닌 인덱스 열을 너무 많이 추가하면 다음과 같이 성능에 영향을 줍니다.Adding too many index columns, key or nonkey, can have the following performance implications:

  • 인덱스 행을 줄이면 한 페이지에 표시됩니다.Fewer index rows will fit on a page. 이로 인해 I/O가 증가되고 캐시 효율성이 떨어집니다.This could create I/O increases and reduced cache efficiency.

  • 인덱스를 저장할 디스크 공간이 더 필요합니다.More disk space will be required to store the index. 특히 varchar(max), nvarchar(max), varbinary(max)또는 xml 데이터 형식을 키가 아닌 인덱스 열로 추가하면 상당히 많은 디스크 공간이 필요할 수 있습니다.In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. 이는 열 값이 인덱스 리프 수준으로 복사되기 때문입니다.This is because the column values are copied into the index leaf level. 따라서 열 값은 인덱스 및 기본 테이블 모두에 존재합니다.Therefore, they reside in both the index and the base table.

  • 인덱스 유지 관리를 위해 기본 테이블 또는 인덱싱된 뷰에 대해 수정, 삽입, 업데이트 또는 삭제하는 시간이 늘어납니다.Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.

    데이터를 수정해야 할지, 디스크 공간을 추가할지 결정할 때 성능에 미치는 영향과 쿼리 성능 향상 중 어느 것이 더 중요한지를 결정해야 합니다.You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements.

고유 인덱스 디자인 지침Unique Index Design Guidelines

고유 인덱스는 인덱스 키에 중복 값을 포함할 수 없으므로 테이블의 모든 행이 고유합니다.A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. 고유 인덱스를 지정하는 것은 데이터 자체가 고유하다는 특성이 있을 때만 의미가 있습니다.Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. 예를 들어 기본 키가 NationalIDNumber 인 경우 HumanResources.Employee 테이블의 EmployeeID열 값이 고유하도록 하려면 NationalIDNumber 열에 대해 UNIQUE 제약 조건을 만듭니다.For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. 사용자가 이 열에서 두 명 이상의 직원에 대해 동일한 값을 입력하면 오류 메시지가 표시되고 중복된 값이 입력되지 않습니다.If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

여러 열로 구성된 고유 인덱스를 사용하면 인덱스 키의 각 값 조합이 고유합니다.With multicolumn unique indexes, the index guarantees that each combination of values in the index key is unique. 예를 들어 LastName, FirstNameMiddleName 열의 조합에 대해 고유 인덱스를 만들면 테이블에 있는 각 행에서 이러한 열의 값 조합이 모두 서로 다릅니다.For example, if a unique index is created on a combination of LastName, FirstName, and MiddleName columns, no two rows in the table could have the same combination of values for these columns.

클러스터형 인덱스와 비클러스터형 인덱스 모두 고유 인덱스가 될 수 있습니다.Both clustered and nonclustered indexes can be unique. 따라서 열의 데이터가 고유하면 같은 테이블에서 하나의 고유 클러스터형 인덱스와 여러 개의 고유 비클러스터형 인덱스를 만들 수 있습니다.Provided that the data in the column is unique, you can create both a unique clustered index and multiple unique nonclustered indexes on the same table.

고유 인덱스의 장점은 다음과 같습니다.The benefits of unique indexes include the following:

  • 정의된 열의 데이터 무결성이 보장됩니다.Data integrity of the defined columns is ensured.

  • 쿼리 최적화 프로그램에 유용한 추가 정보가 제공됩니다.Additional information helpful to the query optimizer is provided.

    PRIMARY KEY 또는 UNIQUE 제약 조건을 만들면 지정된 열에서 고유 인덱스가 자동으로 생성됩니다.Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. UNIQUE 제약 조건을 만드는 것과 제약 조건의 영향을 받지 않는 고유 인덱스를 만드는 것에는 큰 차이가 없습니다.There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. 데이터 유효성 검사가 동일한 방식으로 수행되고 쿼리 최적화 프로그램에서는 제약 조건에 의해 생성된 고유 인덱스와 수동으로 만든 고유 인덱스를 동일하게 취급합니다.Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. 그러나 데이터 무결성을 유지하는 것이 목적일 때는 열에 UNIQUE 또는 PRIMARY KEY 제약 조건을 만들어야 합니다.However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. 그렇게 함으로써 인덱스의 용도가 명확해집니다.By doing this the objective of the index will be clear.

고려 사항Considerations

  • 데이터에 중복된 키 값이 있으면 고유 인덱스, UNIQUE 제약 조건 또는 PRIMARY KEY 제약 조건을 만들 수 없습니다.A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.

  • 데이터가 고유한 경우 고유성을 강제로 적용하려면 같은 열 조합에 고유하지 않은 인덱스 대신 고유 인덱스를 만들면 쿼리 최적화 프로그램에 추가 정보가 제공되어 실행 계획의 효율성이 향상됩니다.If the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. 이 경우 가급적 UNIQUE 제약 조건을 만들어 고유 인덱스를 만드는 것이 좋습니다.Creating a unique index (preferably by creating a UNIQUE constraint) is recommended in this case.

  • 고유 비클러스터형 인덱스에는 키가 아닌 포괄 열이 포함될 수 있습니다.A unique nonclustered index can contain included nonkey columns. 자세한 내용은 포괄 열이 있는 인덱스를 참조하십시오.For more information, see Index with Included Columns.

필터링된 인덱스 디자인 지침Filtered Index Design Guidelines

필터링된 인덱스는 특히 데이터의 잘 정의된 하위 집합에서 선택하는 쿼리를 처리하는 데 적합한 최적화된 비클러스터형 인덱스입니다.A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. 이 인덱스에서는 필터 조건자를 사용하여 테이블의 일부 행을 인덱싱합니다.It uses a filter predicate to index a portion of rows in the table. 잘 디자인된 필터링된 인덱스는 전체 테이블 인덱스에 비해 쿼리 성능을 개선하고 인덱스 유지 관리 비용과 인덱스 저장소 비용을 줄일 수 있습니다.A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

||
|-|
|적용 대상: SQL Server 2008SQL Server 2008 부터 SQL Server 2017SQL Server 2017까지Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.|

필터링된 인덱스는 전체 테이블 인덱스에 비해 다음과 같은 이점이 있습니다.Filtered indexes can provide the following advantages over full-table indexes:

  • 향상된 쿼리 성능 및 계획 품질Improved query performance and plan quality

    잘 디자인된 필터링된 인덱스는 전체 테이블 비클러스터형 인덱스보다 크기가 작고 필터링된 통계가 있기 때문에 쿼리 성능 및 실행 계획 품질이 향상됩니다.A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. 필터링된 통계는 필터링된 인덱스의 행만 처리하기 때문에 전체 테이블 통계보다 정확합니다.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • 줄어든 인덱스 유지 관리 비용Reduced index maintenance costs

    인덱스의 DML(데이터 조작 언어) 문이 데이터에 영향을 줄 때에만 인덱스가 유지 관리됩니다.An index is maintained only when data manipulation language (DML) statements affect the data in the index. 필터링된 인덱스는 크기가 더 작고 인덱스의 데이터가 영향을 받을 때에만 유지 관리되기 때문에 전체 테이블 비클러스터형 인덱스에 비해 인덱스 유지 관리 비용이 줄어듭니다.A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. 특히 영향을 자주 받지 않는 데이터를 포함하는 경우에는 수많은 필터링된 인덱스가 있을 수 있습니다.It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. 마찬가지로 필터링된 인덱스에는 자주 영향을 받는 데이터만 들어 있을 경우 보다 작은 크기의 인덱스가 통계를 업데이트하는 비용을 줄입니다.Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

  • 줄어든 인덱스 저장소 비용Reduced index storage costs

    필터링된 인덱스를 만들면 전체 테이블 인덱스가 필요하지 않은 경우 비클러스터형 인덱스의 디스크 저장소를 줄일 수 있습니다.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. 저장소 요구 사항을 크게 증가시키지 않고 전체 테이블 비클러스터형 인덱스를 여러 필터링된 인덱스로 바꿀 수 있습니다.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

    필터링된 인덱스는 쿼리가 SELECT 문에서 참조하는 데이터의 잘 정의된 하위 집합이 열에 포함되는 경우 유용합니다.Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. 예는 다음과 같습니다.Examples are:

  • 몇 개의 NULL이 아닌 값만 포함하는 스파스 열Sparse columns that contain only a few non-NULL values.

  • 포함하는 데이터의 범주가 서로 다른 열Heterogeneous columns that contain categories of data.

  • 달러 금액, 시간 및 날짜와 같은 값의 범위를 포함하는 열Columns that contain ranges of values such as dollar amounts, time, and dates.

  • 열 값에 대해 간단한 비교 논리로 정의되는 테이블 파티션Table partitions that are defined by simple comparison logic for column values.

    필터링된 인덱스에 대해 줄어든 유지 관리 비용은 인덱스의 행 수가 전체 테이블 인덱스에 비해 적을 때 가장 분명하게 드러납니다.Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. 필터링된 인덱스에 테이블의 열이 대부분 포함되어 있을 경우 전체 테이블 인덱스보다 유지 관리 비용이 더 들 수 있습니다.If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. 이런 경우 필터링된 인덱스 대신 전체 테이블 인덱스를 사용해야 합니다.In this case, you should use a full-table index instead of a filtered index.

    필터링된 인덱스는 하나의 테이블에서 정의되고 간단한 비교 논리만 지원합니다.Filtered indexes are defined on one table and only support simple comparison operators. 여러 테이블을 참조하거나 복잡한 논리를 사용하는 필터 식이 필요할 경우 뷰를 만들어야 합니다.If you need a filter expression that references multiple tables or has complex logic, you should create a view.

디자인 고려 사항Design Considerations

효과적인 필터링된 인덱스를 디자인하려면 응용 프로그램이 사용하는 쿼리와 이 쿼리가 데이터의 하위 집합과 어떻게 연결되는지를 이해하는 것이 중요합니다.In order to design effective filtered indexes, it is important to understand what queries your application uses and how they relate to subsets of your data. 잘 정의된 하위 집합이 있는 데이터의 몇 가지 예로는 대개 NULL 값이 있는 열, 범주가 다른 값이 있는 열 및 특정 범위의 값이 있는 열이 있습니다.Some examples of data that have well-defined subsets are columns with mostly NULL values, columns with heterogeneous categories of values and columns with distinct ranges of values. 다음의 디자인 고려 사항은 전체 테이블 인덱스에 비해 필터링된 인덱스가 이점이 있을 경우에 다양한 시나리오를 제공해 줍니다.The following design considerations give a variety of scenarios for when a filtered index can provide advantages over full-table indexes.

데이터의 하위 집합에 대한 필터링된 인덱스Filtered Indexes for Subsets of Data

열에 적은 수의 쿼리 관련 값만 있는 경우 값의 하위 집합에 필터링된 인덱스를 만들 수 있습니다.When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. 예를 들어 열에 있는 값이 대부분 NULL이고 쿼리는 NULL이 아닌 값에서만 선택하는 경우 NULL이 아닌 데이터 행에 대한 필터링된 인덱스를 만들 수 잇습니다.For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. 결과 인덱스는 같은 키 열에서 정의된 전체 테이블 비클러스터형 인덱스에 비해 크기가 더 작고 유지 관리하는 비용이 더 적게 듭니다.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

예를 들어 AdventureWorks2012 데이터베이스에는 2,679개의 행이 있는 Production.BillOfMaterials 테이블이 있습니다.For example, the AdventureWorks2012 database has a Production.BillOfMaterials table with 2679 rows. EndDate 열에는 199개의 행만 NULL이 아닌 값이 들어 있고 나머지 2,480개의 행에는 NULL이 들어 있습니다.The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain NULL. 다음 필터링된 인덱스는 이 인덱스에서 정의된 열을 반환하고 EndDate에 필요한 NULL이 아닌 값이 있는 행만 선택하는 쿼리를 처리합니다.The following filtered index would cover queries that return the columns defined in the index and that select only rows with a non-NULL value for EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
    ON Production.BillOfMaterials (ComponentID, StartDate)  
    WHERE EndDate IS NOT NULL ;  
GO  

필터링된 인덱스 FIBillOfMaterialsWithEndDate 는 다음 쿼리에 적합합니다.The filtered index FIBillOfMaterialsWithEndDate is valid for the following query. 이 필터링된 인덱스가 쿼리 최적화 프로그램에서 사용되는지 확인하기 위해 쿼리 실행 계획을 표시할 수 있습니다.You can display the query execution plan to determine if the query optimizer used the filtered index.

SELECT ProductAssemblyID, ComponentID, StartDate   
FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL   
    AND ComponentID = 5   
    AND StartDate > '20080101' ;  

필터링된 인덱스를 만드는 방법 및 필터링된 인덱스 조건자 식을 정의하는 방법은 Create Filtered Indexes를 참조하십시오.For more information about how to create filtered indexes and how to define the filtered index predicate expression, see Create Filtered Indexes.

서로 다른 데이터에 대한 필터링된 인덱스Filtered Indexes for Heterogeneous Data

테이블에 다른 유형의 데이터 행이 있는 경우 하나 이상의 데이터 범주에 대한 필터링된 인덱스를 만들 수 있습니다.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data.

예를 들어 Production.Product 테이블에 나열된 제품은 각각 ProductSubcategoryID에 지정된 다음 제품 범주 Bikes, Components, Clothing 또는 Accessories 같은 제품 범주와 연결됩니다.For example, the products listed in the Production.Product table are each assigned to a ProductSubcategoryID, which are in turn associated with the product categories Bikes, Components, Clothing, or Accessories. Production.Product 테이블에 있는 해당 열 값이 서로 유사하지 않기 때문에 이러한 범주는 서로 다릅니다.These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. 예를 들어 Color, ReorderPoint, ListPrice, Weight, ClassStyle 열에는 각 제품 범주에 대한 고유한 특징이 있습니다.For example, the columns Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. 27개에서 36개의 하위 범주가 있는 Accessories에 쿼리가 자주 수행된다고 가정해 봅니다.Suppose that there are frequent queries for accessories which have subcategories between 27 and 36 inclusive. 다음 예와 같이 Accessories 하위 범주에 필터링된 인덱스를 만들어 Accessories에 대한 쿼리의 성능을 향상시킬 수 있습니다.You can improve the performance of queries for accessories by creating a filtered index on the accessories subcategories as shown in the following example.

CREATE NONCLUSTERED INDEX FIProductAccessories  
    ON Production.Product (ProductSubcategoryID, ListPrice)   
        Include (Name)  
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;  

쿼리 결과는 인덱스에 포함되고 기본 테이블 조회는 쿼리 계획에 포함되지 않으므로 필터링된 인덱스 FIProductAccessoriesThe filtered index FIProductAccessories covers the following query because the query

필터링된 인덱스 FIProductAccessories는 다음 쿼리에 사용할 수 있습니다.results are contained in the index and the query plan does not include a base table lookup. 예를 들어 쿼리 조건자 식 ProductSubcategoryID = 33 은 필터링된 인덱스 조건자 ProductSubcategoryID >= 27ProductSubcategoryID <= 36의 하위 집합이고 쿼리 조건자의 ProductSubcategoryIDListPrice 열은 모두 인덱스의 키 열이며 이름은 인덱스의 리프 수준에 포괄 열로 저장됩니다.For example, the query predicate expression ProductSubcategoryID = 33 is a subset of the filtered index predicate ProductSubcategoryID >= 27 and ProductSubcategoryID <= 36, the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.

SELECT Name, ProductSubcategoryID, ListPrice  
FROM Production.Product  
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;  

키 열Key Columns

적은 수의 키 또는 포괄 열을 필터링된 인덱스 정의에 포함하고 쿼리 최적화 프로그램에 필요한 열만 통합하여 쿼리 실행 계획에 대한 필터링된 인덱스를 선택하는 것이 가장 좋습니다.It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. 쿼리 최적화 프로그램에서는 필터링된 인덱스의 쿼리 처리 여부에 상관없이 쿼리에 대한 필터링된 인덱스를 선택할 수 있습니다.The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. 그러나 쿼리 최적화 프로그램에서는 필터링된 인덱스가 쿼리를 처리할 경우 필터링된 인덱스를 선택할 가능성이 커집니다.However, the query optimizer is more likely to choose a filtered index if it covers the query.

경우에 따라 필터링된 인덱스는 필터링된 인덱스 식에 필터링된 인덱스 정의의 포괄 열 또는 키로 열을 포함하지 않고 쿼리를 처리합니다.In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. 다음 지침은 필터링된 인덱스 식의 열이 필터링된 인덱스 정의의 포괄 열 또는 키여야 하는 경우에 대해 설명합니다.The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition. 이 예에서는 앞에서 만든 필터링된 인덱스 FIBillOfMaterialsWithEndDate 를 참조합니다.The examples refer to the filtered index, FIBillOfMaterialsWithEndDate that was created previously.

필터링된 인덱스 식이 쿼리 조건자와 같고 쿼리가 쿼리 결과로 필터링된 인덱스 식의 열을 반환하지 않는다면 필터링된 인덱스 식의 열이 필터링된 인덱스 정의의 포괄 열 또는 키여야 할 필요는 없습니다.A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results. 예를 들어 다음 쿼리 조건자가 필터 식과 같고 FIBillOfMaterialsWithEndDate 가 쿼리 결과로 반환되지 않기 때문에 EndDate 는 이 쿼리를 처리합니다.For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate 는 필터링된 인덱스 정의의 포괄 열 또는 키로 EndDate 가 필요하지 않습니다.FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;   

쿼리 조건자가 필터링된 인덱스 식과 다른 비교에 필터링된 인덱스 식의 열을 사용하면 해당 열은 필터링된 인덱스 정의의 포괄 열 또는 키여야 합니다.A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression. 예를 들어 다음 쿼리는 필터링된 인덱스에서 행의 하위 집합을 선택하기 때문에 FIBillOfMaterialsWithEndDate 는 이 쿼리에 적합합니다.For example, FIBillOfMaterialsWithEndDate is valid for the following query because it selects a subset of rows from the filtered index. 그러나 EndDate 가 필터링된 인덱스 식과 다른 EndDate > '20040101'비교에 사용되기 때문에 다음 쿼리를 처리하지는 못합니다.However, it does not cover the following query because EndDate is used in the comparison EndDate > '20040101', which is not equivalent to the filtered index expression. 쿼리 프로세서는 EndDate값을 조회하지 않고 이 쿼리를 실행할 수 없습니다.The query processor cannot execute this query without looking up the values of EndDate. 따라서 EndDate 는 필터링된 인덱스 정의의 포괄 열 또는 키여야 합니다.Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
WHERE EndDate > '20040101';   

필터링된 인덱스 식의 열이 쿼리 결과 집합에 있으면 해당 열은 필터링된 인덱스 정의의 포괄 열 또는 키여야 합니다.A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set. 예를 들어 다음 쿼리가 쿼리 결과에 FIBillOfMaterialsWithEndDate 열을 반환하기 때문에 EndDate 는 다음 쿼리를 처리하지 않습니다.For example, FIBillOfMaterialsWithEndDate does not cover the following query because it returns the EndDate column in the query results. 따라서 EndDate 는 필터링된 인덱스 정의의 포괄 열 또는 키여야 합니다.Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials  
WHERE EndDate IS NOT NULL;  

테이블의 클러스터형 인덱스 키가 필터링된 인덱스 정의의 포괄 열 또는 키여야 할 필요는 없습니다.The clustered index key of the table does not need to be a key or included column in the filtered index definition. 클러스터형 인덱스 키는 필터링된 인덱스를 비롯하여 모든 비클러스터형 인덱스에 자동으로 포함됩니다.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

필터 조건자의 데이터 변환 연산자Data Conversion Operators in the Filter Predicate

필터링된 인덱스의 필터링된 인덱스 식에 지정된 비교 연산자로 인해 암시적 또는 명시적 데이터 변환이 발생할 경우 비교 연산자의 왼쪽에서 변환이 일어나면 오류가 발생합니다.If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. 이에 대한 해결 방법은 비교 연산자의 오른쪽에 데이터 변환 연산자(CAST 또는 CONVERT)를 사용하여 필터링된 인덱스 식을 작성하는 것입니다.A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

다음 예에서는 여러 가지 데이터 형식이 있는 테이블을 만듭니다.The following example creates a table with a variety of data types.

USE AdventureWorks2012;  
GO  
CREATE TABLE dbo.TestTable (a int, b varbinary(4));  

다음 필터링된 인덱스 정의에서 상수 1과 비교하기 위해 b 열이 정수 데이터 형식으로 암시적으로 변환됩니다.In the following filtered index definition, column b is implicitly converted to an integer data type for the purpose of comparing it to the constant 1. 이로 인해 오류 메시지 10611이 생성되며 그 이유는 필터링된 조건자에 있는 연산자의 왼쪽에서 변환이 발생하기 때문입니다.This generates error message 10611 because the conversion occurs on the left hand side of the operator in the filtered predicate.

CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = 1;  

해결 방법은 다음 예에서와 같이 b열과 동일한 유형이 되도록 오른쪽에 있는 상수를 변환하는 것입니다.The solution is to convert the constant on the right hand side to be of the same type as column b, as seen in the following example:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)  
WHERE b = CONVERT(Varbinary(4), 1);  

데이터 변환을 비교 연산자의 왼쪽에서 오른쪽으로 이동하면 변환 방법이 변경될 수 있습니다.Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. 위의 예에서 CONVERT 연산자가 오른쪽에 추가될 때 정수 비교에서 varbinary 비교로 비교가 변경되었습니다.In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.

더 보기Additional Reading

SQL Server 2008 인덱싱된 뷰를 통해 성능 향상Improving Performance with SQL Server 2008 Indexed Views
Partitioned Tables and IndexesPartitioned Tables and Indexes