인덱싱된 뷰 만들기

뷰에 클러스터형 인덱스를 만들려면 먼저 뷰가 다음 요구 사항을 만족해야 합니다.

• CREATE VIEW 문이 실행될 때는 ANSI_NULLS 및 QUOTED_IDENTIFIER 옵션이 ON으로 설정되어 있어야 합니다. OBJECTPROPERTY 함수가 ExecIsAnsiNullsOn 또는 ExecIsQuotedIdentOn 속성을 통해 뷰에 대한 이 설정을 보고합니다.

• 뷰에서 참조하는 테이블을 만드는 모든 CREATE TABLE 문을 실행하려면 ANSI_NULLS 옵션이 ON으로 설정되어 있어야 합니다.

• 뷰는 기본 테이블 이외에 다른 어떠한 뷰를 참조해서는 안 됩니다.

• 뷰에서 참조하는 모든 기본 테이블은 뷰와 동일한 데이터베이스에 있어야 하고 뷰와 동일한 소유자를 가져야 합니다.

• SCHEMABINDING 옵션을 사용하여 뷰를 만들어야 합니다. 스키마 바인딩은 뷰를 기본 테이블의 스키마에 바인드합니다.

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

• 테이블 및 사용자 정의 함수는 뷰에서 두 부분으로 구성된 이름으로 참조되어야 합니다. 한 부분, 세 부분 및 네 부분으로 구성된 이름은 사용할 수 없습니다.

• 뷰의 식에서 참조하는 모든 함수는 결정적이어야 합니다. OBJECTPROPERTY 함수의 IsDeterministic 속성이 사용자 정의 함수가 결정적인지 여부를 보고합니다. 자세한 내용은 결정적 함수 및 비결정적 함수을 참조하십시오.

  [!참고]

  SQL Server 2008에서 인덱싱된 뷰의 datetime 및 smalldatetime 문자열 리터럴을 참조할 때 결정적 날짜 형식 스타일을 사용하여 리터럴을 원하는 날짜 유형으로 명시적으로 변환하는 것이 좋습니다. 결정적인 날짜 형식 스타일의 목록은 CAST 및 CONVERT(Transact-SQL)를 참조하십시오. 호환성 수준이 80 이하로 설정되지 않은 경우 문자열을 datetime 또는 smalldatetime으로 암시적으로 변환하는 작업과 관련된 식은 비결정적인 것으로 간주됩니다. 서버 세션의 LANGUAGE 및 DATEFORMAT 설정에 따라 결과가 달라지기 때문입니다. 예를 들어 'listopad' 문자열이 다른 언어에서는 다른 월을 의미하므로 CONVERT (datetime, '30 listopad 1996', 113) 식의 결과는 LANGUAGE 설정에 따라 달라집니다. 마찬가지로 DATEADD(mm,3,'2000-12-01') 식에서 SQL Server는 DATEFORMAT 설정을 기준으로 '2000-12-01' 문자열을 해석합니다.

  호환성 수준이 80 이하로 설정되지 않은 경우에 데이터 정렬 간의 비유니코드 문자 데이터를 암시적으로 변환하는 작업도 비결정적인 것으로 간주됩니다.

  90 호환 모드에서는 이러한 식을 포함하는 뷰에 인덱스를 만들 수 없습니다. 그러나 업그레이드된 데이터베이스로부터 이러한 식을 포함하는 기존 뷰는 유지 가능합니다. 문자열에서 날짜로의 암시적 변환이 포함된 인덱싱된 뷰를 사용하는 경우 인덱싱된 뷰의 손상을 방지하려면 데이터베이스 및 응용 프로그램에서 LANGUAGE 및 DATEFORMAT 설정이 일치하는지 확인하십시오.

• 뷰 정의에 집계 함수를 사용하는 경우 SELECT 목록에 COUNT_BIG (*)도 포함되어야 합니다.

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

• CLR(공용 언어 런타임) 함수는 뷰의 SELECT 목록에 표시될 수 있지만 클러스터형 인덱스 키 정의의 일부일 수는 없습니다. CLR 함수는 뷰의 WHERE 절이나 뷰에서 JOIN 연산의 ON 절에 표시되지 않습니다.

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

  속성	참고
  DETERMINISTIC = TRUE	Microsoft.NET Framework 메서드의 특성으로 명시적으로 선언되어야 합니다.
  PRECISE = TRUE	.NET Framework 메서드의 특성으로 명시적으로 선언되어야 합니다.
  DATA ACCESS = NO SQL	DataAccess 특성을 DataAccessKind.None으로 설정하고 SystemDataAccess 특성을 SystemDataAccessKind.None으로 설정함으로써 결정됩니다.
  EXTERNAL ACCESS = NO	CLR 루틴의 경우 이 속성의 기본값은 NO입니다.

  CLR 루틴 메서드의 특성을 설정하는 방법은 CLR 루틴용 사용자 지정 특성을 참조하십시오.

  주의
  CLR 루틴 메서드의 속성을 메서드의 기능과 상충되게 설정하는 것은 좋지 않습니다. 이렇게 하면 데이터가 손상될 수 있습니다.

