Identity property ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Identity property ?

Author

Topic

QuachNguyen
Starting Member

15 Posts

Posted - 2007-10-05 : 18:36:08

hi All

I'm new commer , and new in SQL Server , I'm learning SQL server 2000 , I have a problem with identity property of CustomerID filed .

EX : CustomerID with int type and identity property. When I insert a new record , it's auto increase , Then I delete a record such as CustomerID = 2 . My table has 10 records

WHen I insert a new record , I would like a new record will be inserted with CustomerID=2 . Instead of CustomerID=11 .

How can I do that ? Maybe I must write a trigger ?

Please explain and put code demo .

Thanks in advance
Sorry for poor english

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-05 : 19:47:31

You would have to insert it with ID 2 using Identity insert:

http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server

Future guru in the making.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 20:47:10

And you shouldn't use the identity option if you are going to back-fill gaps.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

QuachNguyen
Starting Member

15 Posts

Posted - 2007-10-06 : 03:02:57

Thank : Zoroaster , tkizer for understanding and replying my question

Now I can do it with manual insert into my table . Is it possible to enable automatically assign a value ?

For Example : Customers
----------- --------------------
1 ABC
3 DEF
4 MBK

When i insert a new row ,
INSERT INTO Customers VALUES('XYZ') .it's automatically insert with value to be 2 , instead of 5 .

Thanks in advance .

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-06 : 03:06:00

quote:
Originally posted by QuachNguyen

Thank : Zoroaster , tkizer for understanding and replying my question

Now I can do it with manual insert into my table . Is it possible to enable automatically assign a value ?

For Example : Customers
----------- --------------------
1 ABC
3 DEF
4 MBK

When i insert a new row ,
INSERT INTO Customers VALUES('XYZ') .it's automatically insert with value to be 2 , instead of 5 .

Thanks in advance .

no there's no "automatic" way to get lost id's. You'd have to manually find them if you want to restore them.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 05:13:51

You can use a query to find a gap, and if there isn't one find the MAX-value-plus-one to add at the end.

But I have to ask why you would want to do this. If you reuse a gap won't it be confusing that there are references to an ID which has been used by two different people?

We never worry about gaps in ID numbers.

Note that if you are using an Identity column you will also get a "gap" if an insert fails - i.e. even if you never delete anything.

Fact of SQL life I'm afraid!

Kristen

QuachNguyen
Starting Member

15 Posts

Posted - 2007-10-06 : 10:42:15

Kristen : "We never worry about gaps in ID numbers." ..."Note that if you are using an Identity column you will also get a "gap" if an insert fails - i.e. even if you never delete anything."

You right , Now I understand .

Thank : Kristen, dinakar , Zoroaster .

Regards.
Vietnamese student :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources