SQL Server 唯一标识符与整数
我一直在学习ASP.net，并且一直在使用会员系统。当它自动生成表时，我非常惊讶地发现它使用名为“uniqueIdentifier”的字段类型作为主键，而多年来我一直使用整数字段设置为自动递增的标识。
这两种方法之间有什么区别（如果有的话），为什么 .NET 似乎更喜欢唯一标识符字段？
感谢您提供任何信息！
汤姆
I've been learning ASP.net, and been using the membership system. When it auto generated the tables, I was quite suprised to see it uses a field type called 'uniqueIdentifier' as a primary key, when for many years I have been using an integer field set to be an identity that auto increments.
What is the difference (if any at all) between these two methods, and why does .NET appear to favour the unique identifier field?
Thanks for any info!
Tom
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
uniqueidentifier类型是 SQL 的 Guid 类型（对应的 BCL 类型为System.Guid）。从概念上讲，Guids 表示一个随机的 128 位数字，该数字应该是唯一的。虽然 Guid 有其批评者（严格来说，比较 guid 比比较整数稍慢），但它们的随机性使它们在复制等环境中很有用，在这种环境中使用递增密钥可能很困难。
The
uniqueidentifiertype is SQL's Guid type (the corresponding BCL type isSystem.Guid). In concept, Guids represent a random 128-bit number that is supposed to be unique.While Guid's have their detractors (comparing guids is, strictly speaking, slightly slower than comparing ints), their random nature makes them helpful in environments like replication, where using an incrementing key can be difficult.
我想说，.NET 不喜欢使用 uniqueidentifier 或 guid 作为 id，但这个特定的实现（ASP.NET SQL Server 成员资格提供程序）却喜欢这样做。我怀疑那些开发数据库的人假设数据库的使用不是用于高流量站点，或者可能需要进行大量报告的地方。
也许他们试图避免在集成现有应用程序时出现任何问题，或者避免未来应用程序拥有用户密钥的情况。这可以是任何实体的任何类型的密钥（PK、UserNumber 等）。在 ASP.NET SQL Server 实现中，发生冲突的可能性非常低/接近于零。
我了解到的一个缺点是，在 guid 上使用聚集索引无法扩展到大容量数据库。
我主要属于整数作为 PK 阵营。它们很小，使用很少的字节，并且当您的数据库需要扩展时可以很好地工作。
I'd say that .NET doesn't favour the uniqueidentifier or guid as an id, but this particular implementation (the ASP.NET SQL Server membership provider) does. I suspect that those who developed the database were working with the assumption that the db usage wasn't to be for high traffic sites, or where heavy reporting was likely to be done.
Perhaps they were trying to avoid any problems with integrating in an existing application, or a future scenario whereby your application had a key for a user. This could be any kind of key for any entity (PK, UserNumber, etc). In the ASP.NET SQL Server implementation, the likelihood of having a collision is very low/approaching zero.
The one drawback that I've learned is that having a clustered index on a guid doesn't scale to large volume databases.
I'm largely in the integer-as-PK camp. They're small, use few bytes, and work very well when your database needs to scale.
其中一个 uniqueidentifier 为 16 字节，而 int 为 4 字节。 的 URL，
如果您有一个类似于http://bla.com?UserID=1
您可以轻松猜出是什么别人的用户 ID 是这样的，所以你可以尝试 2 或 4 等，
当你有这个用户 ID
C7478034-BB60-4F5A-BE51-72AAE5A96640时，它并不那么容易，而且唯一标识符应该在所有用户中都是唯一的 如果计算机使用
NEWID()而不是NEWSEQUENTIALID()那么它们将会出现碎片和页面分割，请查看 最佳实践：使用 NewId 时不要在 UniqueIdentifier 上集群for one a uniqueidentifier is 16 bytes while an int is 4 bytes. IF you have a URL like
http://bla.com?UserID=1
you can easily guess what someone else's userid is so you can try 2 or 4 etc etc
when you have this as UserID
C7478034-BB60-4F5A-BE51-72AAE5A96640it is not as easily and also uniqueidentifiers are supposed to be unique accross all computersif they use
NEWID()instead ofNEWSEQUENTIALID()then they will get fragmentation and page splits, take a look at Best Practice: Do not cluster on UniqueIdentifier when you use NewId