SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
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.
SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.
Reference : Pinal Dave (https://blog.sqlauthority.com)
230 Comments. Leave new
very fine Article
Thanx
Regards
Rahul
CREATE TABLE Nbrs(n INT NOT NULL IDENTITY)
GO
SET NOCOUNT ON
INSERT Nbrs DEFAULT VALUES
WHILE SCOPE_IDENTITY() < 500
INSERT dbo.Nbrs DEFAULT VALUES
The above insert statement is not working in SQL server compact edition
Please help me to execute this query in sql server compact edition.
SCOPE_IDENTITY() function is not supported by SQL Compact edition.
Use @@IDENTITY
It s a fine article. it will com to help of all level programmer
thanks
So nice pinal, can u please give some DR & HA related topics in SQL DBA
Regards,
Subir
Best PLace to learn SQL
rahul – thanks for the comment.
can we use scope_identity() for selecting the last inserted value in different server..
actually i hv to find out max value of one pramary key field.it will always a last inserted value.
so if my connection is opened for one server..then i am not able to find out the last inserted value with scope_identity() for that server.
so for this is there any other way .. or we have to use “select MAX(ID)”
If it is in a different server, you need to use
select max(id) as lat_id from table
Is there any way to retrieve the latest value of a field inserted in a table (apart from primary key field)…….if yes plz let me know…..
If you have identity column, datetime with default value of getdate() or a timestamp, it is easy
Can i use the IDENT_CURRENT to get the last record updated? I am trying to implement logging on a table with a trigger but I need to get the ID of the record that was updated.
In a trigger you can use UPDATE(col)
Thank you for the useful informations.
Can we use scope_identity() for selecting the last inserted value in different server.
No. It is not possible
Actually, if you have a stored procedure or function defined in the remote server and it executes scope_identity, it WILL work properly. It will then be up to the function or sproc to return the value to you on your server.
Also, it depends on what you mean by a different server. If you connect to SQL on one server, execute a command that crosses to a different SQL server, that IS a different server.
If you connect to SQL on a different server than the one you are on, that is NOT a different server.
You shouldn’t be doing things directly to a table on a different server anyway. That is a bad security model. Also, that can affect your permission to execute other things that aren’t allowed across servers that become available when you touch them by sproc or func. (Like locking hints.)
Thanks, this is a great piece of information. I’m more familiar with MySQL, and having a function I’m used to using makes working with MS SQL much nicer!
hi,
Is it possible to find out the list of lattest used stored procedure in sql server, say for eg the list of 10 stored procedure being used in a page.
Is it possible to retrieve from the sysobjects ?
Yes using a DMV
SELECT TOP 10
last_execution_time ,
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY last_execution_time DESC
I want to get the last inserted identity value of a table in a different server.
i want to use like :
select ident_current(‘server2.db1.dbo.table’)
if i give like this
select ident_current(‘db1.dbo.table’)
i am getting the result.
It is not possible to get it from a different server
You need to use
select max(col) from table
I need to retrieve identity column value from the below query:
INSERT INTO table-name(col1, col2) VALUES(val1, val2); SELECT SCOPE_IDENTITY()
using C + MS-SQL through ODBC
Please find the psuedo code that I have used:
hstmt = SQLAllocHandle
odbc_stmt = “INSERT INTO STUDENT (NAME, DIVISION) VALUES (?, ?); SELECT SCOPE_IDENTITY()”
SqlPrepare(hstmt, odbc_stmt)
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, &std_name, …
SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, &std_division, …
SQLBindCol(hstmt, 3, SQL_C_SSHORT, &std_id, ….
SQLExecute(hstmt)
SQLFetch(hstmt)
————
Here ID is an identity column for the student table.
I am able to insert the row. But identity column value for the inserted row is not getting retrieved to std_id variable after SQLFetch.
Please help me to find out the problem?
thanks in advance….
@@identity will work in your case…
Here is the problem, I create records using the identity number from the membership. I have checked the number is the same going in the db which is MS SQL 2005 however I can not retrieve records using the identity number, I have tried everything in grid view.
For example:
I have a drop down box list all users on selection it poplulates the grid view control.
However the grid view control does not populate I have even tried this manually pasting the identity number in but it returns no results. I am missing something here.
I write in VB VS2005 MS SQL 2005
Can you point me in the right direction to make this work? I am lost.
Can you post the code that you used in the VB application?
How do I get the id of the last inserted row if I don’t have an identity column? Of course, @@identity returns NULL in this case.
Thank you
If you have a datetime column with the default value of getdate() or a timestamp it is possible
Hi all,
I want to post the solution for my previous question (Index: 12). After digging for a lot of time, found missing piece from the code.
The key function is SQLMoreResults. It should be called just before SQLFetch.
The main purpose of SQLMoreResults is to find out if we have more than result set. We can always combine multiple queries in a single statement. In that scenario, we may expect multiple result sets. So to traverse from one result set to other, we require to call SQLMoreResults function.
thanks, rajuneo
INSERT INTO (SELECT Lists.ListCode ,Lists.ListName
FROM Lists)
VALUES (‘ListCode’,’ListName’)
plz tell me this statement run!!!!!!!!!
The correct code is
its insert in same table
Can we use bigint for identity field?
Yes. You can use data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0
What can I use to get the last Identity value like @@IDENTITY but over 2 connections.
I need to do multiple inserts and my program works with @@IDENTITY as the primary key the first time the program runs.
When I run it a 2nd time, I get the violation of primary key constraint and the statement has terminated error.
Please help!
Thanks,
Claudia
Also Ident_Current(‘tableName’) does not work in SQL Server 7
Thanks!
I run it but gives null value can u specify it.
It means data are not added to the table