This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Download Microsoft Edge
More info about Internet Explorer and Microsoft Edge
Analytics Platform System (PDW)
SQL Endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Exact-number data types that use integer data. To save space in the database, use the smallest data type that can reliably contain all possible values. For example, tinyint would be sufficient for a person's age because no one lives to be more than 255 years old. But tinyint would not be sufficient for a building's age because a building can be more than 255 years old.
Data type
Range
Range expression
Storage
The
int
data type is the primary integer data type in SQL Server. The
bigint
data type is intended for use when integer values might exceed the range that is supported by the
int
data type.
bigint
fits between
smallmoney
and
int
in the data type precedence chart.
Functions return
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
.
Caution
When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of
int
,
smallint
,
tinyint
, or
bigint
constant values to the
float
,
real
,
decimal
or
numeric
data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.
Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to
numeric
, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to
numeric (1, 0)
, and the constant value 250 is converted to
numeric (3, 0)
.
When a query is autoparameterized, the constant value is always converted to
numeric (10, 0)
before converting to the final data type. When the / operator is involved, not only can the result type's precision differ among similar queries, but the result value can differ also. For example, the result value of an autoparameterized query that includes the expression
SELECT CAST (1.0 / 7 AS float)
, differs from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query, are truncated to fit into the
numeric (10, 0)
data type.
The
tinyint
data type is not supported in Microsoft Fabric.
Converting integer data
When integers are implicitly converted to a character data type, if the integer is too large to fit into the character field, SQL Server enters ASCII character 42, the 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 the threshold value is exceeded, the data type of the result changes from an
int
to a
decimal
.
SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ;
Here is the result set.
Result1 Result2
1073741823 1073741824.500000
Examples
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 TABLE dbo.MyTable
MyBigIntColumn BIGINT
,MyIntColumn INT
,MySmallIntColumn SMALLINT
,MyTinyIntColumn TINYINT
INSERT INTO dbo.MyTable VALUES (9223372036854775807, 2147483647,32767,255);
SELECT MyBigIntColumn, MyIntColumn, MySmallIntColumn, MyTinyIntColumn
FROM dbo.MyTable;
Here is the result set.
MyBigIntColumn MyIntColumn MySmallIntColumn MyTinyIntColumn
-------------------- ----------- ---------------- ---------------
9223372036854775807 2147483647 32767 255
(1 row(s) affected)
See also
ALTER TABLE (Transact-SQL)
CAST and CONVERT (Transact-SQL)
CREATE TABLE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)
SET @local_variable (Transact-SQL)
sys.types (Transact-SQL)
