How to get last id if any column in SQL Server 2005?

Question

Possible Duplicate:
How to get last inserted id?

I am trying to get the last id (Last value of an identity column) to show it on control incremented by 1 each time when data is inserted to table.

How to get this last id in a stored procedure in SQL Server 2005 using C#?

Do you mean "just after an insert" (if so, `SCOPE_IDENTITY()`), or do you mean "at any point"? Note: there's not much benefit in the second case, as you can't guarantee that you'll get +1 next time (other spids etc) — Marc Gravell, May 12 '12 at 10:29

score 2 · Accepted Answer · answered May 12 '12 at 10:30

2

Identity columns are incremented in the database, not on the C# side. You can query for the last value like this:

SELECT MAX(id) FROM mytable

answered May 12 '12 at 10:30

Jirka Hanika

13,301
3
46
75

That just gives you the max value currently in the table - but the `IDENTITY` column could be on a higher value, if the last several inserts were all rolled back by transaction. This is **NOT** at all a reliable way to check – marc_s May 12 '12 at 10:44
1

@marc_s - That's true. +1 to your answer. – Jirka Hanika May 12 '12 at 11:49
Thank you Jirka Han.You did really well but i am looking for is what i m going to post. after 1 day of tricky practice i found a absolut solution for my question.Pleas have a look here. – HEARTBEAT May 15 '12 at 06:44

score 2 · Answer 2 · answered May 12 '12 at 11:48

Either just grab the latest ID when the insert happens (using SCOPE_IDENTITY()), or if you need to check the current value of an IDENTITY column later on, use SELECT IDENT_CURRENT('table_name') to get that value.

So the easiest way is to just get the ID as you insert your values - something like this:

string sql = "INSERT INTO dbo.YourTable(Col1, ..., ColN) VALUES(@Val1, ..., @ValN); SELECT SCOPE_IDENTITY()";

using (SqlConnection conn = new SqlConnection(connString))
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    conn.Open();
    _ID = (Int32)cmd.ExecuteScalar();
    conn.Close();
}

Or if you cannot grab the ID as it's being inserted, you can always check later on what the current last used value of the IDENTITY column on a given table was, using something like this:

string sql = string.Format("SELECT IDENT_CURRENT('{0}');", yourTableName);

using (SqlConnection conn = new SqlConnection(connString))
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
    conn.Open();
    _ID = (Int32)cmd.ExecuteScalar();
    conn.Close();
}

Likurg · Answer 3 · 2012-05-12T10:56:35.313

1

You can use this

SELECT @@IDENTITY AS 'Identity';

or this

SELECT MAX(SomeID) FROM SomeTable;

EDIT

Best way to use

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

and in C# you could call

Int32 _ID = 0;
//you could use second variant sql= "SELECT MAX(SomeID) FROM SomeTable";
string sql =
    "SELECT @@IDENTITY AS 'Identity'";
using (SqlConnection conn = new SqlConnection(connString))
{
    SqlCommand cmd = new SqlCommand(sql, conn);
    try
    {
        conn.Open();
        _ID = (Int32)cmd.ExecuteScalar();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

EDIT

Nice link to feel difference Using @@IDENTITY and SCOPE_IDENTITY with triggers

edited May 12 '12 at 10:56

answered May 12 '12 at 10:32

Likurg

2,742
17
22

I would recommend to use **`SCOPE_IDENTITY()`** instead of anything else to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) – marc_s May 12 '12 at 10:43
@marc_s Oh i see, thank you. by the way i found interesting link where showed some example why We shouldn't use @@Identity http://cgaskell.wordpress.com/2006/11/15/using-identity-vs-scope_identity-and-further-implications-with-triggers/ – Likurg May 12 '12 at 10:54

score 0 · Answer 4 · edited May 12 '12 at 10:43

0

SELECT TOP 1 Id FROM table_name ORDER BY 1 DESC

or in LINQ:

context.table.Select(x->x.Id).OrderByDescending(x->x.Id).FirstOrDefault();

edited May 12 '12 at 10:43

marc_s

732,580
175
1,330
1,459

answered May 12 '12 at 10:29

Mark

1,718
11
10

`.Max(x => x.Id)` seems a lot more direct... Maybe `.Max(x => (int?)x.Id)` if concerned about zero rows. – Marc Gravell May 12 '12 at 10:33
That just gives you the max value currently in the table - but the `IDENTITY` column could be on a higher value, if the last several inserts were all rolled back by transaction. This is **NOT** at all a reliable way to check – marc_s May 12 '12 at 10:46

How to get last id if any column in SQL Server 2005?

4 Answers4