Using bigint Data
The bigint data type is intended for use in cases where integer values might exceed the range supported by the int data type. For compatibility, the int data type remains the primary integer data type in Microsoft SQL Server 2005.
Unless explicitly stated, functions, statements, and system stored procedures that accept int expressions for their parameters have not been changed to support implicit conversion of bigint expressions to those parameters. Thus, SQL Server only implicitly converts bigint to int when the bigint value is within the range supported by the int data type. A conversion error occurs at run time if the bigint expression contains a value outside the range supported by the int data type.
bigint in Transact-SQL Functions
Although SQL Server sometimes promotes tinyint or smallint values to int data type, it will not automatically promote tinyint, smallint, or int to bigint. For example, if the data type of the parameter expression is tinyint or smallint, certain aggregate functions promote the data type of the return value to an int. These aggregate functions will not return a bigint unless the parameter expression is of type bigint.
When you specify bigint parameters and the return values are of type bigint, you may use the following Transact-SQL functions.
ABS |
FLOOR |
POWER |
AVG |
IDENTITY |
RADIANS |
CEILING |
MAX |
ROUND |
COALESCE |
MIN |
SIGN |
DEGREES |
NULLIF |
SUM |
When you reference bigint columns or variables, but you are not looking for bigint data types for return values, you may use these functions.
@@IDENTITY |
ISNULL |
VARP |
COL_LENGTH |
ISNUMERIC |
|
DATALENGTH |
STDEV[P] |
|
SQL Server provides these functions specifically for use with bigint values.
- COUNT_BIG
Use when counting the number of items in a group if the value exceeds the range supported by the int data type, and returns bigint. COUNT_BIG is like the COUNT function except for the return type.
- ROWCOUNT_BIG
Use when counting the number of rows affected in the last statement executed and the value exceeds the range supported by the int data type. This function is similar to the ROWCOUNT function, except that ROWCOUNT_BIG returns a bigint data type.
bigint in Other Transact-SQL Elements
The CAST and CONVERT clauses support bigint. These clauses apply similar conversion rules for bigint as for the other integer data types. The bigint data type fits above int and below smallmoney in the data type precedence chart. For more information about bigint conversions, see CAST and CONVERT (Transact-SQL).
When using the CASE expression, you will get a result of type bigint if the result_expression or the optional else_result_expression evaluate to bigint.
You may use the bigint data type in all syntax locations where integer data types are specified in these Transact-SQL statements:
- ALTER PROCEDURE
- ALTER TABLE
- CREATE PROCEDURE
- CREATE TABLE
- DECLARE variable
In addition, the SQL Server catalog components report information about bigint columns.
Specifying bigint Constants
Whole number constants that are outside the range supported by the int data type continue to be interpreted as numeric, with a scale of 0 and a precision sufficient to hold the value specified. For example, the constant 3000000000 is interpreted as numeric. These numeric constants are implicitly convertible to bigint and can be assigned to bigint columns and variables:
CREATE TABLE BigintTable (ColA bigint)
INSERT INTO BigintTable VALUES (3000000000)
SELECT *
FROM BigintTable
WHERE ColA = 3000000000
You can also cast constants to bigint:
CAST(3000000000 AS bigint)
To get a bigint value into an sql_variant column, use this method:
CREATE TABLE VariantTable (ColA sql_variant)
-- Inserts a value with a numeric base data type.
INSERT INTO VariantTable VALUES (3000000000)
-- Inserts a value with a bigint base data type.
INSERT INTO VariantTable VALUES ( CAST(3000000000 AS bigint) )
See Also
Other Resources
Data Types (Transact-SQL)
CASE (Transact-SQL)
CAST and CONVERT (Transact-SQL)
COUNT_BIG (Transact-SQL)
int, bigint, smallint, and tinyint (Transact-SQL)
sql_variant (Transact-SQL)