Is there any advantage when setting tinyint fields when I know that the value will not exceed 255?

Question

Should I choose the smallest data type, or, for example, I save the value 1, it does not matter what the data type is col, and the value will occupy the same memory size?

The question is also that I will always need to convert it and play in the application.

---

UPDATE

I think varchar (1) and varchar (50) are the same memory sizes, if the value is "a", I thought it was the same with int and tinyint, according to the answers that I don't understand, right?

Answer 1

Always choose the smallest data type. SQL cannot guess what you want the maximum value to be, but it can optimize storage and performance as soon as you specify the data type.

---

To respond to your update:

varchar takes up only as much space as you use, and therefore you are right when you say that the character “a” will occupy 1 byte (in Latin encoding), no matter how large the varchar field is your choice. This does not apply to any other type of field in SQL.

However, you are likely to sacrifice space efficiency if you make the entire varchar field. If everything is a fixed-size field, then SQL can do simple constant-time multiplication to find your value (like an array). If you have varchar fields, then the only way to find out where the data is stored is to look at all previous fields (for example, a linked list).

If you are starting SQL, then I advise you to just stay away from varchar fields unless you expect to have fields that sometimes have very little text and sometimes a very large amount of text (like blog posts). It takes experience to know when to use variable length fields for the best effect, and I don’t even know most of the time.

Answer 2

This is a performance consideration, especially for the design of your system. In general, the more data you can enter into the Sql Server data page, the better the performance.

One page on Sql Server - 8k. Using tiny ints instead of ints will allow you to put more data in one page, but you need to think about whether to do it. If you are going to service thousands of hits per minute, then yes. If this is a hobby project or something that only a few dozen users will ever see, then it does not matter.

Answer 3

There is an advantage, but it can be insignificant if you have many lines and does not work. There will be better performance and less memory.

Answer 4

Traditionally, each bit saved on page size will mean a slight improvement in speed: narrower lines mean more lines per page, which means less memory consumption and fewer I / O requests, resulting in better speed. However, with SQL Server 2008 Page Compression, things are starting to get fuzzy. The compression algorithm can compress 4 byte intervals with values ​​up to 255 by less than bytes.

string compression algorithms will store 4 int bytes in one byte for values ​​below 127 (int is signed), 2 bytes for values ​​under 32768, etc. etc.

However, given that good compression features are available only on Enterprise Edition servers, it makes sense to maintain the habit of using the smallest data type possible.