SQL Server 2008부터 Filtered Index가 추가 되었습니다.
Filtered Index를 사용하면서 ANSI OPTION의 영향으로 에러가 발생하는 내용이 있어 정리하였습니다.
결론: Filtered Index 사용 시 항상 아래 OPTION이 설정 되어 있어야 합니다.
|
SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET NUMERIC_ROUNDABORT OFF SET QUOTED_IDENTIFIER ON |
Ø OPTION 별 인덱스 사용 유무
|
ANSI OPTION |
Default Set |
OPTION OFF 상수 Query |
OPTION OFF 변수 Query |
OPTION OFF SP |
OPTION ON SP |
|
|
ANSI_NULLS |
ON |
X |
X |
X |
O |
|
|
ANSI_PADDING |
ON |
X |
X |
X |
X |
|
|
ANSI_WARNINGS |
ON |
X |
X |
X |
X |
|
|
ARITHABORT |
ON |
O |
O |
O |
O |
|
|
CONCAT_NULL_YIELDS_NULL |
ON |
X |
X |
X |
X |
|
|
NUMERIC_ROUNDABORT |
OFF |
X |
X |
X |
X |
|
|
QUOTED_IDENTIFIER |
ON |
X |
X |
X |
O |
|
|
O |
Filtered Index 인덱스 사용 가능 |
|
||||
|
X |
Filtered Index 인덱스 사용 불가 |
|
||||
일반적으로 사용되는 SQL Server Management Studio 툴에서는 아래와 같은 옵션이 기본으로 설정 되어 있습니다.
기본 설정이 아닌 ANSI OPTION을 변경하여 발생 하는 오류에 대해 아래 테스트를 통해 확인 해보도록 하겠습니다.
테스트는 총 5가지를 진행 하였습니다.
- 공통 Filtered Index를 사용할 수 있게 WHERE 조건 작성
- Case1. WHERE 상수 입력
- Case2. WHERE 변수 입력
- Case3. SP를 통해 실행
- Case4. Index Hint 강제
- Case5. SP만 생성, 변경 시 OPTION의 영향
|
/************************************************************ 테스트 환경 구성 ************************************************************/ USE TESTDB GO
--@@ 테스트 테이블 생성 IF OBJECT_ID('ttt_FilteredIndex','U') IS NOT NULL BEGIN DROP TABLE ttt_FilteredIndex END
CREATE TABLE ttt_FilteredIndex ( n8IndexSN bigint identity(1,1) not null , n8SeqNo bigint not null , TestID varchar(60) null , TestID2 varchar(300) null , ZZZZZ int null CONSTRAINT PK_TEST_n8IndexSN PRIMARY KEY CLUSTERED ( n8IndexSN ) )
--@@ 테스트 데이터 입력 INSERT INTO ttt_FilteredIndex (n8SeqNo , TestID, TestID2) SELECT SalesOrderDetailID , 'taek'+ convert(varchar(100),SalesOrderDetailID ) , 'test'+ convert(varchar(100),SalesOrderDetailID ) FROM AdventureWorks.Sales.SalesOrderDetail
INSERT INTO ttt_FilteredIndex (n8SeqNo , TestID, TestID2) SELECT TOP 30000 SalesOrderDetailID , NULL , 'test'+ convert(varchar(100),SalesOrderDetailID ) FROM AdventureWorks.Sales.SalesOrderDetail
INSERT INTO ttt_FilteredIndex (n8SeqNo , TestID, TestID2) SELECT TOP 30000 SalesOrderDetailID , NULL , NULL FROM AdventureWorks.Sales.SalesOrderDetail
INSERT INTO ttt_FilteredIndex (n8SeqNo , TestID, TestID2) SELECT SalesOrderDetailID , 'sssss'+ convert(varchar(100),SalesOrderDetailID ) , 'zzzzzz'+ convert(varchar(100),SalesOrderDetailID ) FROM AdventureWorks.Sales.SalesOrderDetail
--@@ Filtered Index 생성 CREATE INDEX tti_FilteredIndex_FI_TestID_TestID2 ON ttt_FilteredIndex ( TestID, TestID2 )WHERE TestID IS NOT NULL
CREATE PROC ttp_TestIndex ( @TestID varchar(60) , @TestID2 varchar(300) ) AS
SELECT * FROM dbo.ttt_FilteredIndex WITH(NOLOCK) WHERE TestID = @TestID AND TestID2 = @TestID2
CREATE PROC ttp_TestIndex2 ( @TestID varchar(60) , @TestID2 varchar(300) ) AS
SELECT * FROM dbo.ttt_FilteredIndex WITH(NOLOCK, INDEX = tti_FilteredIndex_FI_TestID_TestID2) WHERE TestID = @TestID AND TestID2 = @TestID2
/************************************************************ 테스트 진행 ************************************************************/ --@@ 현재 세션의 옵션을 확인 DBCC USEROPTIONS /* textsize 2147483647 language 한국어 dateformat ymd datefirst 7 statistics XML SET lock_timeout -1 quoted_identifier SET arithabort SET ansi_null_dflt_on SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET isolation level read committed */
--@@ 현재 세션의 기본 옵션 SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET NUMERIC_ROUNDABORT OFF SET QUOTED_IDENTIFIER ON
--@@ 옵션을 끄고 테스트를 진행 /* 공통 Filtered Index를 사용할 수 있게 WHERE 조건 작성 Case1. WHERE 상수 입력 Case2. WHERE 변수 입력 Case3. SP를 통해 실행 Case4. Index Hint 강제 Case5. SP만 생성 시 OPTION의 영향 */
SET ANSI_NULLS OFF -- 인덱스 사용 불가 (SP는 사용가능 ) SET ANSI_PADDING OFF -- 인덱스 사용 불가 -- 다음 SET 옵션의 설정이 잘못되어 SELECT이(가) 실패했습니다. 'ANSI_PADDING'. SET 옵션을 인덱싱된 뷰 및/또는 계산 열에 대한 인덱스 및/또는 필터링된 인덱스 및/또는 쿼리 알림 및/또는 XML 데이터 형식 메서드 및/또는 공간 인덱스 작업과(와) 함께 사용할 수 있는지 확인하십시오. SET ANSI_WARNINGS OFF -- 인덱스 사용 불가 -- 다음 SET 옵션의 설정이 잘못되어 SELECT이(가) 실패했습니다. 'ANSI_WARNINGS'. SET 옵션을 인덱싱된 뷰 및/또는 계산 열에 대한 인덱스 및/또는 필터링된 인덱스 및/또는 쿼리 알림 및/또는 XML 데이터 형식 메서드 및/또는 공간 인덱스 작업과(와) 함께 사용할 수 있는지 확인하십시오. SET ARITHABORT OFF -- 영향 없음 SET CONCAT_NULL_YIELDS_NULL OFF -- 인덱스 사용 불가 -- 다음 SET 옵션의 설정이 잘못되어 SELECT이(가) 실패했습니다. 'CONCAT_NULL_YIELDS_NULL'. SET 옵션을 인덱싱된 뷰 및/또는 계산 열에 대한 인덱스 및/또는 필터링된 인덱스 및/또는 쿼리 알림 및/또는 XML 데이터 형식 메서드 및/또는 공간 인덱스 작업과(와) 함께 사용할 수 있는지 확인하십시오. SET NUMERIC_ROUNDABORT ON -- 인덱스 사용 불가 -- 다음 SET 옵션의 설정이 잘못되어 SELECT이(가) 실패했습니다. 'NUMERIC_ROUNDABORT'. SET 옵션을 인덱싱된 뷰 및/또는 계산 열에 대한 인덱스 및/또는 필터링된 인덱스 및/또는 쿼리 알림 및/또는 XML 데이터 형식 메서드 및/또는 공간 인덱스 작업과(와) 함께 사용할 수 있는지 확인하십시오. SET QUOTED_IDENTIFIER OFF --인덱스 사용 불가 (SP는 사용가능 )
--@@ 일반적인 쿼리 SELECT * FROM dbo.ttt_FilteredIndex WITH(NOLOCK) WHERE TestID = 'sssss111789' AND TestID2 = 'zzzzzz111789' GO
DECLARE @TestID varchar(60) = 'sssss111789' DECLARE @TestID2 varchar(300) = 'zzzzzz111789'
SELECT * FROM dbo.ttt_FilteredIndex WITH(NOLOCK) WHERE TestID = @TestID AND TestID2 = @TestID2 GO
DECLARE @TestID varchar(60) = 'sssss111789' DECLARE @TestID2 varchar(300) = 'zzzzzz111789'
EXEC dbo.ttp_TestIndex @TestID, @TestID2 GO
--@@ Index Hint 강제 SELECT * FROM dbo.ttt_FilteredIndex WITH(NOLOCK, INDEX = tti_FilteredIndex_FI_TestID_TestID2) WHERE TestID = 'sssss111789' AND TestID2 = 'zzzzzz111789' GO
DECLARE @TestID varchar(60) = 'sssss111789' DECLARE @TestID2 varchar(300) = 'zzzzzz111789'
SELECT * FROM dbo.ttt_FilteredIndex WITH(NOLOCK, INDEX = tti_FilteredIndex_FI_TestID_TestID2) WHERE TestID = @TestID AND TestID2 = @TestID2 GO
DECLARE @TestID varchar(60) = 'sssss111789' DECLARE @TestID2 varchar(300) = 'zzzzzz111789'
EXEC dbo.ttp_TestIndex2 @TestID, @TestID2 GO
--@@ SP만 가능한 경우에 대한 테스트 /* 오류 발생 상황이 모두 동일하기 때문에 옵션 1개에 대해서만 테스트를 진행 */
--@@ 옵션 ON 설정 후 SP 생성 SET QUOTED_IDENTIFIER ON GO
ALTER PROC ttp_TestIndex2 ( @TestID varchar(60) , @TestID2 varchar(300) ) AS
SELECT * FROM dbo.ttt_FilteredIndex WITH(NOLOCK, INDEX = tti_FilteredIndex_FI_TestID_TestID2) WHERE TestID = @TestID AND TestID2 = @TestID2
--@@ 옵션 OFF 후 SP 실행 SET QUOTED_IDENTIFIER OFF GO
DECLARE @TestID varchar(60) = 'sssss111789' DECLARE @TestID2 varchar(300) = 'zzzzzz111789'
EXEC dbo.ttp_TestIndex2 @TestID, @TestID2 GO
--@@ 정상 출력
--@@ 옵션 OFF 후 SP 생성 SET QUOTED_IDENTIFIER OFF GO
ALTER PROC ttp_TestIndex2 ( @TestID varchar(60) , @TestID2 varchar(300) ) AS
SELECT * FROM dbo.ttt_FilteredIndex WITH(NOLOCK, INDEX = tti_FilteredIndex_FI_TestID_TestID2) WHERE TestID = @TestID AND TestID2 = @TestID2
--@@ 옵션 OFF 후 SP 실행 SET QUOTED_IDENTIFIER OFF GO
DECLARE @TestID varchar(60) = 'sssss111789' DECLARE @TestID2 varchar(300) = 'zzzzzz111789'
EXEC dbo.ttp_TestIndex2 @TestID, @TestID2 GO
/* 에러 발생 다음 SET 옵션의 설정이 잘못되어 SELECT이(가) 실패했습니다. 'QUOTED_IDENTIFIER'. SET 옵션을 인덱싱된 뷰 및/또는 계산 열에 대한 인덱스 및/또는 필터링된 인덱스 및/또는 쿼리 알림 및/또는 XML 데이터 형식 메서드 및/또는 공간 인덱스 작업과(와) 함께 사용할 수 있는지 확인하십시오. */
--@@ 옵션 ON 후 SP 실행 SET QUOTED_IDENTIFIER ON GO
DECLARE @TestID varchar(60) = 'sssss111789' DECLARE @TestID2 varchar(300) = 'zzzzzz111789'
EXEC dbo.ttp_TestIndex2 @TestID, @TestID2 GO
/* 에러 발생 다음 SET 옵션의 설정이 잘못되어 SELECT이(가) 실패했습니다. 'QUOTED_IDENTIFIER'. SET 옵션을 인덱싱된 뷰 및/또는 계산 열에 대한 인덱스 및/또는 필터링된 인덱스 및/또는 쿼리 알림 및/또는 XML 데이터 형식 메서드 및/또는 공간 인덱스 작업과(와) 함께 사용할 수 있는지 확인하십시오. */
|
Ø Query 정상 동작 시 Plan 정보
Ø Option 수정 후 Plan
