인덱싱된 뷰 만들기
적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
이 문서에서는 보기에서 인덱스를 만드는 방법을 설명합니다. 뷰에서 만든 첫 번째 인덱스가 고유한 클러스터형 인덱스여야 합니다. 고유 클러스터형 인덱스가 만들어진 후에 비클러스터형 인덱스를 더 만들 수 있습니다. 뷰에 고유한 클러스터형 인덱스를 만들면 클러스터형 인덱스가 있는 테이블이 저장되는 것과 동일한 방식으로 데이터베이스에 저장되므로 쿼리 성능이 향상됩니다. 쿼리 최적화 프로그램은 인덱싱된 뷰를 사용하여 쿼리 실행 속도를 높일 수 있습니다. 최적화 프로그램이 대체를 위해 해당 뷰를 고려하기 위해 쿼리에서 뷰를 참조할 필요가 없습니다.
단계
인덱싱된 뷰를 만들려면 다음 단계가 필요하며 인덱싱된 뷰를 성공적으로 구현하는 데 중요합니다.
- 보기에서
SET
참조되는 모든 기존 테이블에 대한 옵션이 올바른지 확인합니다. - 테이블과 뷰를 만들기 전에 세션에 대한 SET 옵션이 올바르게 설정되었는지 확인합니다.
- 뷰 정의가 결정적인지 확인합니다.
- 기본 테이블에 뷰와 동일한 소유자가 있는지 확인합니다.
- 옵션을 사용하여 보기를 만듭니다
WITH SCHEMABINDING
. - 보기에서 고유한 클러스터형 인덱스 만들기
Important
많은 수의 인덱싱된 뷰에서 INSERT
참조하는 UPDATE
DELETE
테이블에서 또는 연산(데이터 조작 언어 또는 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
. COLUMNPROPERTY
TRUE
는 유효하지 않은 입력의 경우 , 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.None
SystemDataAccess
.로 설정하여 결정합니다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
, ,FREETEXT
CONTAINSTABLE
,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(예: UPDATE
DELETE
또는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_id
sys.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)를 참조하세요.
다음 단계
피드백
https://aka.ms/ContentUserFeedback
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요.다음에 대한 사용자 의견 제출 및 보기