IDENT_CURRENT return number 1 in blank table

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)

IDENT_CURRENT return number 1 in blank table

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-11-14 : 11:59:09

I used IDENT_CURRENT to get last ID but return number 1 in blank table. Why?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 12:02:54

Do you have some TRIGGERs on the table?
Where is the code for verification?

Peter Larsson
Helsingborg, Sweden

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-11-14 : 12:07:26

No trigger. simple code like:
"select IDENT_CURRENT('tblOrder')"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 12:12:11

Have you recently done a TRUNCATE or DELETE on the table?
IDENTITY values are not reset when table is empty.

quote:
Returns the last identity value generated for a specified table in any session and any scope.

Peter Larsson
Helsingborg, Sweden

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-11-14 : 12:15:26

yes, I remove ID and added it again.
How to solve this problem?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 12:18:13

DBCC CHECKIDENT ( yourtablename, RESEED, { new start value } )

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-14 : 12:35:04

That is by design (or lack of

), but there is absolutely no logic behind this behavior.

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 12:49:42

You mean you want the table to automatically RESEED whenever the table is empty?

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-14 : 13:01:52

I think he is commenting the fact that;

ident_current('empty table')
gives the same result as
ident_current('1 row table')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 13:05:34

Then he should not use IDENT_CURRENT function? More like @@ROWCOUNT

select ident_current('1 row table') could also return 20124734, for example.

Come on Moose, this is your chance to boost your postcount and break 3K!

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-14 : 13:17:41

LOL

Yes, but there are situations when SCOPE_IDENTITY() won't work, such as when accessing a linked server.

And the behavior is not sensible imo.

Just 5 more, should not take long

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-11-14 : 16:56:08

The purpose is my app need "Next ID" showing on the enter form.
If there is a recored, use IDENT_CURRENT will get right ID number, but for the very begining (nothing is in table), "Next ID" will be 2 if I code it as "MAXID += 1".

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-11-14 : 17:24:43

But if there are multiple concurrent users the "Next ID" can't be correct.
It will be "The next expected ID if nobody else inserts something first!"

rockmoose

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-15 : 06:28:23

Why do users need to see the ID number? The ID number should mean something to SQL and nobody else.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-11-15 : 16:54:27

if not exists(select top 1 * from Mytable)
begin
return 0
end
else
begin
ident_current(mytable)
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-16 : 00:32:13

This is not correct.
IF you have inserted two values, and later decide to delete the last inserted, the function IDENT_CURRENT will still return the ID for the last inserted value.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-16 : 01:14:12

We use Zero on user forms for "allocate me the next available number" (blank would do provided that you don't need the form-field to be compulsory), and then in a multi-user environment they get the next available number (the number allocated to them is reported back after the record is saved so they can write it on the paperwork they are working from, etc.)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources