CODEWITHSUNDEEP
sqlsql-serverdatabase
Steam
Steam

Reputation: 9856

Understanding the concept of simple row compression in SQL server

I read that the row compression feature of SQL server reduces the space needed to store a row by only using the bytes required to store a given value. Without compression an int column needs 4 bytes. We need the whole 4 bytes only if we are storing the number 1 or 1 million. With row compression turned on, sql server looks at the actual value being stored and calculates the amount of storage needed.

What I don't understand - Why does 1 and 1mn need the full 4 bytes and why do the other numbers, bigger than 1 require lesser memory ?

EDIT - It is taken from the book: Delivering business intelligence with SQL server 2008

Row compression reduces the space required to store a row of data. It does this by only using the bytes required to store a given value. For example, without row compression, a column of type int normally occupies 4 bytes. This is true if we are storing the number 1 or the number 1 million. SQL Server allocates enough space to store the maximum value possible for the data type. When row compression is turned on, SQL Server makes smarter space allocations. It looks at the actual value being stored for a given column in a given row, and then determines the storage required to represent that value. This is done to the nearest whole byte. Of course, this added complexity adds some overhead as data is inserted or updated in the table. It also adds a smaller amount of overhead when data is retrieved from that table. In most cases, the time taken for this additional processing is negligible. In fact, in some cases, the saving in disk read and disk write time can even be greater than the calculation time required for data compression.

Upvotes: 0

Views: 200

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

1 and 1 million are just examples of values that require 4 bytes if row compression is off. It does not mean that the other numbers require less space.

1 and 1 million was probably picked out as examples because 1 is small and 1 million is big.

An int will take 4 bytes regardless of what value it has if row compression is off.

Upvotes: 1

Endrju
Endrju

Reputation: 2436

You're having wrong perception of this. Please read the docs thoroughly.

Upvotes: 2

Related Questions