[2k5] 프로시저 recompile 확인 및 사용

1. /**********************************************************************************************
   -- Title : [2k5] 프로시저 recompile 확인 및 사용
   -- Reference : hanbitbook.co.kr
   -- Key word : 프로시저 procedure recompile 리컴파일
   **********************************************************************************************/
   use tempdb;
   go
2. drop table sptbl;
   go
3. select *
   into sptbl
   from adventureworks.sales.customer
   order by rowguid;
   go
4. create nonclustered index idx_sptbl_id on sptbl (customerid);
   go
5. --index seek
   select * from sptbl where customerid < 10;
   go
6. --table scan
   select * from sptbl where customerid < 5000;
7. drop proc usp_id;
   go
8. create proc usp_id
    @id int
   as
    select * from sptbl where customerid < @id;
   go
9. --index seek
   exec usp_id 10;
   go
10. --index seek한다~!!!!
    exec usp_id 5000;
    go
11. --table scan한다~!!!!
    exec usp_id 5000 with recompile;
    go
12. exec sp_recompile sptbl;
    exec usp_id 10;
    go
13. exec usp_id 10;
    go
14. dbcc freeproccache;
    exec usp_id 5000;
    go
15. drop proc usp_id;
    go
16. create proc usp_id
     @id int
    with recompile
    as
     select * from sptbl where customerid < @id;
    go
    exec usp_id 10;
    go
17. exec usp_id 10000;
    go