인덱싱된 뷰 만들기

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

이 문서에서는 보기에서 인덱스를 만드는 방법을 설명합니다. 뷰에서 만든 첫 번째 인덱스가 고유한 클러스터형 인덱스여야 합니다. 고유 클러스터형 인덱스가 만들어진 후에 비클러스터형 인덱스를 더 만들 수 있습니다. 뷰에 고유한 클러스터형 인덱스를 만들면 클러스터형 인덱스가 있는 테이블이 저장되는 것과 동일한 방식으로 데이터베이스에 저장되므로 쿼리 성능이 향상됩니다. 쿼리 최적화 프로그램은 인덱싱된 뷰를 사용하여 쿼리 실행 속도를 높일 수 있습니다. 최적화 프로그램이 대체를 위해 해당 뷰를 고려하기 위해 쿼리에서 뷰를 참조할 필요가 없습니다.

단계

인덱싱된 뷰를 만들려면 다음 단계가 필요하며 인덱싱된 뷰를 성공적으로 구현하는 데 중요합니다.

  1. 보기에서 SET 참조되는 모든 기존 테이블에 대한 옵션이 올바른지 확인합니다.
  2. 테이블과 뷰를 만들기 전에 세션에 대한 SET 옵션이 올바르게 설정되었는지 확인합니다.
  3. 뷰 정의가 결정적인지 확인합니다.
  4. 기본 테이블에 뷰와 동일한 소유자가 있는지 확인합니다.
  5. 옵션을 사용하여 보기를 만듭니다 WITH SCHEMABINDING .
  6. 보기에서 고유한 클러스터형 인덱스 만들기

Important

많은 수의 인덱싱된 뷰에서 INSERT 참조하는 UPDATEDELETE 테이블에서 또는 연산(데이터 조작 언어 또는 DML)을 실행하거나 매우 복잡하지만 매우 복잡한 인덱싱된 뷰를 실행하는 경우 참조된 인덱싱된 뷰도 업데이트해야 합니다. 따라서 DML 쿼리 성능이 크게 저하되거나 경우에 따라 쿼리 계획을 생성할 수도 없습니다. 이러한 시나리오에서는 프로덕션 사용 전에 DML 쿼리를 테스트하고, 쿼리 계획을 분석하고, DML 문을 조정/단순화합니다.

인덱싱된 뷰에 필요한 SET 옵션

쿼리가 실행될 때 다른 SET 옵션이 활성 상태일 때 동일한 식을 평가하면 데이터베이스 엔진에서 다른 결과가 생성될 수 있습니다. 예를 들어 SET 옵션이 CONCAT_NULL_YIELDS_NULL ON으로 설정된 후 식 'abc' + NULL 은 값을 NULL반환합니다. 그러나 OFF로 설정된 후에 CONCAT_NULL_YIELDS_NULL 는 동일한 식이 생성됩니다 'abc'.

뷰를 올바르게 유지하고 일관된 결과를 반환할 수 있도록 인덱싱된 뷰에는 여러 SET 옵션에 대한 고정 값이 필요합니다. 다음 테이블의 SET 옵션은 다음 조건이 발생할 때마다 필수 값 열에 표시된 값 으로 설정해야 합니다.

  • 뷰의 뷰 및 후속 인덱스가 만들어집니다.
  • 뷰를 만들 때 뷰에서 참조되는 기본 테이블입니다.
  • 인덱싱된 뷰에 참가하는 테이블에서 삽입, 업데이트 또는 삭제 작업이 수행됩니다. 이 요구 사항에는 대량 복사, 복제 및 분산 쿼리와 같은 작업이 포함됩니다.
  • 인덱싱된 뷰는 쿼리 최적화 프로그램에서 쿼리 계획을 생성하는 데 사용됩니다.
Set 옵션 필수 값 기본 서버 값 기본값

OLE DB 및 ODBC 값		 기본값

