auto increment problem

Question

auto increment problem

spectra

SSCarpal Tunnel

Points: 4120
More actions
December 27, 2005 at 12:28 am

#188755

CREATE TABLE mytable
(
id int IDENTITY(1,1),
//blah
//blah
}
question is , what does it mean by "IDENTITY(1,1)" in the above. why it is "1","1"....is this autoincrement will stop ever ? say, i want to insert 1 billion records , will there be 1 billion unique ids created ? or it will stop and crash after some time limit(say, 100000 records).
i am in hesitation whether to use this or not to use this...because i dont know , how many records my client would insert in the data base SQL server 2000.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Westley-239900 Grasshopper Points: 12 More actions · Answer 1

Hi,

as the column you set is int, which means you can have up to 2,147,483,647 records, and cannot go more then that, there is a datatype call bigint, which is 2^63 (can hold up to 9223372036854775807 records). As for identity, BOL can surely help you with that.

Ian Yates SSCoach Points: 19738 More actions · Answer 2

The first "1" means to start at number 1. The second "1" means to increment by 1 for each row. So 2, 4 would give a sequence such as

2, 6, 10, etc

This can be useful if you wanted to have some tables have their own ID columns but be able to ensure that the value in one table's ID column is unique across all of the tables - similar idea can apply to replication...

EG

Table A - identity (1, 5). Will have 1, 6, 11, etc

Table B - identity (2, 5). Will have 2, 7, 12, etc

Table C - identity (3, 5). Will have 3, 8, 13, etc

Table D - identity (4, 5). Will have 4, 9, 14, etc

Table E - identity (5, 5). Will have 5, 10, 15, etc

Having a value of, say 26, you could know that would definitely be in table A - so you can have a unique key across several of the tables..

Having said all of that... most commonly you will just see identity(1,1)

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 3

acally i wanted to make an id kind of thing in my DB table. as soon as i insert any record, this id would changes. so that records are different thatn each other.

i found this automatic increment IDENTITY.

spectra SSCarpal Tunnel Points: 4120 More actions · Answer 4

thank you for all the responses. you are all SQL gurus