Identity seed/increment of MS SQLSERVER 2000 and MSDE

How can I change the increment value of an identity column?
I absolutely need to set a new value for the "increment"
DBCC CHECKIDENT only allow me to change the increment.
ALTER COLUMN does not allow altering identity
Is there a system column i can update or ?
I read few post suggesting using the enterprise manager to do so... but
we got lots of tables with many levels and fk. it's almost impossible in
our situation.
In fact we got many offline databases that where supposed to be
identity(-1,-1) but I just get a surprise!
Tons of data are alrealy inserted (positively) by a "kind of"
replication. but locally created records should be negative.
Thanks a lot
*** Sent via Developersdex http://www.developersdex.com ***Hi
You cannot update an IDENTITY property. Create another non_identity column
and move all data (of identity values) to the column.
You will be able to update a new created column and later on delete an
IDENTITY column
"Vincent" <anonymous@.devdex.com> wrote in message
news:Oq%23ev9A9HHA.600@.TK2MSFTNGP05.phx.gbl...
> How can I change the increment value of an identity column?
> I absolutely need to set a new value for the "increment"
> DBCC CHECKIDENT only allow me to change the increment.
> ALTER COLUMN does not allow altering identity
> Is there a system column i can update or ?
> I read few post suggesting using the enterprise manager to do so... but
> we got lots of tables with many levels and fk. it's almost impossible in
> our situation.
> In fact we got many offline databases that where supposed to be
> identity(-1,-1) but I just get a surprise!
> Tons of data are alrealy inserted (positively) by a "kind of"
> replication. but locally created records should be negative.
> Thanks a lot
>
> *** Sent via Developersdex http://www.developersdex.com ***

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?
> >
> >
> >
> >.
> >

Identity Property

I am setting up a field with the Identity property. I need the Seed value to
be R1 and then increment by 1. Example - R1, R2, R3 etc. Is there a way to
set the Seed Value to have the prefix of R?
Thanks!
Deb
>> Is there a way to set the Seed Value to have the prefix of R?
No, identity columns must be of numeric datatypes. However, it is easy to do
the conversion to a character string in your SELECT statement. If you must
have the values in the table, an alternative is to use a computed column
like:
CREATE TABLE tbl (
idcol INT NOT NULL IDENTITY,
...
compcol AS 'R' + CAST( idcol AS VARCHAR )
... );
Anith

Identity Property

I am setting up a field with the Identity property. I need the Seed value t
o
be R1 and then increment by 1. Example - R1, R2, R3 etc. Is there a way to
set the Seed Value to have the prefix of R?
Thanks!
Deb>> Is there a way to set the Seed Value to have the prefix of R?
No, identity columns must be of numeric datatypes. However, it is easy to do
the conversion to a character string in your SELECT statement. If you must
have the values in the table, an alternative is to use a computed column
like:
CREATE TABLE tbl (
idcol INT NOT NULL IDENTITY,
..
compcol AS 'R' + CAST( idcol AS VARCHAR )
.. );
Anith

Identity Key possible problem

Has anyone experience any problems with identity key value not incrementing properly? I have a table with an identity key which should increment by one but has missing value. I would just like to exclude the possiblity of a SQL Server error.

Missing values happen for identity columns when transactions are rolled back.

Each time when you do an insert, the identity value gets incremented, but if the transaction that contains the insert does a rollback, you will loose the value.

You are guaranteed that you will never get the same identity value, but it is possible to get holes in the numbers.

Identity Increment = Greyed Out (actually, all properties are greyed out)

I am looking to make the primary key auto increment by 1. I have found by looking around the internet that you need to do this in the Tables -> [Table Name] -> Columns -> [Column Name], Properties window, and I see the "Identity Increment" however all of the properties there are greyed out -- I can't access them.

Any ideas on how to make this work?

For some background:

I'm running SQL Server 2005 with Visual Studio 2005. To create this database, I right-clicked on my project, and went to 'Add SQL Database', I filled in the columns all from within visual studio.

Identity Increment is only avalible for integer types, like tinyint, smallint, int, bigint, etc.

Identity Increment