• 뷰의 SELECT 문에는 다음의 Transact-SQL 구문 요소를 사용할 수 없습니다.

  • 열을 지정하는 * 또는 table_name**.*** 구문. 열 이름은 명시적으로 지정되어야 합니다.

  • 단순 식으로 사용되는 테이블 열 이름을 둘 이상의 뷰 열에 지정할 수 없습니다. 열에 대한 모든 참조 또는 하나를 제외한 모든 참조가 복잡한 식의 일부이거나 함수에 대한 매개 변수라면 열을 여러 번 참조할 수 있습니다. 예를 들어 다음 SELECT 목록이 잘못되었습니다.

    SELECT ColumnA, ColumnB, ColumnA
    

    이 SELECT 목록은 유효합니다.

    SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB
    

  • GROUP BY 절에서 사용된 열의 식 또는 집계 결과의 식

  • 파생 테이블

  • CTE(공통 테이블 식)

  • Rowset 함수

  • UNION, EXCEPT 또는 INTERSECT 연산자

  • 하위 쿼리

  • 외부 또는 자체 조인

  • TOP 절

  • ORDER BY 절

  • DISTINCT 키워드

  • COUNT(COUNT_BIG(*)이 허용됨)

  • AVG, MAX, MIN, STDEV, STDEVP, VAR 또는 VARP 집계 함수입니다. AVG(expression)가 인덱싱된 뷰를 참조하는 쿼리에 지정된 경우 뷰의 SELECT 목록에 SUM(expression) 및 COUNT_BIG(expression)이 있으면 최적화 프로그램은 필요한 결과를 자주 계산할 수 있습니다. 예를 들어 인덱싱된 뷰의 SELECT 목록에 AVG(column1) 식을 포함할 수 없습니다. 뷰 SELECT 목록에 SUM(column1) 및 COUNT_BIG(column1) 식이 있으면 SQL Server는 뷰를 참조하고 AVG(column1)를 지정하는 쿼리에 대해 평균을 계산할 수 있습니다.

  • Null 허용 식을 참조하는 SUM 함수

  • Ranking Window 또는 Aggregate Window 함수를 포함하는 OVER 절

  • CLR 사용자 정의 집계 함수

  • CONTAINS 또는 FREETEXT 전체 텍스트 조건자

  • COMPUTE 또는 COMPUTE BY 절

  • CROSS APPLY 또는 OUTER APPLY 연산자

  • PIVOT 또는 UNPIVOT 연산자

  • 테이블 힌트(90 이상의 호환 수준에만 적용)

  • 조인 힌트

  • Xquery 식에 대한 직접적인 참조. 스키마 바인딩 사용자 정의 함수 내에 Xquery 식과 같은 간접적인 참조를 사용할 수 있습니다.

• GROUP BY가 지정되면 뷰 SELECT 목록에 COUNT_BIG(*) 식이 포함되어야 하고 뷰 정의에서 HAVING, ROLLUP, CUBE 또는 GROUPING SETS를 지정할 수 없습니다.

CREATE INDEX 문의 요구 사항

뷰에 만들어지는 첫 번째 인덱스는 고유 클러스터형 인덱스여야 합니다. 고유 클러스터형 인덱스가 만들어진 후에 비클러스터형 인덱스를 더 만들 수 있습니다. 뷰 인덱스의 명명 규칙은 테이블 인덱스의 명명 규칙과 같습니다. 테이블 이름이 뷰 이름으로 바뀌는 것만 다릅니다. 자세한 내용은 CREATE INDEX(Transact-SQL)를 참조하십시오.

CREATE INDEX 문은 일반 CREATE INDEX 요구 사항뿐 아니라 다음 요구 사항도 만족해야 합니다.

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

• CREATE INDEX 문이 실행될 때 다음 SET 옵션은 ON으로 설정되어야 합니다.

  • ANSI_NULLS

  • ANSI_PADDING

  • ANSI_WARNINGS

  • CONCAT_NULL_YIELDS_NULL

  • QUOTED_IDENTIFIER

• NUMERIC_ROUNDABORT 옵션은 OFF로 설정되어야 합니다. 이 값은 기본 설정입니다.

• 데이터베이스가 80 호환성 모드 이전에서 실행되는 경우 ARITHABORT 옵션을 ON으로 설정해야 합니다.

• 클러스터형 인덱스 또는 비클러스터형 인덱스를 만들 때 IGNORE_DUP_KEY 옵션은 OFF(기본 설정)로 설정되어야 합니다.

