SQL Server data types int, bigint, smallint, and tinyint ranges

Environment: SQL Server 2008 R2

An exact numeric data type that uses integer data.

bigint

Integer data (all numbers) from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807). The storage size is 8 bytes.

Int

Integer data (all numbers) from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). The storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) to 2^15-1 (32,767). The storage size is 2 bytes.

tinyint

Integer data from 0 to 255. The storage size is 1 bytes.

Comments

Supports bigint data types where integer values are supported. However, bigint is used in some special cases where the integer value exceeds the range supported by the INT data type, and the bigint can be used. In SQL Server, the int data type is the primary integer data type.

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

A function returns bigint only if the parameter expression is a bigint data type. SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

Attention

SQL Server calculates data types and expression knots when an int, smallint, tinyint, or bigint constant value is implicitly or explicitly converted to a float, real, decimal, or numeric data type using arithmetic operators such as + 、-、 *,/or% The precision of the fruit applies differently, depending on whether the query is automatically parameterized.

As a result, similar expressions in a query can sometimes produce different results. If the query is not automatically parameterized, the constant value is converted to numeric before it is converted to the specified data type, and the data type is highly accurate enough to hold the value of the constant. For example, a constant value of 1 is converted to numeric (1, 0), and a constant value of 250 is converted to numeric (3, 0).

If the query is automatically parameterized, the constant value is always converted to numeric (10, 0) before it is converted to the final data type. If the/operator is involved, then for a similar query, not only the precision of the result type may be different, but the resulting value may also be different. For example, the result value of an automatic parameterized query that contains the expression SELECT CAST (1.0/7 as float) will be different from the result value of the same query that is not automatically parameterized, because the results of the automatic parameterized query will be truncated to fit the numeric (10, 0) data type.

Converting integer data

When an integer is implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters the ASCII character 42, an asterisk (*).

Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type. The following example shows that when this threshold is exceeded, the data type of the result becomes decimal from int.

SELECT2147483647/2 as RESULT1, 2147483649/2 as RESULT2;

Here is the result set:

RESULT1 RESULT2

1073741823 1073741824.500000

Summarize

The choice of data type depends on the actual situation, it is best to meet the demand. Do not overkill, because the waste of space will be very serious, efficiency will also be affected by certain.