If delete all the records from a table that has an incremental identity. Is there a TSQL way of reset the first number on an insert back to be 1 again without going to the table taking it off then saving it the putting it back on again?TRUNCATE table clears all data and resets the Identity counter.|||Perfect! Thank you.

Identity Increment

Hello, I need some help writing a script to generate Identity keys. I cannot use the row number generator because I would like to start the identity at a package level variable. Is this possible?
Thank you in advance

I just find this using a web search engine:

http://www.sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx

I know that has been also discussed in this forum; I recommned you tu use the search functionality of the forum

|||http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

identity fields

how do I start an auto increment field at a certain number? other than 1CREATE TABLE test (
test_id int NOT NULL IDENTITY (100, 1)
)

will create a table where the test_id column starts at 100|||Open desired table in design mode
Set the Identity property to "Yes" for the desired field
Set Identity Seed property to the desired value

You can even set the increment value here.

?

identity error

Help!

I have said yes to identity for my id in some tables. I left the values of identity seed and increment as 1, so it should really start at 1 and keep increasing by 1. When I input and then decide delete a row it affects the increment seed!!!e.g. if i delete row 2, the next row should be 2 but it becomes 3!! how do i stop this?That's the normal behavior. You can reset the seed with DBCC checkident,reseed (see manual), but that'll only set a new starting point and you'll keep getting "holes" when you delete rows. To prevent that, you should disable "identity" and increment manually.|||Its not a good idea in general to rely on surrogate keys for sorting or ordering data. It is better to use a natural key or add a datetime value.|||asbirpam, my advice is, just don't worry about it

leave the holes

are you worried about running out of numbers?

let's say you add new rows to your table at the rate of 100 per hour (that's more than one per minute)

with an integer identity field, you will not run out of number for over two thousand years

i personally would not worry about it|||Not to mention that you can use the datatype bigint (ss2k) as well for identities. So for the same 2000 year time span you could have over 500 billion transactions per hour before the identity would rollover ... Just think that when our grandchildren (future dbas) talk to us about their trillion records per hour processing we can warn them of this.

Identity Columns

Where do I find the seed and increment values of the identity column in database.
If possile can u suggest query for that Pl.
Thanx in advance
-aliselect
object_name(id) as table_name,
name as column_name,
ident_seed(object_name(id)) as seed,
ident_incr(object_name(id)) as increment,
ident_current(object_name(id)) as last_identity
from
syscolumns
where
autoval is not null
and colstat & 1 = 1
and status & 0x80 = 0x80
and columnproperty(id,name,'IsIdentity') = 1

Hope, this helps.

Identity columns

Hi Everyone,
If you great an identity column using the statement below, what is the
maximum value the identity column will increment to? Is it the it the
maximum value of the column data type? In this case 32,768? I tried a loop
to add 100,000 rows and it did so without any error. Thanks in advance.
Larry
create table test_table
(
Doc_Index INT IDENTITY(1,1) NOT NULL
)
The maximum value is determined by the datatype. In this case you've used
INT for which the maximum is 2,147,483,647. So you have some way to go yet
...
David Portas
SQL Server MVP
|||Each integer datatype will allow so many as David pointed out.
So a tinying (1 byte = 8 bits) = 2^8 or 256
smallint (2 bytes = 16 bits) = 2^16 or 32767
int (4 bytes = 32 bits) = 2^32 or 2.14 billion
bigint (8 byte = 64 bits) = 2^64 or a whole bunch
You can also use a scaled integer like decimal, just set the scale to 0.
Decimal(15,0) = a really really big number.
Rick Sawtell
MCT, MCSD, MCDBA
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:BKWdnc2hu_wFJ_jcRVn-ig@.giganews.com...
> The maximum value is determined by the datatype. In this case you've used
> INT for which the maximum is 2,147,483,647. So you have some way to go yet
> ...
> --
> David Portas
> SQL Server MVP
> --
>
|||You must be thinking of an INT in VBScript. In SQL Server, the upper bound
of an INT is over 2 billion. If that isn't enough, you could go to BIGINT
(assuming SQL Server 2000). http://www.aspfaq.com/2503
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:4DF935A2-34AB-4509-B131-BC7FD1FD0B91@.microsoft.com...
> Hi Everyone,
> If you great an identity column using the statement below, what is the
> maximum value the identity column will increment to? Is it the it the
> maximum value of the column data type? In this case 32,768? I tried a
loop
> to add 100,000 rows and it did so without any error. Thanks in advance.
> Larry
> create table test_table
> (
> Doc_Index INT IDENTITY(1,1) NOT NULL
> )
|||Larry wrote:
> Hi Everyone,
> If you great an identity column using the statement below, what is the
> maximum value the identity column will increment to? Is it the it the
> maximum value of the column data type? In this case 32,768? I tried
> a loop to add 100,000 rows and it did so without any error. Thanks
> in advance. Larry
> create table test_table
> (
> Doc_Index INT IDENTITY(1,1) NOT NULL
> )
INT in SQL Server is the same as Long in VB (4 bytes). It's not the same
as a VB Integer (2 bytes).
David Gugick
Imceda Software
www.imceda.com

