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.
我一直在学习ASP.net，并且一直在使用会员系统。当它自动生成这些表时，我非常惊讶地看到它使用了一个名为“惟一识别器”的字段类型作为主键，在许多年里，我一直使用一个整数字段作为自动增量的标识。
What is the difference (if any at all) between these two methods, and why does .NET appear to favour the unique identifier field?
这两个方法之间有什么区别(如果有区别的话)，为什么。net似乎支持唯一标识符字段?
Thanks for any info!
谢谢你的任何信息!
Tom
汤姆
3 个解决方案
#1
1
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.
我想说。net不支持惟一标识符或guid作为id，而是支持这个特殊的实现(ASP)。NET SQL Server会员制提供者)。我怀疑开发这个数据库的人是在假定db的使用不会用于高流量的站点，或者可能需要进行大量报告的站点。
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.
也许他们试图避免在现有应用程序中集成的任何问题，或者在未来的场景中，应用程序对用户有一个密钥。这可以是任何实体的任何类型的键(PK、UserNumber等)。在ASP。NET SQL Server实现，发生冲突的可能性非常低/接近零。
The one drawback that I've learned is that having a clustered index on a guid doesn't scale to large volume databases.
我了解到的一个缺点是，在guid上拥有集群索引不能扩展到大型数据库。
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.
我很大程度上属于精英阵营。它们很小，使用很少的字节，并且在数据库需要扩展时工作得很好。
#2
2
The uniqueidentifier type is SQL's Guid type (the corresponding BCL type is System.Guid). In concept, Guids represent a random 128-bit number that is supposed to be unique.
惟一的标识符类型是SQL的Guid类型(对应的BCL类型是System.Guid)。在概念上，Guids表示一个被认为是唯一的128位随机数字。
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.
虽然Guid也有批评者(严格地说，比较gui比比较int稍慢)，但它们的随机性使它们在复制这样的环境中很有用，在这种环境中使用递增键是很困难的。
#3
1
What is the difference (if any at all) between these two method
这两种方法有什么区别(如果有区别的话)
for one a uniqueidentifier is 16 bytes while an int is 4 bytes. IF you have a URL like
一个惟一标识符是16字节，一个整数是4字节。如果你有一个像这样的URL
http://bla.com?UserID=1
you can easily guess what someone else's userid is so you can try 2 or 4 etc etc
你可以很容易地猜到别人的用户id是什么，这样你就可以尝试2或4等等
when you have this as UserID C7478034-BB60-4F5A-BE51-72AAE5A96640 it is not as easily and also uniqueidentifiers are supposed to be unique accross all computers
当你有这个UserID C7478034-BB60-4F5A-BE51-72AAE5A96640时，它就不那么容易了，而且唯一标识符应该是所有计算机上唯一的标识符
if they use NEWID() instead of NEWSEQUENTIALID() then they will get fragmentation and page splits, take a look at Best Practice: Do not cluster on UniqueIdentifier when you use NewId
如果他们使用NEWID()而不是NEWSEQUENTIALID()，那么他们将获得碎片和页面分割，请查看最佳实践:在使用NEWID时不要集群到惟一标识符上
#1
1
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.
我想说。net不支持惟一标识符或guid作为id，而是支持这个特殊的实现(ASP)。NET SQL Server会员制提供者)。我怀疑开发这个数据库的人是在假定db的使用不会用于高流量的站点，或者可能需要进行大量报告的站点。
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.
也许他们试图避免在现有应用程序中集成的任何问题，或者在未来的场景中，应用程序对用户有一个密钥。这可以是任何实体的任何类型的键(PK、UserNumber等)。在ASP。NET SQL Server实现，发生冲突的可能性非常低/接近零。
The one drawback that I've learned is that having a clustered index on a guid doesn't scale to large volume databases.
我了解到的一个缺点是，在guid上拥有集群索引不能扩展到大型数据库。
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.
我很大程度上属于精英阵营。它们很小，使用很少的字节，并且在数据库需要扩展时工作得很好。
#2
2
The uniqueidentifier type is SQL's Guid type (the corresponding BCL type is System.Guid). In concept, Guids represent a random 128-bit number that is supposed to be unique.
惟一的标识符类型是SQL的Guid类型(对应的BCL类型是System.Guid)。在概念上，Guids表示一个被认为是唯一的128位随机数字。
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.
虽然Guid也有批评者(严格地说，比较gui比比较int稍慢)，但它们的随机性使它们在复制这样的环境中很有用，在这种环境中使用递增键是很困难的。
#3
1
What is the difference (if any at all) between these two method
这两种方法有什么区别(如果有区别的话)
for one a uniqueidentifier is 16 bytes while an int is 4 bytes. IF you have a URL like
一个惟一标识符是16字节，一个整数是4字节。如果你有一个像这样的URL
http://bla.com?UserID=1
you can easily guess what someone else's userid is so you can try 2 or 4 etc etc
你可以很容易地猜到别人的用户id是什么，这样你就可以尝试2或4等等
when you have this as UserID C7478034-BB60-4F5A-BE51-72AAE5A96640 it is not as easily and also uniqueidentifiers are supposed to be unique accross all computers
当你有这个UserID C7478034-BB60-4F5A-BE51-72AAE5A96640时，它就不那么容易了，而且唯一标识符应该是所有计算机上唯一的标识符
if they use NEWID() instead of NEWSEQUENTIALID() then they will get fragmentation and page splits, take a look at Best Practice: Do not cluster on UniqueIdentifier when you use NewId
如果他们使用NEWID()而不是NEWSEQUENTIALID()，那么他们将获得碎片和页面分割，请查看最佳实践:在使用NEWID时不要集群到惟一标识符上