SQL studies similar data types

The most common numeric type is int, but it is not necessarily the best choice. Bigint, smallint, and tinyint can be used in special scenarios. Their features are shown in the following table:

Data type	Range	Storage
Bigint	-2 ^ 63 (-9,223,372,036,854,775,808) to 2 ^ 63-1 (9,223,372,036,854,775,807)	8 Bytes
Int	-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647)	4 Bytes
Smallint	-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767)	2 Bytes
Tinyint	0 to 255	1 Byte

It can be seen that if a column has only 10 values and you use an int value, no data row occupies three bytes. If you have 1 GB of rows, you can use 3 GB of space. If it is still in index, OK, it occupies 3 GB of unnecessary resources.

Similar to Float and Real, their formats are as follows:

Data type	Range	Storage
Float	-1.79E + 308 to-2.23E-308, 0 and 2.23E-308 to 1.79E + 308	Depends on the valueN 1, 24 = 4 bytes 25, 53 = 8 bytes
Real	-3.40E + 38 to-1.18E-38, 0 and 1.18E-38 to 3.40E + 38	4 Bytes

It should be noted that the float length depends on a parameter. Although we usually do not specify the float length when declaring it, such an option actually exists. The default value is 53. The value range is shown in the preceding table.

Obviously, if the number you want to represent will not exceed the power 38 of 10, you should use real, which can save 4 bytes for you.

Another related example is decimal and numerical. They are another set of exact numeric types. Float and real are inaccurate, so they cannot be compared equally.

The functions of decimal and float are equivalent. I don't know why there are two names.

Decimal statement: decimal (p, s), p indicates the total number of digits, including the number before and after the decimal point, s indicates the number of digits after the decimal point, p cannot exceed 38, of course, s cannot exceed p. The default value is 0.

The decimal range is the result of the power of p-s minus the power of-s of 10. For example, the range of decimal (2, 1) is plus or minus 9.9.

The length of decimal is related to the size of p, as shown in the following table:

Precision	Storage bytes
1-9	5
10-19	9
20-28	13
29-38	17

Variable-length bytes can be used to store decimal. In this case, the database stores the decimal value based on the actual value, rather than the declaration. If the table does not have a column named var length, two additional bytes are required. Otherwise, no additional requirements are required.

Run the following command to start a variable-length decimal storage in a database:

EXEC sp_db_vardecimal_storage_format 'adventureworks', 'on ';

Use EXEC sp_db_vardecimal_storage_format; to view all databases that have enabled this function.

EXEC sp_tableoption 'sales. SalesOrderDetail ', 'vardecimal storage format', 1;

Start variable-length decimal storage for a table.

Finally, let's take a look at datetime and smalldatetime, both of which indicate time. The difference is as follows:

Datetime is expressed for a longer time, 1753 to 9999, with a higher precision. It takes up to 8 bytes for one second;

Smalldatetime is expressed in a short time, 1900 to 2079, precise to the minute, 4 bytes.

Generally, smalldatetime is sufficient.