Identity columns

Hi Everyone,
If you great an identity column using the statement below, what is the
maximum value the identity column will increment to? Is it the it the
maximum value of the column data type? In this case 32,768? I tried a loop
to add 100,000 rows and it did so without any error. Thanks in advance.
Larry
create table test_table
(
Doc_Index INT IDENTITY(1,1) NOT NULL
)The maximum value is determined by the datatype. In this case you've used
INT for which the maximum is 2,147,483,647. So you have some way to go yet
...
--
David Portas
SQL Server MVP
--|||Each integer datatype will allow so many as David pointed out.
So a tinying (1 byte = 8 bits) = 2^8 or 256
smallint (2 bytes = 16 bits) = 2^16 or 32767
int (4 bytes = 32 bits) = 2^32 or 2.14 billion
bigint (8 byte = 64 bits) = 2^64 or a whole bunch
You can also use a scaled integer like decimal, just set the scale to 0.
Decimal(15,0) = a really really big number.
Rick Sawtell
MCT, MCSD, MCDBA
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:BKWdnc2hu_wFJ_jcRVn-ig@.giganews.com...
> The maximum value is determined by the datatype. In this case you've used
> INT for which the maximum is 2,147,483,647. So you have some way to go yet
> ...
> --
> David Portas
> SQL Server MVP
> --
>|||You must be thinking of an INT in VBScript. In SQL Server, the upper bound
of an INT is over 2 billion. If that isn't enough, you could go to BIGINT
(assuming SQL Server 2000). http://www.aspfaq.com/2503
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:4DF935A2-34AB-4509-B131-BC7FD1FD0B91@.microsoft.com...
> Hi Everyone,
> If you great an identity column using the statement below, what is the
> maximum value the identity column will increment to? Is it the it the
> maximum value of the column data type? In this case 32,768? I tried a
loop
> to add 100,000 rows and it did so without any error. Thanks in advance.
> Larry
> create table test_table
> (
> Doc_Index INT IDENTITY(1,1) NOT NULL
> )|||Larry wrote:
> Hi Everyone,
> If you great an identity column using the statement below, what is the
> maximum value the identity column will increment to? Is it the it the
> maximum value of the column data type? In this case 32,768? I tried
> a loop to add 100,000 rows and it did so without any error. Thanks
> in advance. Larry
> create table test_table
> (
> Doc_Index INT IDENTITY(1,1) NOT NULL
> )
INT in SQL Server is the same as Long in VB (4 bytes). It's not the same
as a VB Integer (2 bytes).
--
David Gugick
Imceda Software
www.imceda.com

Identity Column Increment Control

Hi,
I have a table with identity column. I set the increment
to 1. But after I have deleted a couple of rows then
inserted a new row, the increment is not based on the
existing row number. For example, I had 100 rows already.
After I deleted two rows from the bottom., the last row I
have is 98. Then if I insert another row, it starts from
101 instead of 99. How can I solve this problem.
Thanks,
Derek
This is expected. The next identity value will not be in sequence, you will
see gaps in the identity values. The increment is not based on the existing
row number, however it will be the next of last generated identity value for
the table.
You can use "dbcc checkident" to reset the identity value of the table.
ex:
create table tt(i int not null identity, ii varchar(6000))
go
insert into tt (ii) values('x')
insert into tt (ii) values('y')
go
delete from tt where i = 2
go
DBCC CHECKIDENT (tt, RESEED, 1)
GO
insert into tt (ii) values('z')
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||>> The increment is not based on the existing
row number, <<
I mean to say, it is not based on the last value of the identity column.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Identity Column Increment Control

