PROCEDURE 에서 FILTERED INDEX 쓰는 방법

ROLLBACK 

GO

SET STATISTICS IO OFF

IF OBJECT_ID('tblx') IS NOT NULL

    DROP TABLE tblx

GO

CREATE TABLE tblx (

    c1 int,

    c2 int,

    c3 char(2000) default 'a'

)

GO

SET NOCOUNT ON

DECLARE @idx  int = 0

BEGIN TRAN

WHILE (@idx < 7000)

BEGIN

    INSERT tblx (c1, c2) VALUES (@idx, @idx % 100)

    SET @idx += 1

END

COMMIT TRAN

GO

SELECT * FROM tblx;

--7000

INSERT INTO tblx (c1, c2)

SELECT 20000 c1, 100 c2 FROM tblx

GO

--14000

CREATE CLUSTERED INDEX cl_tblx ON tblx(c2)

GO

CREATE NONCLUSTERED INDEX ncf_tblx_1 ON tblx (c1) WHERE (c1 < 20000)

GO

CREATE NONCLUSTERED INDEX ncf_tblx_2 ON tblx (c1) WHERE (c1 = 20000)

GO

CREATE NONCLUSTERED INDEX ncf_tblx_3 ON tblx (c1) WHERE (c1 > 20000)

GO

IF OBJECT_ID('usp_a') IS NOT NULL

    DROP PROC usp_a

GO

CREATE PROC usp_a

(@a int)

AS

SELECT c1, c2 FROM tblx WHERE c1 = @a

GO

-- TEST

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

EXEC dbo.usp_a 1

--1

StmtText : 

SELECT c1, c2 FROM tblx WHERE c1 = @a

  |--Clustered Index Scan(OBJECT:([head1ton].[dbo].[tblx].[cl_tblx]), WHERE:([head1ton].[dbo].[tblx].[c1]=[@a]))

 

ALTER PROCEDURE usp_a

(@a int)

AS

SELECT c1, c2 FROM tblx WITH (INDEX(ncf_tblx_1)) WHERE c1 = @a

GO

EXEC dbo.usp_a 1

GO

메시지 8622, 수준 16, 상태 1, 프로시저 usp_a, 줄 4

이 쿼리에 정의된 힌트로 인해 쿼리 프로세서에서 쿼리 계획을 생성할 수 없습니다. 힌트를 지정하거나 SET FORCEPLAN을 사용하지 않고 쿼리를 다시 전송하십시오.

ALTER PROC usp_a

(@a int)

AS

SELECT c1, c2 FROM tblx WHERE c1 = @a AND c1 < 20000

GO

EXEC dbo.usp_a 1

GO 

SELECT c1, c2 FROM tblx WHERE c1 = @a AND c1 < 20000

  |--Index Seek(OBJECT:([head1ton].[dbo].[tblx].[ncf_tblx_1]), SEEK:([head1ton].[dbo].[tblx].[c1]=[@a]) ORDERED FORWARD)

--고정으로 꼭 하고 싶은 경우

ALTER PROC usp_a

(@a int)

AS

SELECT c1, c2 FROM tblx WITH (INDEX(ncf_tblx_1)) WHERE c1 = @a AND c1 < 20000

GO

EXEC dbo.usp_a 1

GO 

SELECT c1, c2 FROM tblx WITH (INDEX(ncf_tblx_1)) WHERE c1 = @a AND c1 < 20000

  |--Index Seek(OBJECT:([head1ton].[dbo].[tblx].[ncf_tblx_1]), SEEK:([head1ton].[dbo].[tblx].[c1]=[@a]) ORDERED FORWARD)

예를 보듯이 WHERE 조건에 인덱스 조건을 성립시켜 처리한다.

http://www.sqler.com/?mid=bColumn&page=5&document_srl=126248