---
title: "Great SQL tip: @@IDENTITY==bad, scope_identity()==good"
url: "https://osherove.com/blog/2003/11/12/great-sql-tip-identitybad-scope_identitygood.md"
canonical: "https://osherove.com/blog/2003/11/12/great-sql-tip-identitybad-scope_identitygood.html"
published: "2003-11-12T21:27:00"
updated: "2003-11-12T21:27:00"
tags: [".Net Quotations"]
author: "Roy Osherove"
description: "And another tip discovered by Sirsha . This time it's from SQLTeam . A very good SQL Server tip that I really must cling to! The gist: Use scope_identity() rather than @@IDENTITY or you might not get what you're looking…"
usage: "Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page)."
license: "https://creativecommons.org/licenses/by/4.0/"
---

# Great SQL tip: @@IDENTITY==bad, scope\_identity()==good

By Roy Osherove November 12, 2003 In [.Net Quotations](https://osherove.com/blog/category/net-quotations)

**Historical article** · Originally published November 12, 2003; technical details may be outdated.

And [another tip](http://hdconsultants.us/posts/239.aspx) discovered by [Sirsha](http://hdconsultants.us/). This time it's from [SQLTeam](http://www.sqlteam.com/). A very good SQL Server tip that I really must cling to!

**The gist: Use scope\_identity() rather than @@IDENTITY or you might not get what you're looking for.**

_“ManyTSQL books show you how to use @@Identity to get the identity of the most recently added row. Many articles online, or in magazines show the same. What you might not know is that it is potentially a source for some very hard to trace bugs in your application._

_@@Identity is potentially a very, very bad thing! In almost every case, you should use scope\_identity() instead._

_Why? @@Identity returns the most recently created identity for your current connection. When you first use it, it might be fine. Until someone adds a trigger. If the trigger causes another identity to be created, guess which identity you'll get in your call to @@Identity? Not nice._

_scope\_identity() is much nicer. It gives you what you're expecting.”_

## More in .Net Quotations

[All posts in .Net Quotations](https://osherove.com/blog/category/net-quotations)

- Nov 18, 2003

    ### [The dynamic SQL discussion is a must read](https://osherove.com/blog/2003/11/18/the-dynamic-sql-discussion-is-a-must-read.html)

- Nov 12, 2003

    ### [Using NAnt? Download NAntPad - for visual NAnt build configuration](https://osherove.com/blog/2003/11/12/using-nant-download-nantpad-for-visual-nant-build-configurat.html)

- Nov 12, 2003

    ### [Longhorn PDC bits download available for MSDN subscribers](https://osherove.com/blog/2003/11/12/longhorn-pdc-bits-download-available-for-msdn-subscribers.html)

- Nov 12, 2003

    ### [BitVector32 has a bug. Here's a good replacement](https://osherove.com/blog/2003/11/12/bitvector32-has-a-bug-heres-a-good-replacement.html)


---

## About the author

**Roy Osherove**: Senior Solutions Architect at AWS working on AI for production and AI-driven development; author of The Art of Unit Testing and Elastic Leadership, with about 20 years of consulting and training on unit testing, TDD, team leadership and continuous delivery.

### Sites

- [RobotPaper](https://robotpaper.ai/): This blog: AI patterns and anti-patterns, AI and coding agents, and engineering leadership.
- [osherove.com](https://osherove.com): Roy's personal site. Author of The Art of Unit Testing and Elastic Leadership; consulting and training on unit testing, TDD, team leadership and continuous delivery, plus about 20 years of blog posts.
- [The Art of Unit Testing](https://www.artofunittesting.com): The Art of Unit Testing, 3rd Edition (Manning): readable, maintainable, trustworthy unit tests, plus TDD workshops in C#, JavaScript, Java and Kotlin.
- [5whys.com](https://www.5whys.com): Roy's writing on software team leadership: self-organizing teams, surviving chaos, learning mode and the Elastic Leadership model.
- [Pipeline Driven](https://pipelinedriven.org): Continuous delivery and DevOps practices: build patterns, pipeline-friendly testing, DevOps metrics and enterprise DevOps.
- [Elastic Leadership](https://www.elasticleadership.com): Elastic Leadership, Roy's framework for software team leaders (survival, learning and self-organizing modes).

### Social

- X: https://x.com/royosherove
- LinkedIn: https://www.linkedin.com/in/osherove/

### Contact

- Contact form: https://forms.gle/jQSx5KoXvrVA1JCK7

### License

Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page). License: https://creativecommons.org/licenses/by/4.0/