int, bigint, smallint, and tinyint (Transact-SQL)

Source: Internet
Author: User
Tags arithmetic arithmetic operators truncated

An exact numeric data type that uses integer data.

Data type

Range

Store

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 bytes

notes int data type is the primary integer data type in SQL Server." The >int data type is the primary integer data type in SQL Server. bigint data type is intended for use when an integer values might exceed the range that's supported by the int data type. " > bigint data type is used for integer values that may exceed int data type support range.

bigint fits between smallmoney and int in the The data type precedence chart. In the Data type prioritization table, bigint between smallmoney and int.

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

Note

Use the + 、-、 *,/, or% arithmetic operators towhen an int,smallint,tinyint, or bigint constant value is implicitly or explicitly converted to a float,Real,decimal, or numeric data type, the SQL The rules that are applied when the Server calculates the precision of the data type and expression results vary, depending on whether the query is automatically parameterized.

Therefore, 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 large 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 the final data type before it is converted to theNumeric (10, 0). if the/operator is involved, then for similar queries, not only the precision of the result type may be different, but the resulting value may also be different. For example, an automatic parameterized query that contains an expression SELECT CAST (1.0/7 as float) will have a different result value than the result value of the same query that is not automatically parameterized, because the results of an 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, which is the asterisk (*).

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

SELECT 2147483647 / 2  as 2147483649 / 2  as RESULT2;

Here is the result set:

RESULT1      Result21073741823   1073741824.500000

Example

The following example creates a table using the bigint,int,smallint, and tinyint data types. values are inserted into each column and returned in the SELECT statement.

CREATE TABLEdbo. MyTable (Mybigintcolumnbigint, Myintcolumnint, Mysmallintcolumnsmallint, Mytinyintcolumntinyint);GOINSERT  intoDbo. MyTableVALUES(9223372036854775807,214483647,32767,255); GOSELECTMybigintcolumn, Myintcolumn, Mysmallintcolumn, Mytinyintcolumn fromDbo. MyTable;

Here is the result set:

Mybigintcolumn       Myintcolumn mysmallintcolumn mytinyintcolumn-------------------------------------------------- ------------9223372036854775807  214483647   32767            255 (1 row (s) affected)

int, bigint, smallint, and tinyint (Transact-SQL)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.