Doubt about data type database

Question

See this table of INT data types in SQL SERVER:

tinyint  | 0 a 255                                        | 1 byte
smallint |-2^15 (-32.768) a 2^15-1 (32.767)               | 2 bytes
int      |-2^31 (-2.147.483.648) a 2^31-1 (2.147.483.647) | 4 bytes

About the third column (Storage) says that a field of type INT occupies 4 bytes. But it means any INSERT that I give and fill this field it will consume 4 bytes? Regardless of the number, for example, "2" or "25874524" will still consume 4 bytes?

Answer 1

Yes, because you will use all 4 bytes to represent the number.

For example, following the writing order of the bits from right to left, this is how you write the following values as 4-byte integers:

0: 00000000 00000000 00000000 00000000
1: 00000000 00000000 00000000 00000001
128: 00000000 00000000 0000000 10000000
1025: 00000000 0000000 00000100 00000001

etc., etc. up to the highest possible value. Depending on the implementation (and unfortunately I don’t know the Mysql bit), the bit further away from the first indicates sign instead of a value (i.e.: 0 indicates +, 1 indicates -, or vice versa).

This may seem like a waste of space. But there are several reasons why it should be done this way. I will quote just what I consider the strongest: if the number of bytes in the numbers were variable, you would have to interpret a sequence of variable size to know the real value of the number. For example, if the quantity were variable, and I read the following byte:

00010000

How would I know if the value is 16, or some higher value added to 16? You’d have to read another byte, and another, and another, always testing for some stop sign.

But if you are sure that all your integers have a fixed amount of bytes, you can take that fixed amount of data from the disk first and read it later.

In general, reading multiple bytes of a single time from a device such as a disk, flash memory and even RAM memory is much faster than reading the same amount of bytes with more than one read operation. So, ultimately, the loss of space is justified by the gain in speed and the simplification of reading.