Presents your 
SQL SERVER E-NEWSLETTER for August 12, 2003

<------------------------------------------->

USE BIGINT WHEN INTEGERS EXCEED THE RANGE INT SUPPORTS

While int remains the primary integer data type in SQL Server 2000, the
new bigint data type was created for instances when integer values
exceed the int data range.

The int data type.covers whole number data that ranges from -2^31
through 2^31 -1. This means that you can use the int data type for numbers that
range from -2,147,483,648 to 2,147,483,647. Another way to remember int
is that it covers a little over two billion data on either side of the
zero. An int requires 4 bytes of storage.

Bigint, on the other hand, will hold whole number exact data that ranges
between -2^63 through 2^63 -1 or -9,223,372,036,854,775,808 through
9,223,372,036,854,775,807. Bigints require 8 bytes of storage.

Here are several points to keep in mind when you're using bigint:

* Bigint fits between small money and int in the data type precedence chart.

* SQL Server doesn't automatically convert an int into a bigint.

* Functions ONLY return a bigint if the parameter expression is a bigint
data type. Some of the functions that use bigint include AVG, CEILING,
FLOOR, MAX, MIN, ROUND, and SUM.

* You may use bigint in all syntax locations where integer data types
are specified in the following statements: ALTER PROCEDURE, ALTER TABLE,
CREATE PROCEDURE, CREATE TABLE, and DECLARE variable.

* You can get information about bigint columns by using the SQL Server
catalog components.

You might be wondering why anyone would need to use the bigint data
type. One instance in which you might use it is if you work with a database
that performs a large number of calculations such as those done by
credit card companies.

J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about
technology for more than two decades.

----------------------------------------