使用 MS SQL Identity 是一个好的做法吗？
在企业应用程序中使用 MS SQL Identity 是一个好的做法吗？ 这难道不会给创建业务逻辑以及将数据库从一个数据库迁移到另一个数据库带来困难吗？
Is using MS SQL Identity good practice in enterprise applications? Isn't it make difficulties in creating business logic, and migrating database from one to another?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
就我个人而言，我不能没有身份列并在任何地方使用它们，但是有一些理由考虑不使用它们。
最初不使用标识列的主要原因 AFAIK 是由于分布式多数据库模式（断开连接）使用复制和/或各种中间件组件来移动数据。 只是没有可用的分布式同步机制，因此没有可靠的方法来防止冲突。 由于 SQL Server 支持分发 ID，因此情况发生了显着变化。 然而，它们的使用仍然可能无法映射到更复杂的应用程序控制的复制方案。
他们可以泄露信息。 帐户 ID、发票号码等。如果我每个月收到您的发票，我就可以估算出您发送的发票数量或您拥有的客户数量。
我在合并客户数据库时总是遇到问题，而且各方仍然希望保留他们的旧帐号。 这有时让我质疑我对身份字段的沉迷:)
像大多数事情一样，最终的答案是“这取决于”给定情况的具体情况必然在您的决定中占有很大的分量。
Personally I couldn't live without identity columns and use them everywhere however there are some reasons to think about not using them.
Origionally the main reason not to use identity columns AFAIK was due to distributed multi-database schemas (disconnected) using replication and/or various middleware components to move data. There just was no distributed synchronization machinery avaliable and therefore no reliable means to prevent collisions. This has changed significantly as SQL Server does support distributing IDs. However, their use still may not map into more complex application controlled replication schemes.
They can leak information. Account ID's, Invoice numbers, etc. If I get an invoice from you every month I can ballpark the number of invoices you send or customers you have.
I run into issues all the time with merging customer databases and all sides still wanting to keep their old account numbers. This sometimes makes me question my addiction to identity fields :)
Like most things the ultimate answer is "it depends" specifics of a given situation should necessarily hold a lot of weight in your decision.
是的，它们工作得很好，很可靠，而且表现最好。 使用身份字段与不使用身份字段相比的一大好处是，它们可以处理多个调用者尝试保留新 ID 的所有复杂并发问题。 这对于编码来说似乎是微不足道的事情，但事实并非如此。
下面的这些链接提供了一些有关身份字段的有趣信息以及为什么您应该尽可能使用它们。
Yes, they work very well and are reliable, and perform the best. One big benefit of using identity fields vs non, is they handle all of the complex concurrency issues of multiple callers attempting to reserve new id's. This may seem like something trivial to code but it's not.
These links below offer some interesting information about identity fields and why you should use them whenever possible.
问题始终是：
您实际上从一个数据库迁移到另一个数据库的可能性有多大？ 如果您正在构建一个多数据库应用程序，那就是另一回事了，但大多数应用程序永远不会移植到新的数据库中游 - 特别是当它们从像 SQL Server 这样强大的东西开始时。
身份构造非常出色，而且几乎没有理由不使用它。 如果您有兴趣，我写了一篇博客文章，介绍有关身份价值观的一些常见神话。
IDENTITY 属性：SQL Server 中备受诟病的构造
The question is always:
What are the chances that you're realistically going to migrate from one database to another? If you're building a multi-db app it's a different story, but most apps don't ever get ported over to a new db midstream - especially when they start out with something as robust as SQL Server.
The identity construct is excellent, and there's really very few reasons why you shouldn't use it. If you're interested, I wrote a blog article on some of the common myths surrounding identity values.
The IDENTITY Property: A Much-Maligned Construct in SQL Server
是的。
它们通常按预期工作，您可以使用 DBCC CHECKIDENT 命令来操作和使用它们。
身份最常见的想法是提供一个有序的数字列表，作为主键的基础。
编辑：我对填充因子的看法是错误的，我没有考虑到所有插入都会发生在 B 树的一侧。
另外，在您修改后的问题中，您询问了从一个数据库迁移到另一个数据库的问题：
只要迁移是单向复制，身份就完全没问题。 如果您有两个需要相互复制的数据库，则 UniqueIdentifier 列可能是您的最佳选择。
请参阅：您什么时候真正被迫使用 UUID 作为设计的一部分？ 有关何时在数据库中使用 UUID 的讨论。
Yes.
They generally works as intended, and you can use the
DBCC CHECKIDENTcommand to manipulate and work with them.The most common idea of an identity is to provide an ordered list of numbers on which to base a primary key.
Edit: I was wrong about the fill factor, I didn't take into account that all of the inserts would happen on one side of the B-tree.
Also, In your revised question, you asked about migrating from one DB to another:
Identities are perfectly fine as long as the migrating is a one-way replication. If you have two databases that need to replicate to each other, a UniqueIdentifier column may be your best bet.
See: When are you truly forced to use UUID as part of the design? for a discussion on when to use a UUID in a database.
关于身份的好文章，http://www.simple- talk.com/sql/t-sql-programming/identity-columns/
IMO，现在很少需要迁移到另一个 RDBMS。 即使需要，开发可移植应用程序的最佳方法是开发一层存储过程，将应用程序与专有功能隔离：
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/02/24/writing-ansi-标准-sql-is-not-practical.aspx
Good article on identities, http://www.simple-talk.com/sql/t-sql-programming/identity-columns/
IMO, migrating to another RDBMS is rarely needed these days. Even if it is needed, the best way to develop portable applications is to develop a layer of stored procedures isolating your application from proprietary features:
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/02/24/writing-ansi-standard-sql-is-not-practical.aspx