DB-Library 값
ANSI_NULLS 켜기 켜기 켜기 OFF
ANSI_PADDING 켜기 켜기 켜기 OFF
ANSI_WARNINGS 1 켜기 켜기 켜기 OFF
ARITHABORT 켜기 켜기 OFF OFF
CONCAT_NULL_YIELDS_NULL 켜기 켜기 켜기 OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER 켜기 켜기 켜기 OFF

1 ON으로 설정 ANSI_WARNINGS 은 암시적으로 ON으로 설정됩니다 ARITHABORT .

OLE DB 또는 ODBC 서버 연결을 사용하는 경우 수정해야 하는 유일한 값은 설정입니다 ARITHABORT . 모든 DB-라이브러리 값은 명령을 사용하여 SET 애플리케이션을 사용 sp_configure 하거나 애플리케이션에서 서버 수준에서 올바르게 설정해야 합니다.

Important

계산 열의 ARITHABORT 첫 번째 인덱싱된 뷰 또는 인덱스가 서버의 모든 데이터베이스에 만들어지는 즉시 사용자 옵션을 ON 서버 전체로 설정하는 것이 좋습니다.

결정적 뷰 요구 사항

인덱싱된 뷰의 정의는 결정적이어야 합니다. 선택 목록 WHERE 의 모든 식과 절 GROUP BY 이 결정적이면 보기가 결정적입니다. 결정적 식은 특정 입력 값 집합으로 평가될 때마다 항상 동일한 결과를 반환합니다. 결정적 함수만 결정적 식에 참여할 수 있습니다. 예를 들어 함수 DATEADD 는 세 개의 매개 변수에 대해 지정된 인수 값 집합에 대해 항상 동일한 결과를 반환하기 때문에 결정적입니다. GETDATE 는 항상 동일한 인수를 사용하여 호출되기 때문에 결정적이지 않지만 반환하는 값은 실행될 때마다 변경됩니다.

뷰 열이 결정적인지 여부를 확인하려면 COLUMNPROPERTY 함수의 속성을 사용합니다IsDeterministic. 스키마 바인딩이 있는 뷰의 결정적 열이 정확한지 확인하려면 함수의 COLUMNPROPERTY 속성을 사용합니다IsPrecise. COLUMNPROPERTYTRUE는 유효하지 않은 입력의 경우 , 0 if FALSE및 NULL 를 반환 1 합니다. 즉, 열이 결정적이지 않거나 정확하지 않습니다.

식이 결정적이더라도 float 식을 포함하는 경우 정확한 결과는 프로세서 아키텍처 또는 마이크로코드 버전에 따라 달라질 수 있습니다. 데이터 무결성을 보장하기 위해 이런 식은 인덱싱된 뷰의 키가 아닌 열로만 참여할 수 있습니다. float 식을 포함하지 않는 결정적 식을 정밀이라고 합니다. 정확한 결정적 식만 키 열과 인덱싱된 뷰의 절 또는 GROUP BY 키 열에 WHERE 참여할 수 있습니다.

추가 요구 사항

