LastRecord in DB

[darab]

Hi
How I Can Get To Last Records in DataBase tthat User Enter?

[darab]

my DB is SQL2005
How I can get to my last records? I need it's ID

[sknake]

If the column is an identity the value can be retrieved by calling SCOPE_IDENTITY()

Declare @ID int

Insert Into aTable (aColumn) Values (aValue)
Set @ID = Cast(SCOPE_IDENTITY() as int)

[rapture]

To get the last record in a SQL database you simply type

SELECT TOP 1 * FROM tableName ORDER BY columnWithYourID DESC

or if you just need the id then


SELECT TOP 1 (columnWithYourID ) FROM tableName ORDER BY columnWithYourID DESC

This will return the last record in the column you want from the table you are looking for. How to translate that into C# without using SQL I do not know.

[sknake]

SELECT TOP 1 (columnWithYourID ) FROM tableName ORDER BY columnWithYourID DESC

That is bad advice. It creates a race condition with concurrency in SQL Server where it can return the wrong ID. Use @@IDENTITY or SCOPE_IDENTITY() depending on what ID you need. Consult SQL Books Online for the difference in the two values. Based on the original post SCOPE_IDENTITY() would best suit your needs.

[rapture]

If you want to get technical, you're advice is bad as well, as scope_identity will:

"SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function."

Thus it may not return the proper id that the user entered right?

my solution was just a quick fix for a small database and someone new, it will work in most instances but the proper method would technically be

SELECT IDENT_CURRENT(’tablename’)

as this will return the last identity in the table regardless of the other variables mentioned. I suppose I should have answered this way the first time to prevent disagreement and confusion of the original poster or those who search the subject. Thanks for the advice.

Also of note, there is a possible bug when run in parallel with scope_identity as well that get more complicated
http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

Darab,

You can use either method mentioned. If you're just working on a small project then scope_identity may work fine. Otherwise you can use SELECT IDENT_CURRENT(’tablename’) as stated. This should answer your question. Go ahead and ask anything else you need on this subject or mark the thread as solved. :)

[sknake]

If you want to get technical, you're advice is bad as well, as scope_identity will:

"SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function."

No, it is not wrong in this case. Based on the original post it seems apparent that the OP wants the identity of the inserted record and not an identity from a trigger that may be defined on the table that was never mentioned. If you insert a record to a user table and a trigger inserts a record in an "audit" table for logging then SCOPE_IDENTITY() will return the user id, and @@IDENTITY will return the audit log id (if the tables are both set with identities and the user table has a trigger).

>> Thus it may not return the proper id that the user entered right?
Yes, it will. They did not enter an ID in this case as I didn't not see the OP indicating about enabling IDENTITY INSERT on the table.

>>my solution was just a quick fix for a small database and someone new, it will work in most instances but the proper method would technically be: SELECT IDENT_CURRENT(’tablename’)
That is the exact same problem as Select Max(id) from Table and is still a race condition. This is far more likely to be an issue than the parallelism condition of SCOPE_IDENTITY() >> Also of note, there is a possible bug when run in parallel with scope_identity as well that get more complicated
I was not aware of this bug. However this bug occurs sometimes where parallel execution plans are generated by the SQL Server which does not happen with Insert Into Table () Values () , and if it does I have failed to see anything from Microsoft that indicates this. That being said I doubt that bug affects this situation.

I do appreciate you posting that bug though. I myself have queries which are affected by that condition so I'll be sending out a company wide email :)

[rapture]

Intersting, thank you - and what was your take on using

SELECT IDENT_CURRENT(’tablename’)

[sknake]

Intersting, thank you - and what was your take on using

SELECT IDENT_CURRENT(’tablename’)

DO NOT use it. Use SCOPE_IDENTITY() or @@IDENTITY . Consult SQL books online for the differences.

Read the Microsoft Connect report that was posted here to be aware of a potential bug when using the identity features.

[rapture]

Can you find an example? on MSDN here http://msdn.microsoft.com/en-us/library/aa933217(SQL.80).aspx

it looks like it is similar to @@IDENTITY AND SCOPE_IDENTITY but just returning the last id value from any session or scope.

I see a problem if the table is null
http://support.microsoft.com/kb/835188

but if the table is created why wouldn't this be the best way to go?

--and thanks for the advice :P

[sknake]

IDENT_CURRENT
Problem:
1. You insert into "UserTable" and the row's identity is 2005.
2. Another user inserts into 'UserTable' and the row's identity is 2006.
3. You call IDENT_CURRENT() and it returns 2006. The wrong ID.

Insert Into Table (aColumn) Values (aValue)
--after your insert but before your select another user executes the same insert.
Select Ident_Current

So the solution for using IDENT_CURRENT would be to wrap the Insert/Select in a translaction and call (TABLOCKX) on the table and lock everyone else out to ensure you get your identity. This will also block other users from even calling a Select on the table and can create deadlocks on your system and cause the queries to hang until the lock is released, slowing down your system. To get around this you could call (NOLOCK) on your selects but then you're ready dirty data. The list goes on and on... Does this sound like a good solution to you? If it does then go back to using flat files for databases.

@@IDENTITY
Problem:
1. You insert a record in to UserTable
2. You select back @@IDENTITY
3. Everything is good.
Two years later your boss wants you to implement logging in your application so you add a trigger to UserTable to log what user modified which fields.

So at this point we have the trigger implemented.
1. You insert in to the usertable and get ID 2074.
2. The trigger fires off and inserts an entry in "UserTableLog" which gets the record id of 1000
3. You call Select @@IDENTITY and the value 1000 is returned.
4. Your application breaks.

Unless you are intending to locate a record created by a trigger then this is unsafe. It will work but on down the road if you change something and have @@IDENTITY calls throughout your application you will break it. Better yet you hire a new DBA and he does this without telling you. You're not aware of the DB changes, hes not aware how your application works, so nobody has half a clue what happened. Does this sound like a good solution?

SCOPE_IDENTITY()
1. You insert a record in to UserTable and it generates record 8751.
2. Another user inserts a record a in the table creating id #8752
3. triggers fire off from both inserts creating ids #1,#2
4. You call SCOPE_IDENTITY and it returns 8751, the expected value.
5. Life is good.

IF you are using SQL Server Enterprise edition (not express, msde, or developer) and you have more than one logical processor and you have the sql server's max degree of parallelism configured to >1 and you write a complex insert query that generates a parallel execution problem then you may have a problem. That being said it is rather unique circumstances in which this command will fail and there are three published workarounds for it.

[rapture]

Thanks, that helps - we don't have any inserts that will be affected that I can think of so it's not a huge deal. I think what I was thinking was he just wanted to get the last record regardless of the reason and if someone else entered a record he might not get the right id returned. But based on his question I think your way gets him the last id that this user entered so that's the way to go.

*but hey man I always though reading dirty data was a good thing . .. jk :)

[sknake]

>>*but hey man I always though reading dirty data was a good thing
Thats a discussion for when i'm off the clock :P