Reputation: 2753
I would like to know if defining a column as tiny int vs int matter when page compression is on?
Upvotes: 2
Views: 345
Reputation: 300837
Define your column type to match the domain model. If the domain requires an int
then use an int
. Then worry about compression.
Upvotes: 3
Reputation: 252
Traditionally every bit saved on the page size would mean a little bit of speed improvement: narrower rows means more rows per page, which means less memory consumed and fewer IO requests, resulting in better speed. However, with SQL Server 2008 Page compression things start to get fuzzy. The compression algorithm may compress 4 byte ints with values under 255 on even less than a byte.
Row compression algorithms will store a 4 byte int on a single byte for values under 127 (int is signed), 2 bytes for values under 32768 and so on and so forth.
However, given that the nice compression features are only available on Enterprise Edition servers, it makes sense to keep the habit of using the smallest possible data type.
Upvotes: 4