Not all datatypes are eligible for data compression.
Datatype |
Length, in Bytes |
Compression Type |
|---|---|---|
bigint |
8 |
Row and page dictionary |
int |
4 |
Row and page dictionary |
smallint |
2 |
Page index |
tinyint |
1 |
Page index |
ubigint |
8 |
Row and page dictionary |
unsigned int |
4 |
Row and page dictionary |
unsigned smallint |
2 |
Page index |
Datatype |
Length, in Bytes |
Compression Type |
|---|---|---|
numeric (precision, scale) |
User-specified |
Row and page dictionary |
decimal (precision, scale) |
Datatype |
Length, in Bytes |
Compressed? |
Compression Type |
|---|---|---|---|
float (precision) |
4 bytes if precisions < 16, 8 if ≥ 16 |
No |
N/A |
double precision |
8 |
||
real |
4 |
Datatype |
Length, in Bytes |
Storage format |
Compressed? |
Compression Type |
|---|---|---|---|---|
money |
8 |
Two 4-byte values: one signed int and the other an unsigned int |
Yes |
Row and page dictionary |
smallmoney |
4 |
1 signed 4-byte integer |
Datatype |
Length, in Bytes |
Storage Format |
Compressed? |
Compression Type |
|---|---|---|---|---|
bigdatetime |
8 |
Represented as an unsigned 64-bit integer. Using a base date of 1/1/0001, bigdatetime holds the number of microseconds between midnight of the base date and a point in time. Stores fractions of a second to 6 decimal places. |
Yes |
Row and page dictionary |
bigtime |
8 |
8-byte unsigned integer holding the number of microseconds since midnight. Stores fractions of a second to 6 decimal places. |
||
date |
4 |
Stores the number of days, backward or forward, from January 1, 1900. |
||
datetime |
8 |
Two 4-byte parts. First part stores the number of days forward or backward from 1/1/1900. Second part stores the number of 1/300th seconds since midnight. |
Yes |
Page dictionary (date portion) and row compressed (time portion) |
smalldatetime |
4 |
Two 2-byte unsigned smallint values. First stores the number of days since 1/1/1900. Second stores the number of minutes since midnight. |
No |
N/A |
time |
4 |
The number of milliseconds since midnight. |
Yes |
Row page dictionary |
Datatype |
Length, in Bytes |
Storage Format |
Compressed? |
Compression Type |
|---|---|---|---|---|
char(n) |
User-specified |
Single or multiple byte or character stream, depending on the character type |
Yes |
Row, page dictionary if length ≥ 4. Page index, if length < 4. |
unichar(n) |
||||
nchar(n) |
||||
varchar(n) |
Yes |
Page dictionary if length ≥ 4. Page index, if length < 4. |
||
univarchar(n) |
||||
nvarchar(n) |
Datatype |
Length, in Bytes |
Storage Format |
Compressed? |
Compression Type |
|---|---|---|---|---|
binary(n) |
User-specified |
Byte stream |
Yes (length ≥ 4) |
Row, page dictionary if length ≥ 4. Page index, if length < 4. |
varbinary(n) |
User-specified |
Yes (length ≥ 4) |
Page dictionary if length ≥ 4. Page index, if length < 4. |
Datatype |
Length, in Bytes |
Storage Format |
Compressed? |
Compression Type |
|---|---|---|---|---|
bit |
No |
N/A |
||
timestamp |
8 |
Byte stream; binary data |
No |
N/A |
xtype_token |
User-specified |
No | N/A | |
text pointer |
16 bytes of binary data |
Byte stream. 8 bytes of RID, 8 bytes of first text page's database timestamp value. |
No | N/A |