• CREATE INDEX 문에서 text, ntext 또는 image 열을 참조하지 않는 경우에도 뷰에는 이러한 열이 포함될 수 없습니다.

• 뷰 정의의 SELECT 문에서 GROUP BY 절을 지정하면 고유 클러스터형 인덱스의 키는 GROUP BY 절에 지정된 열만 참조할 수 있습니다.

• 인덱스 키 열의 값을 구성하는 정확하지 않은 식은 뷰의 기반이 되는 기본 테이블의 저장 열을 참조해야 합니다. 이 열은 일반 저장 열 또는 지속형 계산 열일 수 있습니다. 다른 정확하지 않은 식은 인덱싱된 뷰의 키 열에 포함될 수 없습니다.

고려 사항

인덱싱된 뷰의 열에 대한 large_value_types_out_of_row 옵션의 설정은 기본 테이블의 해당 열에 대한 설정에서 상속됩니다. 이 값은 sp_tableoption을 통해 설정됩니다. 식으로부터 구성된 열의 기본 설정은 0으로서 큰 값 유형이 행 내부에 저장됨을 의미합니다. 자세한 내용은 큰 값 데이터 형식 사용을 참조하십시오.

클러스터형 인덱스가 만들어진 후 뷰의 기본 데이터를 수정하기 위한 모든 연결의 옵션 설정은 인덱스를 만드는 데 필요한 옵션 설정과 같아야 합니다. 문을 실행하는 연결의 옵션 설정이 잘못된 경우 SQL Server는 오류를 생성하고 뷰의 결과 집합에 영향을 주는 INSERT, UPDATE 또는 DELETE 문을 롤백합니다. 자세한 내용은 결과에 영향을 주는 SET 옵션을 참조하십시오.

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

CREATE UNIQUE CLUSTERED INDEX 문에 클러스터형 인덱스 키를 구성하는 열만 지정되어도 뷰의 전체 결과 집합이 데이터베이스에 저장됩니다. 기본 테이블의 클러스터형 인덱스와 마찬가지로 클러스터형 인덱스의 B-트리 구조에는 키 열만 포함되지만 데이터 행에는 뷰 결과 집합의 모든 열이 포함됩니다.

인덱스를 기존 시스템의 뷰에 추가하려는 경우 인덱스를 배치하려는 뷰를 스키마 바인딩해야 합니다. 다음 작업을 수행할 수 있습니다.

• 뷰를 삭제하고 WITH SCHEMABINDING을 지정하여 다시 만듭니다.

• 기존 뷰와 같은 텍스트를 갖지만 이름이 다른 두 번째 뷰를 만들 수 있습니다. 쿼리의 FROM 절에서 직접 참조하지 않아도 최적화 프로그램이 새 뷰에 있는 인덱스를 고려합니다.

  [!참고]

  뷰가 삭제되거나 변경되어 스키마 바인딩이 더 이상 존재하지 않는 경우에만 SCHEMABINDING 절로 만든 뷰에서 사용하는 뷰 또는 테이블을 삭제할 수 있습니다. 또한 ALTER TABLE 문이 뷰 정의에 영향을 주는 경우에는 스키마 바인딩이 있는 뷰에서 사용하는 테이블에 대해 ALTER TABLE 문이 실행되지 않습니다.

새로 만든 뷰가 인덱싱된 뷰의 모든 요구 사항을 준수하는지 확인해야 합니다. 이 작업을 수행하려면 뷰가 참조하는 모든 기본 테이블 및 뷰의 소유자를 변경하여 모두 같은 사용자가 소유하도록 해야 합니다.

테이블 및 뷰의 인덱스를 비활성화할 수 있습니다. 테이블의 클러스터형 인덱스가 비활성화되면 테이블과 관련된 뷰의 인덱스도 비활성화됩니다. 자세한 내용은 인덱스 비활성화를 참조하십시오.

예

다음 예에서는 뷰를 만들고 이 뷰에 인덱스를 만듭니다. 인덱싱된 뷰를 사용하는 두 개의 쿼리가 포함되어 있습니다.

USE AdventureWorks;
GO
--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;
GO
--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
--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 ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can 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 DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

참고 항목

참조

CREATE INDEX(Transact-SQL)

SET ANSI_NULLS (Transact-SQL)

SET ANSI_PADDING(Transact-SQL)

SET ANSI_WARNINGS(Transact-SQL)

SET ARITHABORT(Transact-SQL)

SET CONCAT_NULL_YIELDS_NULL(Transact-SQL)

SET NUMERIC_ROUNDABORT(Transact-SQL)

SET QUOTED_IDENTIFIER(Transact-SQL)

개념

인덱싱된 뷰 디자인

인덱싱된 뷰의 DBCC 오류 문제 해결