Maximum value for Unique ID

Hi,
Am a newbie to sql server so apologies if this question seems a bit
simple but here goes anyway. All our tables are defined with a unique
id column with the identity attribute. What is the maximum value for
this identity column and what happens if this maximum value is reached?
Many thanks in advance
Lee.It depends what type the column was declared as. IDENTITY is a property,
not a type. There is an underlying column type - it could be an integer
type (tinyint, smallint, int, bigint) or a decimal type with scale 0
(decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
of the declared type, you will get an overflow error.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> Hi,
> Am a newbie to sql server so apologies if this question seems a bit
> simple but here goes anyway. All our tables are defined with a unique
> id column with the identity attribute. What is the maximum value for
> this identity column and what happens if this maximum value is reached?
>
> Many thanks in advance
> Lee.
>|||Many thanks for the response steve.
Just to make sure I understand then, if our column that has the identity
attribute is declared as int and the value tries to go above
2,147,483,647 an overflow error will be returned, rather than sql going
back through using any gaps.
Many thanks
Lee.
Steve Kass wrote:
> It depends what type the column was declared as. IDENTITY is a property,
> not a type. There is an underlying column type - it could be an integer
> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
> of the declared type, you will get an overflow error.
> SK
>
> "monkey" <monkey@.email.com> wrote in message
> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>>Hi,
>>Am a newbie to sql server so apologies if this question seems a bit
>>simple but here goes anyway. All our tables are defined with a unique
>>id column with the identity attribute. What is the maximum value for
>>this identity column and what happens if this maximum value is reached?
>>
>>Many thanks in advance
>>Lee.
>
>|||correct - if you want to see it in action, use dbcc checkident and reseed
with the max value. Note, however, that you can use negative values (in
case you start with 1 as the original seed)
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Yes. The identity property simply generates sequential values. Whether a
value generated ends up in the table or not is irrelevant, as is the actual
data in the table. The identity property does guarantee uniqueness or that
there will be no gaps.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Many thanks for all the responses, with your help I think I have a
pretty good understanding now.
Thanks again
lee.
monkey wrote:
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>> It depends what type the column was declared as. IDENTITY is a property,
>> not a type. There is an underlying column type - it could be an integer
>> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
>> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
>> range
>> of the declared type, you will get an overflow error.
>> SK
>>
>> "monkey" <monkey@.email.com> wrote in message
>> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>> Hi,
>> Am a newbie to sql server so apologies if this question seems a bit
>> simple but here goes anyway. All our tables are defined with a unique
>> id column with the identity attribute. What is the maximum value for
>> this identity column and what happens if this maximum value is reached?
>>
>> Many thanks in advance
>> Lee.
>>
>>
>