반응형
  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
반응형

+ Recent posts