"Lisa Pearlson" wrote:

> I posted this on .programming, but got no response.. maybe because it's a
> newbie question:
>
> Hi,
>
> Do many people use the IDENTITY type for a column?
> The advantage is that every time you add a row, that identity column is
> automatically incremented, however, I have always wondered about the
> following issues that I could try to test and find out, but my testing will
> not necessarily reveil all there is to know that you might know off the top
> of your heads.
>
> 1) can you manually insert an identity? for example, row 1, 2 and 3 already
> exist and I manually INSERT a row with id 5, what will happen when identity
> reaches 4 and wish to move to 5? will it skip 5 because it already exists,
> overwrite it, or what?
>
> 2) What happens if identity reaches maximum? If INDENTITY is reset, while
> rows already exist, what will happen to existing rows? See question 1.
>
> 3) identity keeps moving up.. if later rows are deleted, gaps are formed.
> How can this be prevented? Is there a way to find most efficiently which
> ID's are available?
>
> So basically, imagine I have a table like this:
>
> CREATE TABLE MyTable (
> Id IDENTITY(1,1) PRIMARY KEY,
> OtherData TEXT
> )
> GO
>
> INSERT INTO MyTable(OtherData) VALUES ("test1")
> INSERT INTO MyTable(OtherData) VALUES ("test2")
> INSERT INTO MyTable(OtherData) VALUES ("test3")
> INSERT INTO MyTable(OtherData) VALUES ("test4")
> INSERT INTO MyTable(OtherData) VALUES ("test5")
>
> DELETE FROM MyTable WHERE Id=2 OR Id=4
>
> Now I wish to have a query that gives the available id's back (2 and 4 in
> this case).
> The problem is, how do you info about rows that do not exist?
> Only way I can think of is get MAX(Id) and then loop through all values in
> descending order until you find one that doesn't exist, testing each one.
> And this can become very slow in case of many records, and can't be the way.
>
> Any suggestions? How are IDENTITY columns used? Just keep letting it go up
> until it runs out? I know this would take millions of records maybe and in
> real life situations may never happen, but theoretically, it just doesn't
> sound right.
> rows will get deleted and added and at some point in the future, I will have
> reached the ceiling because deleted id's are never reused.
>
> Or is it much better to use a "FindFreeId" type of stored procedure or
> whatever, wit or without an IDENTITY column?
>
> Lisa
>
>
>