Differences between tinyint, smallint, Int, and bigint types in SQL Server databases

1. Type comparison

Bigint: integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807). The storage size is 8 bytes. One byte is 8 bits, so bigint has 64 bits

INT: integer data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647). The storage size is 4 bytes. Int type, which can store up to 32 bits of data

Smallint: integer data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767). The storage size is 2 bytes. Smallint has 16 digits.

Tinyint: integer data from 0 to 255. The storage size is 1 byte. Tinyint has eight digits.

Ii. Notes

The bigint data type is supported where integer values are supported. However, bigint is used in some special cases. When the integer value exceeds the range supported by the int data type, bigint can be used. In SQL Server, the int data type is the main integer data type.

In the data type precedence table, bigint is located between smallmoney and Int.

The function returns bigint only when the parameter expression is of the bigint data type. SQL Server does not automatically upgrade other integer data types (tinyint, smallint, and INT) to bigint.

Iii. Summary

Therefore, it is very important to select the most appropriate data type. For example, if you have 10 18 digits, all are digital ID card numbers, and 10 15 digits are all digital ID card numbers. Let you find the 20 records at the fastest query speed. When designing this field, you should pay attention to the following points:

1. The fastest query speed is nothing more than using int data to store the 20 ID card numbers. However, the 15-bit and 18-bit data seems to only meet the bigint condition.

2. Why not use varchar or Char. Directly save it as a string. The query speed of int is certainly faster than that of varchar and Char. Int type data is the first choice, but there are several int types in sqlserver. Select the most appropriate data type.