1

I'm working in Visual Studio - VB.NET.

My problem is that I want to delete a specific row in SQL Server but the only unique column I have is an Identity that increments automatically.

My process of work:
1. I add a row in the column (the identity is being incremented, but I don't know the number)
2. I want to delete the previous row

Is there a sort of unique ID that every new record has? It's possible that my table has 2 exactly the same records, just the sequence (identity) is different.

Any ideas how to handle this problem?

Vella
  • 71
  • 1
  • 3
  • 11
  • Why not remember the identity column? You can retrieve it after insertion with `scope_identity()`. – Andomar Jan 08 '13 at 08:36
  • `(the identity is being incremented, but I don't know the number)` You can get that identity value using [**`SCOPE_IDENTITY()`**](http://msdn.microsoft.com/en-us/library/ms190315.aspx) after the identity insert directly. – Mahmoud Gamal Jan 08 '13 at 08:37
  • Or simply SELECT [Ident] FROM [TABLE] WHERE [Ident] = (select max(ident) FROM [table]). Take not, that if you have app that serve many users, some of them can insert in between your insert and query. Same as with other ways to find Ident. – Gustav Klimt Jan 08 '13 at 09:23

2 Answers2

6

SQL Server has a few functions that return the generated ID for the last rows, each with it's own specific strengths and weaknesses.

Basically:

  • @@IDENTITY works if you do not use triggers
  • SCOPE_IDENTITY() works for the code you explicitly called.
  • IDENT_CURRENT(‘tablename’) works for a specific table, across all scopes.

In almost all scenarios SCOPE_IDENTITY() is what you need, and it's a good habit to use it, opposed to the other options.
A good discussion on the pros and cons of the approaches is also available here.

Community
  • 1
  • 1
SWeko
  • 30,434
  • 10
  • 71
  • 106
3

I want to delete the previous row

And that is your problem. There is no such concept in SQL as a 'previous row'. The word previous implies order and order applies only to queries, where is achieved by adding an ORDER BY clause. Tables have no order. You need to rephrase this in terms of "I need to delete the record that satisfies <this> condition.". This may sound to you like pedantic gibberish, but you will never find a solution until you acknowledged the problem.

Searching for a way to interpret the value of the inserted identity column and then subtracting 1 from it is flawed with many many many problems. It is incorrect under concurrency. It is incorrect in presence of rollbacks. It is incorrect after ETL jobs. Overall, never expect monotonically increasing identities, they're free to jump gaps and your code should be correct in presence of gaps.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569