_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › SparseColumnsAndColumnSets

Contents

1 Sparse Columns
2 Example
3 Column Sets
4 Example
5 谿瑚襭


1 Sparse Columns #

ろ 伎 Null螳 豕 レ螳 朱 伎 襷. 蟠レ 豕 20% ~ 40% 螻糾 暑 蟆曙一 . 磯 碁煙れ 螳 . Geography, geometry, image, ntext, text, timestamp, user-defined data types 一危 sparse襦 讌 . れ 襷 伎企.

螻蠍語企一危壱
一危壱觜-ろる危ろる危NULL覦焔
bit0.1254.12598%
tinyint1586%
smallint2676%
int4864%
bigint81252%
real4864%
float81252%
smallmoney4864%
money81252%
smalldatetime4864%
datetime81252%
uniqueidentifier162043%
date3769%

豌 襴酔 譬 蠍語 一危
一危 觜-ろ 覦危ろ 覦危NULL 覦焔
datetime2(0)61057%
datetime2(7)81252%
time(0)3769%
time(7)5960%
datetimetoffset(0)81252%
datetimetoffset (7)101449%
decimal/numeric(1,s)5960%
decimal/numeric(38,s)172142%
vardecimal(p,s)decimal 朱 豺襦

一危 譬 蠍語 一危
一危壱觜-ろる危ろる危NULL覦焔
sql_variant蠍磯蓋一危壱磯朱る
varcharchar4+蠏一危2+蠏一危60%
nvarcharnchar4+蠏一危2+蠏一危60%
varbinarybinary4+蠏一危2+蠏一危60%
xml4+蠏一危2+蠏一危60%
hierarchyId4+蠏一危2+蠏一危60%

2 Example #

--drop table sparse_table
create table sparse_table
(
	sparse_col nchar(4) sparse null
);

--drop table non_sparse_table
create table non_sparse_table
(
	non_sparse_col nchar(4) null
);

with temp(num)
as
(
        select 1 num
        union all
        select num + 1 from temp
        where num + 1 <= 10
)
insert sparse_table(sparse_col) 
select N'覘°蟾?' -- 100000蟇
from temp a, temp b, temp c, temp d, temp e
union all
select NULL -- 100000蟇
from temp a, temp b, temp c, temp d, temp e;

with temp(num)
as
(
        select 1 num
        union all
        select num + 1 from temp
        where num + 1 <= 10
)
insert non_sparse_table(non_sparse_col) 
select N'覘°蟾?' -- 100000蟇
from temp a, temp b, temp c, temp d, temp e
union all
select NULL -- 100000蟇
from temp a, temp b, temp c, temp d, temp e;

exec sp_spaceused 'sparse_table'
exec sp_spaceused 'non_sparse_table'

/*
name         rows   reserved data    index_size unused
------------ ------ -------- ------- ---------- ------
sparse_table 200000 3720 KB  3664 KB 8 KB       48KB

name         rows   reserved data    index_size unused
------------ ------ -------- ------- ---------- ------
sparse_table 200000 3400 KB  3368 KB 8 KB       48KB
*/

3 Column Sets #

ろ 伎 企 企 覈 ろ 伎 覦 伎 讌 . 讌 蟲譟壱 豢レ朱 企 覈 ろ 伎 蟆壱 讌 XML 企.

4 Example #

--drop table column_sets 
create table column_sets
(
	sparse1 int primary key
,	sparse2 int sparse null
,	sparse3 nvarchar(20) sparse null
,	cs xml column_set FOR ALL_SPARSE_COLUMNS
);

insert column_sets(sparse1, sparse2, sparse3) values
	(1, 1, N'願唄覘?')
,	(2, null, N'覘螳襷?');

select * from column_sets;
select sparse1, sparse2, sparse3, cs from column_sets
column_set01.jpg
column_set02.jpg

CREATE TABLE t (
	i int SPARSE
, cs xml column_set FOR ALL_SPARSE_COLUMNS
);

INSERT t(cs) VALUES ('<i/>');
SELECT i FROM t;
SELECT * FROM t;
column_set03.jpg

5 谿瑚襭 #

蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

襾碁 螻 螳. 螳曙 エ 覦レ 讌襷 ′朱り 貅譽覃 覃襴 . (襦 貉)