CODEWITHSUNDEEP
sqlsql-server
Sriram Subramanian
Sriram Subramanian

Reputation: 2753

does int vs tinyint matter when page compression is on in SQL Server 2008?

I would like to know if defining a column as tiny int vs int matter when page compression is on?

Upvotes: 2

Views: 345

Answers (2)

Mitch Wheat
Mitch Wheat

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

AbhiRoczz...
AbhiRoczz...

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

Related Questions