USE tableDB

go;

CREATE TABLE DBO.T_FILTER_INDEX

(

             a INT

             , b INT

)

GO

DROP INDEX T_FILTER_INDEX.CL_T_FILTER_INDEX_a

GO

-- 클러스터드인덱스에는생성이안된다.

--CREATE CLUSTERED INDEX CL_T_FILTER_INDEX_a

--ON dbo.T_FILTER_INDEX( a )

--WHERE a IS NOT NULL;

--GO

-- NC만생성이가능하구낭??

CREATE INDEX NC_T_FILTER_INDEX_a

ON dbo.T_FILTER_INDEX( a )

WHERE a IS NOT NULL;

GO

SET NOCOUNT ON

GO

-- 10000 번반복하다.

INSERT INTO dbo.T_FILTER_INDEX(a, b) VALUES(NULL, NULL)

GO 10000

DECLARE @i INT = 0;

WHILE @i < 40000

BEGIN

             INSERT INTO dbo.T_FILTER_INDEX(a, b) VALUES(@i, @i)     

             SET @i+=1;

END

EXEC sp_spaceused 'T_FILTER_INDEX'

GO

--name rows      reserved             data       index_size          unused

--T_FILTER_INDEX           50706               2504 KB 1656 KB 744 KB   104 KB

DBCC SHOW_STATISTICS('T_FILTER_INDEX', 'NC_T_FILTER_INDEX_a')

GO

SELECT COUNT(*)

FROM T_FILTER_INDEX

WHERE a IS NOT NULL

--Name                                                                                                                             Updated              Rows                 Rows Sampled         Steps  Density       Average key length String Index Filter Expression                                                                                                                                                                                                                                                Unfiltered Rows

---------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------

--NC_T_FILTER_INDEX_a                                                                                                              01 13 2010  3:55PM   40000                40000                3      1             4                  NO           ([a] IS NOT NULL)                                                                                                                                                                                                                                                50706

--All density   Average Length Columns

--------------- -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--2.5E-05       4              a

--RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS

-------------- ------------- ------------- -------------------- --------------

--0            0             1             0                    1      

--39998        39997         1             39997                1

--39999        0             1             0                    1

use tableDB

go

------------------------

-- TABLE 생성

------------------------

create table dbo.noCompress

(

             txt char(100)

);

go

create table dbo.Compress

(

             txt char(100)

) with ( data_compression=row) ;

go

SET NOCOUNT ON;

------------------------

-- SampleData Insert

------------------------

DECLARE @i INT = 0;

WHILE @i < 40000

BEGIN

             INSERTINTO dbo.noCompressvalues('ABC');

             INSERTINTO dbo.Compressvalues('ABC');           

             SET@i+=1;

END

------------------------

-- 공간절약얼마나??

------------------------

exec sp_spaceused 'noCompress'

go

exec sp_spaceused 'Compress'

go

--name rows      reserved             data       index_size          unused

--noCompress   40000              4552 KB 4512 KB 8KB      32 KB

--Compress       40000              520 KB   464 KB   8KB      48 KB

------------------------

-- I/O를얼마나줄이느냐??

------------------------

set statistics io on

go

select top 100  *

from noCompress

select top 100  *

from Compress

go

-- 우와,, 많이절약되는구낭

--테이블'noCompress'. 검색수1, 논리적읽기수2, 물리적읽기수0,미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

--테이블'Compress'. 검색수1, 논리적읽기수1, 물리적읽기수0,미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.

--go

------------------------

-- 압축을하였을경우얼마나절약할수있는지예측프로시져

------------------------

exec sp_estimate_data_compression_savings'dbo', 'noCompress', null, null, 'row';

go

--object_name   schema_name   index_id              partition_number size_with_current_compression_setting(KB)             size_with_requested_compression_setting(KB)      sample_size_with_current_compression_setting(KB)             sample_size_with_requested_compression_setting(KB)

--noCompress   dbo       0            1            4520      472        4344      456

--------------------------

--nocompress table 행압축시도

--------------------------

ALTER TABLE dbo.noCompress

             REBUILDWITH(DATA_COMPRESSION=ROW);

GO

exec sp_spaceused 'noCompress'

go