MS-SQL Server

Filtered index 사용 시 ANSI OPTION 설정

알 수 없는 사용자 2012. 7. 20. 12:06

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