指南与自动递增整数
我想知道在处理父级 > 的 ID 方面是否有最佳编码实践代码中的子对象，其中数据库记录使用自动递增的 int 作为 ID（在初始保存时）。当然，在代码中，您无法猜测这个 ID 可能是什么，因此必须将其留空，并且可能将所有这些项目保存在事务中，首先获取父 ID，然后将其设置在所有子项上，然后再将它们保存
在另一个上hand 在代码中更容易处理，因为当然，您可以愉快地首先生成 Id 并将其设置在所有内容上，然后无需担心地保存..
有没有一种很好的简单方法来使用自动整数作为对象来处理代码中的对象数据库密钥？
谢谢
I am wondering if there is a best coding practice in regard to dealing with IDs for parent > child objects in the code, where the DB records use an auto incremented int as the ID (on the initial save). Of course when in the code you are unable to guess what this ID might be and therefore must leave it blank and presumably save all these items in a transaction grabbing the parent ID first and then setting it on all children before saving them
Guids on the other hand are much easier to deal with in the code, as of course you can happily generate the Id first and set it on everything and save without worry..
is there a nice easy way to deal with objects in code using auto-ints as their db keys?
thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
GUID 似乎是主键的自然选择 - 如果您确实必须这样做，您可能会争论将其用作表的主键。我强烈建议不要这样做，即使用 GUID 列作为聚集键，这是 SQL Server 默认执行的操作，除非您明确告诉它不要这样做。
您确实需要区分两个问题：
1）主键是一个逻辑构造 - 唯一且可靠地标识表中每一行的候选键之一。这实际上可以是任何东西——一个 INT、一个 GUID、一个字符串——选择对你的场景最有意义的。
2）聚集键（定义表上“聚集索引”的一个或多个列） - 这是一个物理与存储相关的东西，这里是一个小的、稳定、不断增加的数据类型是您的最佳选择 - INT 或 BIGINT 作为您的默认选项。
默认情况下，SQL Server 表上的主键也用作聚簇键 - 但不必如此！我个人看到，将以前基于 GUID 的主键/聚集键分解为两个单独的键 - GUID 上的主（逻辑）键和单独的 INT IDENTITY(1, 1）专栏。
作为 Kimberly Tripp - 女王索引 - 和其他人已经说过很多次 - GUID 作为集群键并不是最佳的，因为由于它的随机性，它将导致大量页面和索引碎片以及通常较差的性能。
是的，我知道 - SQL Server 2005 及更高版本中有
newsequentialid()- 但即便如此，它也不是真正完全顺序的，因此也遇到了与 GUID 相同的问题 - 只是稍微不那么突出，所以。然后还有另一个问题需要考虑：表上的聚集键也将添加到表上每个非聚集索引的每个条目中 - 因此您确实希望确保它尽可能小。通常，具有 2+ 十亿行的 INT 对于绝大多数表来说应该足够了 - 与作为集群键的 GUID 相比，您可以在磁盘和服务器内存中节省数百兆字节的存储空间。
快速计算 - 使用 INT 与 GUID 作为主键和聚集键：
总计：25 MB 与. 106 MB - 而且仅在一个表上！
还有一些值得深思的东西 - Kimberly Tripp 写的很棒的东西 - 读它，再读它，消化它！这确实是 SQL Server 索引的福音。
另外：从 C# / .NET 的角度来看 - 这取决于您访问 SQL Server 数据库的方式。如果您使用Linq-to-SQL或Entity Framework v4之类的东西，您的.NET对象将使用插入的ID自动更新（来自您的
INT IDENTITY< /code> 列） - 您无需执行任何操作。所以对我来说，这就是你应该感到需要使用 GUID 的又一个原因了……GUID 作为 SQL Server 集群键非常糟糕 - 不仅仅是糟糕 - 真的，真的，AWFUL
GUIDs may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.
You really need to keep two issues apart:
1) the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.
2) the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.
By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based Primary / Clustered Key into two separate key - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.
As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.
Yes, I know - there's
newsequentialid()in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.
Quick calculation - using INT vs. GUID as Primary and Clustering Key:
TOTAL: 25 MB vs. 106 MB - and that's just on a single table!
Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.
Also: from a C# / .NET point of view - it depends on how you access your SQL Server database. If you use something like Linq-to-SQL or Entity Framework v4, your .NET objects will be automagically updated with the inserted IDs (from your
INT IDENTITYcolumn) - without you having to do anything at all. So to me, that's one less reason why you should feel the need to use GUID's....GUID's are horribly bad as SQL Server clustering keys - not just bad - really, truly, AWFUL
与 auto incs 相比，GUID 有几个“编码”优势。
首先它是解耦的，你不需要访问数据库来获取几乎肯定唯一的ID。
因此，您可以在内存中创建一条新记录，现在知道它并将实际存储传递给某个服务，然后愉快地使用它在本地添加相关数据，然后将其传递给相同或另一个其他服务。
像 EF 这样的东西在幕后为您解决的推论是必须在数据库中插入记录，然后获取分配给它的 DBMS 标识以传递到下游功能。如果您有另一个唯一的密钥并且 auto inc 是替代项，您可以避免这种情况，但这无论如何都不是免费的午餐。
如果我没有进行分布式，并且我的应用程序必须连接到数据库，并且 auto inc 是真正的代理（不作为订单号或类似的数字公开），并且 int 覆盖了范围，并且不可能假设两个或我的客户合并并想要合并他们的数据库，那么我就不会费心去指导。
就我而言，处理一个问题是另一个问题。虽然存在一些问题，但与处理不唯一的唯一密钥相比，这些问题是微不足道的。
There are a couple of "coding" advantages to GUIDs over auto incs.
First it's decoupled, you don't need to get to the database to get an almost certainly unique id.
So you can create a new record in memory, have it now know and the pass the actual storage to some service, and then happily use it to say add related data locally and then pass that to the same or another other service.
The corrolary which things like EF sort out for you under the covers is dealing with having to insert the record in the db and then get the identity teh DBMS assigned to it to pass to down stream functionality. You can avoid this if you have another unique key and the auto inc is a surrogate, but that's not a free lunch any way.
If I wasn't doing distributed and my app had to be connected to the database, and the auto inc is a true surrogate (not exposed as order number or some such numptiness), and int covered the range, and there was no possiblity of say two or my customers merging and wanting to merge their databases, then I wouldn't bother with a guid.
Coping with having one being another question as far as I'm concerned. There are issues, but they are trivial compared to dealing with your unique key not being unique.