Does the size of the database depend on the amount of data stored in the column or on the type of data stored?

E.g: if I have Varchar column and I'm just typing like word (Hello) in it, it will take the same size if I write 255 characters in it?


For your specific case the size on disk will be longer if you type more characters. This is because you defined your column as VARCHAR i.e. "varying-length character". Varying-length fields have to indicate the actual number of character present so they have an additional internal filed to show this. Therefore even empty strings consume some space when held in VARCHAR. If the column had been declared as CHAR the number of characters typed would not affect the space used. The remainder would be filled with spaces.

When looking at the bigger picture things get more complicated. Disk space is only ever consumed in chunks, often called "pages." One page is the minimum amount of disk the DB can request from the operating system and is also the minimum amount that can be read from or written to disk. Defining a table with one column of CHAR(1) and a single row will still consume a whole page of disk, typically 16kB.

Further, compression can be applied. Then the amount of space used depends on the length of the source strings and how similar they are to other strings held on that page. To be clear, the page size is still fixed (16kB) but more rows can be held on that one page compared to uncompressed storage. Then a single IO (which is typically the slowest step in query processing) will return more data, fewer IOs will be needed overall and the average query latency will reduce.

When designing tables it is best to declare the smallest size that will satisfy the actual business requirement. Don't declare them too short because those limits are hard limits and data truncation will occur. But definitely don't get lazy and use the maximum size just because it is easy. The declared sizes affect how many rows can fit on a page and so how many IOs are needed to satisfy a query. They can also affect memory usage, potentially reserving space for data which isn't actually present. Overly-generous size declarations can affect performance.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.