옵션 및 결정적 함수 요구 사항 외에 SET 다음 요구 사항도 충족해야 합니다.

  • 실행하는 CREATE INDEX 사용자는 뷰의 소유자여야 합니다.

  • 인덱스 생성 시 인덱 IGNORE_DUP_KEY 스 옵션을 기본 설정으로 OFF 설정해야 합니다.

  • 테이블은 두 부분으로 구성된 이름인 스키마로 참조해야 합니다.뷰 정의의 테이블 이름 입니다.

  • 이 옵션을 사용하여 뷰에서 참조되는 사용자 정의 함수를 WITH SCHEMABINDING 만들어야 합니다.

  • 뷰에서 참조하는 사용자 정의 함수는 두 부분으로 구성된 이름인 <schema>.<function>으로 참조되어야 합니다.

  • 사용자 정의 함수의 데이터 액세스 속성은 여야 NO SQL하며 외부 액세스 속성은 이어야 NO합니다.

  • CLR(공용 언어 런타임) 함수는 보기의 선택 목록에 표시될 수 있지만 클러스터형 인덱스 키 정의의 일부가 될 수는 없습니다. CLR 함수는 뷰의 WHERE 절이나 뷰에 있는 JOIN 작업의 ON 절에 나타날 수 없습니다.

  • 뷰 정의에 사용되는 CLR 사용자 정의 형식의 CLR 함수 및 메서드에는 다음 표와 같이 설정된 속성이 있어야 합니다.

    Property 참고 항목
    결정적 = TRUE Microsoft .NET Framework 메서드의 특성으로 명시적으로 선언해야 합니다.
    PRECISE = TRUE .NET Framework 메서드의 특성으로 명시적으로 선언해야 합니다.
    데이터 액세스 = SQL 없음 특성과 DataAccess 특성을 DataAccessKind.NoneSystemDataAccess .로 설정하여 결정합니다 SystemDataAccessKind.None.
    EXTERNAL ACCESS = NO 이 속성은 CLR 루틴에 대해 기본적으로 NO로 설정됩니다.

  • 뷰는 WITH SCHEMABINDING 옵션을 사용하여 만들어야 합니다.

  • 뷰는 뷰와 동일한 데이터베이스에 있는 기본 테이블만 참조해야 합니다. 보기는 다른 보기를 참조할 수 없습니다.

  • 있는 경우 GROUP BY VIEW 정의는 포함해야 하며 포함 COUNT_BIG(*)HAVING해서는 안됩니다. 이러한 GROUP BY 제한 사항은 인덱싱된 뷰 정의에만 적용됩니다. 쿼리는 이러한 GROUP BY 제한을 충족하지 않더라도 실행 계획에서 인덱싱된 뷰를 사용할 수 있습니다.

  • 뷰 정의에 절이 GROUP BY 포함된 경우 고유한 클러스터형 인덱스의 키는 절에 지정된 열만 참조할 GROUP BY 수 있습니다.

  • 뷰 정의의 SELECT 문에는 다음 Transact-SQL 구문이 포함되어서는 안됩니다.

    Transact-SQL 함수 가능한 대안
    COUNT COUNT_BIG 사용
    ROWSET 함수(OPENDATASOURCE, OPENQUERY, OPENROWSET및 OPENXML)
    산술 평균 AVG SUM 별도의 열로 사용 COUNT_BIG
    통계 집계 함수(STDEV, STDEVP, VAR및 VARP)
    SUM nullable 식을 참조하는 함수 내부 SUM() 를 사용하여 ISNULL 식을 null을 허용하지 않도록 설정
    기타 집계 함수(MIN, MAX, CHECKSUM_AGG및 STRING_AGG)
    사용자 정의 집계 함수(SQL CLR)
    SELECT 절 Transact-SQL 요소 가능한 대안
    WITH cte AS CTE(공통 테이블 식) WITH
    SELECT 하위 쿼리
    SELECT SELECT [ <table>. ] * 열 이름을 명시적으로 지정합니다.
    SELECT SELECT DISTINCT GROUP BY 사용
    SELECT SELECT TOP
    SELECT OVER 순위 또는 집계 창 함수를 포함하는 절
    FROM LEFT OUTER JOIN
    FROM RIGHT OUTER JOIN
    FROM FULL OUTER JOIN
    FROM OUTER APPLY
    FROM CROSS APPLY
    FROM 파생 테이블 식(즉, 절에서 FROM 사용SELECT)
    FROM 자체 조인
    FROM 테이블 변수
    FROM 인라인 테이블 반환 함수
    FROM 다중 문 테이블 반환 함수
    FROM PIVOT, UNPIVOT
    FROM TABLESAMPLE
    FROM FOR SYSTEM_TIME 임시 기록 테이블을 직접 쿼리합니다.
    WHERE 전체 텍스트 조건자(CONTAINS, , FREETEXTCONTAINSTABLE,FREETEXTTABLE)
    GROUP BY CUBE, ROLLUP또는 GROUPING SETS 연산자 각 열 조합 GROUP BY 에 대해 별도의 인덱싱된 뷰 정의
    GROUP BY HAVING
    집합 연산자 UNION, UNION ALL, EXCEPT, INTERSECT 각 절에서 , AND NOT및 AND 절을 WHERE 사용합니다OR.
    ORDER BY ORDER BY
    ORDER BY OFFSET
    원본 열 형식 가능한 대안
    사용되지 않는 큰 값 열 형식 텍스트, ntext 및 이미지 각각 varchar(max), nvarchar(max) 및 varbinary(max)로 열을 마이그레이션합니다.
    xml 또는 FILESTREAM 열
    인덱스 키의 float1 열
    스파스 열 집합

    1 인덱싱된 뷰에는 부동 열이 포함될 수 있지만 이러한 열은 클러스터형 인덱스 키에 포함될 수 없습니다.

    Important

    인덱싱된 뷰는 임시 쿼리를 기반으로 사용할 수 없습니다(FOR SYSTEM_TIME 절을 사용하는 쿼리).

