When is space be allocated in databases? I came from the developer-side, not the administration.
But here and there I have to choose the datatype when I add a new column in our server (MS SQL or MySQL).

I want to add a column for numbers, so my choice is int or tinyint. At the moment I choose tinyint just because I know I want to store a value between 0 and 100 (a percent value) and it can't be higher.

But is there a difference for the space needed on HDD?
Basically: When is space allocated in databases?  

When I create a nullable column for tinyint instead of int (1 byte instead of 4 bytes), and I insert a row of data with NULL for this column, is there still space allocated? Or just when I enter a number?

When I create a int column and just save a 1 into it, is there still 4 bytes allocated?
(This Answer addresses MySQL with ENGINE=InnoDB.)

Your specific question:  Use TINYINT UNSIGNED instead of INT to save 3 bytes per row (on disk and in cache).  Do it.

Basic guidelines (and TMI):

All numeric types (at least for NOT NULL) are fixed length, regardless of the value stored in them:


TINYINT - 1 byte
INT - 4 bytes
DOUBLE - 8 bytes
DECIMAL(m,n) about m/2 bytes.  (The exact number of bytes is complicated.)


SIGNED vs. UNSIGNED has not impact on space.  Instead, it impacts the range for *INT types.  Eg, TINYINT SIGNED is -128..127; TINYINT SIGNED is 0..255.

NULL vs NOT NULL -- the space consumed is complex due to MVCC, potential for ROLLBACK, the storage in BTree structure, etc.  It is not worth thinking of NULL as a way to save space.  Decide between NULL vs NOT NULL based on business logic, not space.  OK, you could let 0 or '' be used instead of NULL.  But, again, the space difference is so minuscule and complex to compute that it is not worth it. 
 I think that NULL vs NOT NULL either matters or does not, depending on the ROW_FORMAT.

Make the decision between a 4-byte INT and a 1-byte TINYINT (or other sizes) when you originally create the table; it is costly to change it later.  Be conservative; maybe there are only 10K thingies today, but if the landscape changes, might you exceed the 64K limit of SMALLINT UNSIGNED tomorrow?

Most database data can be UNSIGNED.

String types (VARCHAR, TEXT, BLOB, etc) are typically variable length, with a 1- or 2-byte length prepended to the space.

CHAR(10) CHARACTER SET utf8mb4 NOT NULL takes 10 to 40 bytes, plus at least 1 byte overhead.  VARCHAR(10) CHARACTER SET utf8mb4 (NULL or NOT NULL) takes 0 to 40 bytes, plus at least 1 byte overhead.  (There may be ROW_FORMAT dependencies here, too.)  TEXT, VARBINARY, and BLOB act similar to VARCHAR.

Priority #1:  Business logic
Priority #2:  Space