Database Reference
In-Depth Information
Table 4-8.
Space used by nonsparse and sparse columns
Data Type
Nonsparse storage
space (bytes)
Sparse storage space
when not null (bytes)
NULL percentage
bit
0.125
4.125
98%
tinyint
1
5
86%
smallint
2
6
76%
date
3
7
69%
time(0)
3
7
69%
int
4
8
64%
real
4
8
64%
smallmoney
4
8
64%
smalldatetime
4
8
64%
time(7)
5
9
60%
decimal/numeric(1,s)
5
9
60%
datetime2(0)
6
10
57%
bigint
8
12
52%
float
8
12
52%
Money
8
12
52%
datetime
8
12
52%
datetime2(7)
8
12
52%
datetimeoffset(0)
8
12
52%
datetimeoffset(7)
10
14
49%
uniqueidentifier
16
20
43%
decimal/numeric(38,s)
17
21
42%
Variable-length types
2 + avg data size
4 + avg data size
60%
Sparse columns allow the creation of wide tables with up to 30,000 columns. Some systems, for example
Microsoft SharePoint, use wide tables to store semistructured data.
Think about a table that stores different types of documents as an example. Each document type has its own
set of attributes/columns defined. Some attributes, for example
Document Number
and
Creation Date, e
xist in every
document type; while other are unique for a specific type.
If you decided to keep all documents in a single table, you could define common attributes as regular nonsparse
columns and document-type related attributes as sparse columns. That approach can significantly reduce table row
size in cases where a large number of attributes store null values.