Hi,
I have a table with identity column. I set the increment
to 1. But after I have deleted a couple of rows then
inserted a new row, the increment is not based on the
existing row number. For example, I had 100 rows already.
After I deleted two rows from the bottom., the last row I
have is 98. Then if I insert another row, it starts from
101 instead of 99. How can I solve this problem.
Thanks,
DerekThis is expected. The next identity value will not be in sequence, you will
see gaps in the identity values. The increment is not based on the existing
row number, however it will be the next of last generated identity value for
the table.
You can use "dbcc checkident" to reset the identity value of the table.
ex:
create table tt(i int not null identity, ii varchar(6000))
go
insert into tt (ii) values('x')
insert into tt (ii) values('y')
go
delete from tt where i = 2
go
DBCC CHECKIDENT (tt, RESEED, 1)
GO
insert into tt (ii) values('z')
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||>> The increment is not based on the existing
row number, <<
I mean to say, it is not based on the last value of the identity column.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

identity and identity increment

Hello,
whenever I add a record to a table with identity column it increments by one
which is what I want. But when I input a record that was reject for any
reason (I.e. primary key violation) and then input another record it will
not be increment by one...it skips the number that would of been assigned
to the incorrect record. is there an easy to ensure that it always
increments by one?> whenever I add a record to a table with identity column it increments by
> one which is what I want. But when I input a record that was reject for
> any reason (I.e. primary key violation) and then input another record it
> will not be increment by one...it skips the number that would of been
> assigned to the incorrect record. is there an easy to ensure that it
> always increments by one?
No, if you are going to use IDENTITY, you should learn to live with gaps, or
else use something more like a sequence generator (essentially you lock the
entire table and use MAX(id_col)+1). If you delete any row other than the
most recent, how do you account for that gap? Do you intend to re-insert a
row with ID = 40 when in fact your next ID should have been 722? Are you
associatng some tangible value to the actual ID value generated? If so,
why? In reality, the consumers of the data should not really care if thir
next row gets an ID of 36 or 37 or 38.
http://www.aspfaq.com/2523
A|||That is the nature of using IDENTITY; transactions that are rolled back caus
e
gaps in the sequence. It's one of the reasons some DBA's won't use IDENTITY.
The only way around it is to use a custom id sequence function. Itzik
Ben-Gan wrote an article for SQL Server Magazine a few months back on how to
create custom identity functions.
HTH,
-Mark Williams
"John Smith" wrote:

> Hello,
> whenever I add a record to a table with identity column it increments by o
ne
> which is what I want. But when I input a record that was reject for any
> reason (I.e. primary key violation) and then input another record it will
> not be increment by one...it skips the number that would of been assigned
> to the incorrect record. is there an easy to ensure that it always
> increments by one?
>
>|||thanks... that helps
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCh7ueQeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> No, if you are going to use IDENTITY, you should learn to live with gaps,
> or else use something more like a sequence generator (essentially you lock
> the entire table and use MAX(id_col)+1). If you delete any row other than
> the most recent, how do you account for that gap? Do you intend to
> re-insert a row with ID = 40 when in fact your next ID should have been
> 722? Are you associatng some tangible value to the actual ID value
> generated? If so, why? In reality, the consumers of the data should not
> really care if thir next row gets an ID of 36 or 37 or 38.
> http://www.aspfaq.com/2523
> A
>

Identity / Auto Incrementing Column

Hi,

I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?

trenyboy wrote:

Hi,

I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?

since int's maximum size is 2G once it's limit is exceeded you will have an arithmetic overflow. same goes with bigint.. although by then 2GB of rows this is a prime candidate for partioning already...