datetime 및 smalldatetime 권장 사항

인덱싱된 뷰에서 datetime 및 smalldatetime 문자열 리터럴을 참조하는 경우 결정적 날짜 형식 스타일을 사용하여 리터럴을 원하는 날짜 형식으로 명시적으로 변환하는 것이 좋습니다. 결정적 날짜 서식 스타일 목록은 CAST 및 CONVERT(Transact-SQL)를 참조하세요. 결정적 식과 비결정적 식에 대한 자세한 내용은 이 페이지의 고려 사항 섹션을 참조하세요.

문자열을 datetime 또는 smalldatetime으로 암시적으로 변환하는 식은 비결정적인 것으로 간주됩니다. 자세한 내용은 날짜 값으로 리터럴 날짜 문자열의 비결정적 변환을 참조하세요.

인덱싱된 뷰 관련 성능 고려 사항

많은 수의 인덱싱된 뷰에서 참조하는 테이블에서 DML(예: UPDATEDELETE 또는INSERT)을 실행하거나 더 적지만 복잡한 인덱싱된 뷰를 실행하는 경우 DML 실행 중에도 인덱싱된 뷰를 업데이트해야 합니다. 따라서 DML 쿼리 성능이 크게 저하되거나 경우에 따라 쿼리 계획을 생성할 수도 없습니다. 이러한 시나리오에서는 프로덕션 사용 전에 DML 쿼리를 테스트하고, 쿼리 계획을 분석하고, DML 문을 조정/단순화합니다.

데이터베이스 엔진이 인덱싱된 뷰를 사용하지 못하도록 하려면 쿼리에 OPTION (EXPAND VIEWS) 힌트를 포함합니다. 또한 나열된 옵션이 잘못 설정된 경우 최적화 프로그램에서 뷰의 인덱스를 사용하지 못하게 됩니다. 힌트에 대한 OPTION (EXPAND VIEWS) 자세한 내용은 SELECT(Transact-SQL)를 참조하세요.

다양한 추가 고려 사항

  • 인덱싱된 뷰의 열에 대한 large_value_types_out_of_row 옵션의 설정은 기본 테이블의 해당 열에 대한 설정에서 상속됩니다. 이 값은 sp_tableoption 사용하여 설정됩니다. 식에서 형성된 열의 기본 설정은 0입니다. 즉, 큰 값 형식이 행에 저장됩니다.

  • 인덱싱된 뷰는 분할된 테이블에 만들 수 있으며 자체 분할될 수 있습니다.

  • 뷰를 삭제하면 뷰의 모든 인덱스가 삭제됩니다. 클러스터형 인덱스가 삭제되면 뷰에 대한 모든 비클러스터형 인덱스 및 자동 생성된 통계가 삭제됩니다. 뷰에서 사용자가 만든 통계는 유지 관리됩니다. 비클러스터형 인덱스는 개별적으로 삭제할 수 있습니다. 뷰에서 클러스터형 인덱스를 삭제하면 저장된 결과 집합이 제거되고 최적화 프로그램은 표준 보기처럼 뷰를 처리하는 것으로 돌아갑니다.

  • 테이블 및 뷰의 인덱스를 사용하지 않도록 설정할 수 있습니다. 테이블의 클러스터형 인덱스가 비활성화되면 테이블과 관련된 뷰의 인덱스도 비활성화됩니다.

