Identity column.

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?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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_3grn.asp)

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 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_6dyq.asp)|||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|||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?|||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

:)|||:cool: This is how the math is done on computers:

Int type is 4 bytes, which is 4*8=32 bits, which means 2^31~= :shocked: 2Gigs(10^9):shocked: 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 ~=:shocked: 8*10^18:shocked: unique numbers.

:angel: have fun|||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.|||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!