Identity Seed

have a table set one column as
data type = int
identity to yes
identity seed = 1 and identity increment = 1
after deleting some rows (e.g. 5), the number 5 will never be re used.
when the number has grown to the limit, can I still insert new rows?when you mention a limit, I am assuming you are talking about a check
constraint.
Regardless, identity column values are not necessary inserted in the order
of sequence, however the seed will always be no lower than the last
committed value.
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Music Lover" <music@.my-heart.org> wrote in message
news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||No he means what happens when the INT field gets to 0x7FFFFFFF (2147483647)
"Mark Broadbent" <nospamplease_mark.broadbent@.virgin.net> wrote in message
news:VnpWa.582$k4.11506@.news2.nokia.com...
> when you mention a limit, I am assuming you are talking about a check
> constraint.
> Regardless, identity column values are not necessary inserted in the order
> of sequence, however the seed will always be no lower than the last
> committed value.
>
> --
> BR,
> Mark Broadbent mcdba,mcse+i
> _________________________
> "Music Lover" <music@.my-heart.org> wrote in message
> news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> > have a table set one column as
> > data type = int
> > identity to yes
> > identity seed = 1 and identity increment = 1
> >
> > after deleting some rows (e.g. 5), the number 5 will never be re used.
> >
> > when the number has grown to the limit, can I still insert new rows?|||Little Test...
USE PUBS
CREATE TABLE dbo.tblIdentity ( ID INT IDENTITY(2147483640,1) CONSTRAINT
PK_tblIdentity PRIMARY KEY CLUSTERED, DATA CHAR(1) )
GO
INSERT INTO tblIdentity ( DATA )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
UNION ALL SELECT 'G'
UNION ALL SELECT 'H'
UNION ALL SELECT 'I'
UNION ALL SELECT 'J'
GO
SELECT * FROM tblIdentity
GO
DROP TABLE dbo.tblIdentity
The Insert query generates the following Error:-
Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
So to answer your question.
No you can't insert records when the INT field reaches its limit
(2147483647)
When this happens you will have to reseed the field to -2147483648.
This will give you a few million more records...
When you run out of numbers after going through the negative values it'll be
time to a) archive some data b) change the number to a GUID or c) change the
number to a BIGINT. But 2147million records is enough for most people ;)
DBCC CHECKIDENT ( tblIdentity , RESEED , -2147483648 )
"Music Lover" <music@.my-heart.org> wrote:
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||you can reset the identity seed to start incrementing from
your chosen number...
>--Original Message--
>have a table set one column as
>data type = int
> identity to yes
>identity seed = 1 and identity increment = 1
>after deleting some rows (e.g. 5), the number 5 will
never be re used.
>when the number has grown to the limit, can I still
insert new rows?
>
>.
>|||And bigint can store -9,223,372,036,854,775,808 through
9,223,372,036,854,775,807.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Tony Wilton" <tony@.scruffytiger.co.uk.NOSPAM> wrote in message
news:3f2a3824$0$12152$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you use an INT field yes.
> Maximum values for data types are:-
> TINYINT 255
> SMALLINT 32767
> INT 2147483647
>
> "Music Lover" <music@.my-heart.org> wrote:
> > Thanks for your reply.
> > Want to double confirm
> > I can insert 2147million records withou any problem?
>|||thanks
how?
"jano" <janobermudes@.microsoft.com> wrote in message
news:09a801c35812$d7540810$a401280a@.phx.gbl...
> you can reset the identity seed to start incrementing from
> your chosen number...
>
> >--Original Message--
> >have a table set one column as
> >data type = int
> > identity to yes
> >identity seed = 1 and identity increment = 1
> >
> >after deleting some rows (e.g. 5), the number 5 will
> never be re used.
> >
> >when the number has grown to the limit, can I still
> insert new rows?
> >
> >
> >
> >.
> >