사용 권한

뷰를 만들려면 사용자가 데이터베이스에 CREATE VIEW 권한을 보유하고 뷰를 만드는 스키마에 대한 ALTER 권한을 보유해야 합니다. 기본 테이블이 다른 스키마 내에 있는 경우 최소한 테이블에 대한 REFERENCES 권한이 필요합니다. 인덱스를 만드는 사용자가 뷰를 만든 사용자와 다른 경우 인덱스 만들기에만 보기에 대한 ALTER 권한이 필요합니다(스키마의 ALTER가 적용됨).

인덱스는 참조된 테이블 또는 테이블과 동일한 소유자가 있는 뷰에서만 만들 수 있습니다. 이를 뷰와 테이블 간의 온전한 소유권 체인 이라고도 합니다. 일반적으로 테이블과 뷰가 동일한 스키마 내에 있는 경우 스키마 내의 모든 개체에 동일한 스키마 소유자가 적용됩니다. 따라서 뷰를 만들 수 있으며 뷰의 소유자가 될 수 없습니다. 반면에 스키마 내의 개별 개체에는 다른 명시적 소유자가 있을 수도 있습니다. principal_idsys.tables 소유자가 스키마 소유자와 다른 경우 열에 값이 포함됩니다.

인덱싱된 뷰 만들기: T-SQL 예제

다음 예제에서는 데이터베이스에서 해당 뷰에 뷰 및 인덱스 만들기 AdventureWorks

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
   QUOTED_IDENTIFIER, ANSI_NULLS ON;

--Create view with SCHEMABINDING.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
   DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
   WITH SCHEMABINDING
   AS
      SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID;
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
   ON Sales.vOrders (OrderDate, ProductID);
GO

다음 두 쿼리는 절에 뷰가 지정되지 않았더라도 인덱싱된 FROM 뷰를 사용하는 방법을 보여 줍니다.

--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
   OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o
   ON od.SalesOrderID=o.SalesOrderID
      AND o.OrderDate >= CONVERT(datetime, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700 and 800
   GROUP BY OrderDate, ProductID
   ORDER BY Rev DESC;
GO

--This query will also use the above indexed view.
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o
   ON od.SalesOrderID=o.SalesOrderID
      AND o.OrderDate >= CONVERT(datetime,'03/01/2012', 101)
      AND o.OrderDate < CONVERT(datetime,'04/01/2012', 101)
    GROUP BY OrderDate
    ORDER BY OrderDate ASC;

마지막으로, 이 예제에서는 인덱싱된 뷰에서 직접 쿼리하는 방법을 보여줍니다. SQL Server 2016(13.x) 서비스 팩 1 이전에는 쿼리 최적화 프로그램에서 인덱싱된 뷰를 자동으로 사용하는 것은 SQL Server의 특정 버전에서만 지원됩니다. SQL Server Standard 버전에서는 쿼리 힌트를 NOEXPAND 사용하여 인덱싱된 뷰를 직접 쿼리해야 합니다. SQL Server 2016(13.x) 서비스 팩 1부터 모든 버전은 인덱싱된 뷰의 자동 사용을 지원합니다. 또한 Azure SQL Database 및 Azure SQL Managed Instance는 힌트를 NOEXPAND 지정하지 않고 인덱싱된 뷰의 자동 사용을 지원합니다. 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하세요.

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(datetime,'03/01/2012', 101)
    AND OrderDate < CONVERT(datetime,'04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(datetime,'03/01/2012', 101)
    AND OrderDate < CONVERT(datetime,'04/01/2012', 101)
ORDER BY OrderDate ASC;

자세한 내용은 CREATE VIEW(Transact-SQL)를 참조하세요.

다음 단계