bug with ident_current

Hello,
I have tried developped a stored proc that is using the ident_current('tablename') function to get the last Identity ID.

It was working fine. I drop and recreate the table (which reset the ID) and found that the ident_current said that the last Identity Id used was 1. The problem was that the table was empty. I insert a record and try it again. It said again that the last Identity ID was 1. After the insertion if the first record, everything is fine...

I would like to know if you know a way get 0 when the table is empty and 1 when there is only one identity id that have been used.

Thank

Felix Pageau
fpageau@.str.ca

You can test the functionnality with the following code:

create table identCurrentBugExeample(IDnumber int IDENTITY(1,1), aChar char(1))
Declare @.count as int
set @.count = (select count(IDnumber) from identCurrentBugExeample)

--Print the number of record in the table
print @.count

--Supposed to print 0 because there isn't any identity values that has been used
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')

--Print 1 because the last identity used is 1
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')

--Print 2 because the last identity used is 2
print ident_current('identCurrentBugExeample')
drop table identCurrentBugExeampleI'm sorry...is there a question here?|||Yes,

is there an option I can use with the fonction to know if there has been a record inserted. I mean, I would like to know if I can get "0" instead of 1 when there hasn't been any record inserted.

It is not because the count of the table is 0 that hasn't been any record inserted and deleted (and the next identity won't be 1...)|||Are you looking for SCOPE_IDENTITY?|||i'm not looking for SCOPE_IDENTITY. I need the to know what was the last value inserted into the IDENTITY column.

If there wasn't any record ever added, I want something like NULL or -1 to show that the table is still intact.

When there was at least one record added, I want the value of IDENTITY coloumn of the last record|||Ok...I admit it...I have NO idea what you're getting at...

anything like...

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), col2 char(1))
GO

SELECT MAX(Col1) FROM myTable99
INSERT INTO myTable99(Col2) SELECT 'A'
SELECT MAX(Col1) FROM myTable99

DROP TABLE myTable99
GO|||Hello,
the code you have provided works great if the record hasn't been deleted.

When you delete a record (or all the records), the next time you insert one, the value of the IDENTITY column will be x+1

Like:
ID Name GUID
1 Felix AAAAAjsdhksdkjk
2 Bob AAAASDFfsdjkdsj
... ...
7 Alycia ASDADnenefnefn
8 Roger AAAAAdfshdsfhjsdf

If I delete the record #8, and I insert a new one it will look like :
ID Name GUID
1 Felix AAAAAjsdhksdkjk
...
7 Alycia ASDADnenefnefn
9 NewName AAAACCCCddddd

Then I delete all the records
ID Name GUID

Now I must know what is the ID to add a new one (I need to know that before adding it because I'm saving some parts of the record on a Exchnage 2000 linked server (where I must put the ID), get the Global Unique ID of the record in Exchange 2k after saving and putting the guID back in the SQL server in the right record.)

If I do ident_current('tablename') and the value returned is 1. How can I know if the new record I will put in the table will have the ID 1 or 2 if when I do ident_current and there hasn't been anyrecord in the table I got 1 and when there is (or there was) only one record, I also got 1 ?

Do you have a clue on how to do this ?|||By the way, I can't get the GUID (global unique Identifier) directly from Exchange but only while I'm using the linked server to exchange from SQL Server. But I can get the ID from the exchange server while using the linked server and I can set it directly in Exchange...

I need a cross reference between the record (object because it is a OO databse) and the record in SQL server.

That is why I must get the next ID.
Create a dummy record with no informations
Send the ID back to exchange
Save the object in exchange with the ID
Send the GUID of exchange to SQL
Put the GUID in the right "dummy" record with the ID
Get the needed informations from the linked server

And that should work (that is working when I already have a record in the table in sql prior to adding the dummy record. But when I haven't put any, I got a bad ID for the first record that I had. Every other records added later will work fine).

It is why I need to know what is the last value of the IDENTITY column and get 0 when there wasn't any ever added to the table. But the function is giving 1 when there wasn't any record added and is also giving 1 when only one record has been added.

Thank a lot|||I think you will have to set Identity_SID starts from 0 then only..you will get '0' for Select IDENT_CURRENT('table1') when no reords inserted...

try playing with Identity_SID ...you will get the correct value...

see below For SQL help ---
IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.

Syntax
IDENT_CURRENT('table_name')

Arguments
table_name

Is the name of the table whose identity value will be returned. table_name is varchar, with no default.

Return Types
sql_variant

Remarks
IDENT_CURRENT is similar to the Microsoft SQL Server 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@.@.IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.