SQL research similar data types _mssql

The most commonly used numeric type is int, but it is not necessarily the best choice. Bigint,smallint,tinyint can be applied to special occasions. 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 is obvious that if a column has a value of only 10 or more, and you use int, then no row of data occupies 3 bytes. If you have 1G of rows, then you use 3G more space. If it's still in index, OK, it takes up an unnecessary 3G.

Similar to the float and real, their format is 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 value of n 1,24=4 bytes 25,53=8bytes
Real	-3.40E + to-1.18e-38, 0 and 1.18E-38 to 3.40E + 38	4 Bytes

It should be explained that the length of float depends on a parameter, although we usually do not specify the length of float, there is actually such an option. This length defaults to 53 and the range of values is shown in the table above.

Obviously if the number you are trying to represent does not exceed 10 of 38, you should use real and he can save you 4 bytes.

Another related example is decimal and numerical, which are another set of types that accurately represent numeric values. float and Real are imprecise, so you can't make equal comparisons.

Decimal and float are equivalent in function, why should be divided into two names, I do not know.

The declaration of decimal is as follows: Decimal (P,s), p represents 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 can not exceed 38, of course, s can not exceed p, the default is 0.

The range of decimal is the p-s of plus or minus 10 minus 10 of the S-second party. 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

You can use variable-length bytes to store decimal. At this point, the database stores decimal based on the size of the actual value, rather than on a declaration basis. If a column with var length is not in the table, an additional two bytes is required, otherwise there is no additional need.

You can start a variable-length decimal store on a database by using the following command:

EXEC sp_db_vardecimal_storage_format ' AdventureWorks ', ' on ';

Through exec sp_db_vardecimal_storage_format, view all databases that have started this feature.

Through exec sp_tableoption ' sales.salesorderdetail ', ' vardecimal storage format ', 1;

Starts a variable-length decimal store for a table.

Finally look at the datetime and smalldatetime, both of which indicate time, the difference is as follows:

The datetime expression time is longer, 1753 to 9999, the precision is higher, 300 cent one second, occupies 8 bytes;

smalldatetime expression time is shorter, 1900 to 2079, accurate to cent, 4 bytes.

Usually, the smalldatetime is sufficient.