1. Registered User
Join Date
Aug 2005
Location
Sharjah, UAE
Posts
25

Kudos to y'all! On most of my tables, I'm using a column of data type numeric and properties set as Identity with no replication. Now I know that it can handle only a length of 9 and obviously not nullable. Now by length of 9, does it mean in bytes or literal digits. If it is, then that would mean that it would reach only up to 999,999,999. What happens then if it reaches that digit?

2. L O S T in Reality
Join Date
Nov 2005
Location
San Francisco, CA
Posts
506
Originally Posted by trojanz
Kudos to y'all! On most of my tables, I'm using a column of data type numeric and properties set as Identity with no replication. Now I know that it can handle only a length of 9 and obviously not nullable. Now by length of 9, does it mean in bytes or literal digits. If it is, then that would mean that it would reach only up to 999,999,999. What happens then if it reaches that digit?
Numeric datatype ...
decimal and numeric
Numeric data types with fixed precision and scale.

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

--microsoft

You can see its larger than what you think.
You can use uniqueidentifier (A globally unique identifier (GUID)) in this case where you can be sure that the above problem will never arise for time being.
But I think after the year 9999 ,those data can have duplicate values.

uniqueidentifier

3. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
if you use INTEGER, you can store up to 2 billion (which is a bit bigger than 999,999,999)

if that's not big enough, use BIGINT

4. Registered User
Join Date
Aug 2005
Location
Sharjah, UAE
Posts
25
Originally Posted by r937
if you use INTEGER, you can store up to 2 billion (which is a bit bigger than 999,999,999)

if that's not big enough, use BIGINT
Thanks for the tip r937. I tried your suggestion but it turns out that with INT data type and set as identity column, the length is only 4 whilst the BIGINT data type gives me only 8. So that would mean...

NUMERIC-> 999999999
INT-> 9999
BIGINT-> 99999999

...or am I lookin' at it the wrong way?

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
yes, you are looking at it the wrong way

INT stores numbers from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647)

BIGINT stores numbers from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807)

look it up in Books OnLine

6. Registered User
Join Date
Apr 2006
Location
System.Web.UI.WebControls.Home.BedRoom2
Posts
42

## Wake Up

This is how the math is done on computers:

Int type is 4 bytes, which is 4*8=32 bits, which means 2^31~= 2Gigs(10^9) unique numbers
(I think a bit is take off for +/-)

BigInt type is 8 bytes, which is 8*8=64 bits, which means 2^63 ~= 8*10^18 unique numbers.

have fun

7. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
In short, the length of the datatype is the number of bytes it requires. Character data is 1 byte per character (2 for unicode), so the length of the datatype equals the length of the string. Numeric datatypes are stored in binary, as Spere explains above.

8. Registered User
Join Date
Aug 2005
Location
Sharjah, UAE
Posts
25
Thanks guys for the explanation. So the length doesn't necessary mean the number of digits/characters but the number of bytes. Interesting. I'll look it up for some more on BOL. Thanks again guys!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•