[2k5] 문자열 검색과 통계

1. /**********************************************************************************************
   -- Title : [2k5] 문자열 검색과 통계
   -- Reference : mcpworld.com
   -- Key word : show_statistics profile 문자열
   **********************************************************************************************/
   
   --CLEAN UP
   /*
   USE MASTER
   GO
   
   DROP DATABASE TESTDB
   GO
   */
   --임시 데이터베이스 생성
   CREATE DATABASE TESTDB
   GO
   
   USE TESTDB
   GO
   
   CREATE TABLE StringTestTable(
    StrValues VARCHAR(10)
   )
   GO
   
   INSERT StringTestTable VALUES (REPLICATE('A', 4))
   GO 100
   INSERT StringTestTable VALUES (REPLICATE('B', 4))
   GO 50
   INSERT StringTestTable VALUES (REPLICATE('C', 4))
   GO 30
   
   --입력된 데이터의 분포 형태
   SELECT StrValues, COUNT(*) AS CNT FROM StringTestTable GROUP BY StrValues
   GO
   /*
    AAAA 100
    BBBB 50
    CCCC 30
   */
   
   --현재 테이블의 통계 정보 출력
   SP_HELPSTATS StringTestTable, 'ALL'
   GO
   
   DBCC SHOW_STATISTICS(StringTestTable, _WA_Sys_00000001_7B905C75)
   GO
   
   SET STATISTICS PROFILE ON
   
   --실행 계획 부분에서 ESTIMATEROWS 컬럼값 비교 (SQL 2000에서와 비교)
   SELECT * FROM StringTestTable WHERE StrValues LIKE 'AAAA' --2k5:104.2105, 2k:100.0
   SELECT * FROM StringTestTable WHERE StrValues LIKE 'CCCC' --2k5: 28.4210, 2k: 50.0
   
   SELECT * FROM StringTestTable WHERE StrValues LIKE 'AA%' --2k5:104.2105, 2k:100.0
   SELECT * FROM StringTestTable WHERE StrValues LIKE 'CC%' --2k5: 28.4210, 2k: 50.0
   
   SELECT * FROM StringTestTable WHERE StrValues LIKE '%AA%' --2k5:104.2105, 2k:24.8285
   SELECT * FROM StringTestTable WHERE StrValues LIKE '%CC%' --2k5: 28.4210, 2k:24.8285
   
   SELECT * FROM StringTestTable WHERE StrValues LIKE '%AA' --2k5:104.2105, 2k:12.4142
   SELECT * FROM StringTestTable WHERE StrValues LIKE '%CC' --2k5: 28.4210, 2k:12.4142
   GO
   
   SET STATISTICS PROFILE OFF
   GO
   
   --CLEAN UP
   /*
   USE MASTER
   GO
   
   DROP DATABASE TESTDB
   GO
   */