Quenstion about indentity in SQL server 2000

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

Transact-SQL (2000)

Quenstion about indentity in SQL server 2000

Author

Topic

bathong
Starting Member

1 Post

Posted - 2009-10-12 : 22:15:40

I have a table


create table Users (
      idUser int indentity(1,1) primary key,
      usename varchar(20) not null unique,
      .....
)

I execute three insert statement
+ First insert with username as 'test' was successful --> idUser = 1
+ Second insert with username as 'test' was error
+ Third insert with username as 'test1' was successful --> idUser = 3

I wanna third insert successful with idUser = 2, why is idUser = 3 ??? How to 'idUser = 2' because table have 2 record !!

Please help me !! Thank

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-12 : 23:15:58

The identity value gets used even on error. Don't pay attention to the gaps in the identity values as it doesn't make a difference.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 13:04:39

you can always get number of records in table using count. no need for id values to be contiguos for that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources