blocks|key|3773758|text|Guids肯定会比标准整数键慢(并且使用更多的内存)，但这是否是一个问题将取决于您的系统将看到的负载类型。根据后端DB的不同，索引guid字段可能会出现问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3773759|使用guids简化了一大类问题，但是你要为它的部分性能和可调试性付出代价--在那些测试查询中输入guids将会很快过时！|3773760|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Guids will certainly be slower (and use more memory) than standard integer keys, but whether or not that is an issue will depend on the type of load your system will see. Depending on your backend DB there may be issues with indexing guid fields.

Using guids simplifies a whole class of problems, but you pay for it part will performance and also debuggability - typing guids into those test queries will get old real fast!

blocks|key|5184491|text|这是GUID的完美用法。唯一的缺点是在INTs上使用GUID时会有轻微的复杂性，以及细微的大小差异(16字节与4字节)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5184492|我认为这两个都不是什么大问题。|5184493|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

This is a perfectly good use of GUIDs. The only draw backs would be a slight complexity in working with GUIDs over INTs and the slight size difference (16 bytes vs 4 bytes).

I don't think either of those are a big deal.

blocks|key|716939|text|后端将是SQL+Server+2005|type|unstyled|depth|inlineStyleRanges|entityRanges|data|716940|前端/应用程序逻辑将是.Net|716941|除了GUID之外，您还能想到其他方法来解决脱机计算机将新数据同步回中央数据库时发生的“合并”问题吗？|716942|我的意思是，如果键是整型的，我将不得不在导入时对所有内容重新编号。GUID不会让我这么做的。|716943|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

The backend will be SQL Server 2005 
Frontend / Application Logic will be .Net

Besides GUIDs, can you think of other ways to resolve the "merge" that happens when the offline computer syncs the new data back into the central database? 
I mean, if the keys are INTs, i'll have to renumber everything when importing basically. GUIDs will spare me of that.

blocks|key|722259|text|当我们必须将两个数据库合并为一个数据库时，使用GUID为我们节省了大量工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|722260|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Using GUIDs saved us a lot of work when we had to merge two databases into one.

blocks|key|5184591|text|如果您的数据库足够小，可以下载到笔记本电脑上并脱机使用，那么您可能不需要太担心int和Guid之间的性能差异。但是不要低估int在开发和故障排除系统时有多有用！无论您是否使用Guids，您都可能需要提出一些相当复杂的导入/同步逻辑，因此它们可能没有您想象的那么有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5184592|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If your database is small enough to download to a laptop and work with it offline, you probably don't need to worry too much about the performance differences between ints and Guids. But do not underestimate how useful ints are when developing and troubleshooting a system! You will probably need to come up with some fairly complex import/synch logic regardless of whether or not you are using Guids, so they might not help as much as you think.

blocks|key|2928935|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2928936|通过GUID键访问这些表会很慢吗？|blockquote|2928937|2928938|2928939|GUID还存在其他问题，您会看到GUID不是连续的，因此插入将分散在各处，这会导致页面拆分和索引碎片|2928940|在SQL+Server2005MS中引入了NEWSEQUENTIALID()来解决此问题，唯一的问题可能是您只能在表中使用NEWSEQUENTIALID作为默认值|2928941|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|L|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|M|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|N|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|O|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|P|7|@]|8|@]|9|$]]|$1|H|3|I|4|5|6|Q|7|@]|8|@]|9|$]]|$1|J|3|-4|4|5|6|R|7|@]|8|@]|9|$]]]|K|$]]

<blockquote>
 Will access to these tables through
 the GUID key be slow?
</blockquote>

There are other problems with GUIDs, you see GUIDs are not sequential, so inserts will be scattered all over the place, this causes page splits and index fragmentation

In SQL Server 2005 MS introduced NEWSEQUENTIALID() to fix this, the only problem for you might be that you can only use NEWSEQUENTIALID as a default value in a table

blocks|key|717065|text|使用Guids作为主键是可以接受的，并且被认为是一种相当标准的实践，原因与您正在考虑它们的原因相同。它们可能会被过度使用，这会使调试和管理变得有点单调乏味，所以如果可能的话，尽量将它们排除在代码表和其他参考数据之外。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|717066|您必须关注的是人类可读的标识符。guid不能被人交换-如果是guid，你能想象尝试通过电话确认你的订单号吗？因此，在离线情况下，您可能仍然需要生成一些+-例如发布者(工作站/用户)+id和一些序列号，因此订单号可能是123-5678+-。|717067|但是，这可能无法满足具有序列号的业务需求。事实上，监管要求是可以影响的-一些法规(可能是SOX)要求发票编号是连续的。在这种情况下，可能有必要生成一种形式编号，该编号稍后在系统同步时进行修复。你可能会得到包含OrderId+(Guid)、OrderNo+(int)、ProformaOrderNo+(varchar)的表--一些复杂性可能会潜入其中。|717068|至少将guids作为主键意味着当同步最终发生时，您不必进行大量的级联更新-您只需更新人类可读的数字即可。|717069|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Using Guids as primary keys is acceptable and is considered a fairly standard practice for the same reasons that you are considering them. They can be overused which can make things a bit tedious to debug and manage, so try to keep them out of code tables and other reference data if at all possible.

The thing that you have to concern yourself with is the human readable identifier. Guids cannot be exchanged by people - can you imagine trying to confirm your order number over the phone if it is a guid? So in an offline scenario you may still have to generate something - like a publisher (workstation/user) id and some sequence number, so the order number may be 123-5678 -. 

However this may not satisfy business requirements of having a sequential number. In fact regulatory requirements can be and influence - some regulations (SOX maybe) require that invoice numbers are sequential. In such cases it may be neccessary to generate a sort of proforma number which is fixed up later when the systems synchronise. You may land up with tables having OrderId (Guid), OrderNo (int), ProformaOrderNo (varchar) - some complexity may creep in.

At least having guids as primary keys means that you don't have to do a whole lot of cascading updates when the sync does eventually happen - you simply update the human readable number.

blocks|key|2929076|text|@Simon，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2929077|你提出了非常好的观点。我已经在考虑我在离线时生成的“临时”“人类可读”数字，我将在同步时重新创建这些数字。但是我想避免使用外键，等等。|2929078|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

@Simon, 

You raise very good points. I was already thinking about the "temporary" "human-readable" numbers i'd generate while offline, that i'd recreate on sync. But i wanted to avoid doing with with foreign keys, etc.

blocks|key|717123|text|我会开始考虑SQL+Server+Compact+Edition来解决这个问题！它可以帮助你解决所有的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|717124|Data+Storage+Architecture+with+SQL+Server+2005+Compact+Edition|offset|length|717125|它是专门为|717126|717127|场力应用(FFA)。FFA通常共享以下一个或多个属性|blockquote|717128|717129|它们允许用户在与后端网络断开连接的情况下执行其工作功能-在客户现场、在路上、在机场或在家中。|717130|FFA通常是为偶尔连接而设计的，这意味着当用户运行客户端应用程序时，他们不需要有任何类型的网络连接。FFA通常涉及多个客户端，这些客户端可以在连接和断开模式下同时访问和使用后端数据库中的数据。|717131|FFA必须能够将数据从后端数据库复制到客户端数据库以提供离线支持。当应用程序能够连接到网络时，它们还需要能够将修改、添加或删除的数据记录从客户端复制到服务器|717132|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/bb380177.aspx^0|0|0|1Q|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@$D|12|E|13|1|14]]|A|$]]|$1|F|3|G|5|6|7|15|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|17|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|18|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

i would start to look at SQL Server Compact Edition for this! It helps with all of your issues. 

<a href="http://msdn.microsoft.com/en-us/library/bb380177.aspx" rel="nofollow noreferrer">Data Storage Architecture with SQL Server 2005 Compact Edition</a>

It specifically designed for 

<blockquote>
 Field force applications (FFAs). FFAs
 usually share one or more of the
 following attributes
 
 They allow the user to perform their
 job functions while disconnected from
 the back-end network—on-site at a
 client location, on the road, in an
 airport, or from home. 
 
 FFAs are usually designed for
 occasional connectivity, meaning that
 when users are running the client
 application, they do not need to have
 a network connection of any kind. FFAs
 often involve multiple clients that
 can concurrently access and use data
 from the back-end database, both in a
 connected and disconnected mode. 
 
 FFAs must be able to replicate data
 from the back-end database to the
 client databases for offline support.
 They also need to be able to replicate
 modified, added, or deleted data
 records from the client to the server
 when the application is able to
 connect to the network
</blockquote>

blocks|key|2929171|text|首先想到的是:微软不是设计了DataSet和DataAdapter模型来支持这样的场景吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2929172|我相信我读到微软将他们的ADO记录集模型更改为当前的DataSet模型，因此它在脱机时也能很好地工作。还有这个Sync+Services+for+ADO.NET|offset|length|2929173|我相信我见过使用DataSet模型的代码，该模型也使用外键，并且在使用DataAdapter时它们仍然可以完美地同步。虽然还没有尝试过同步服务，但我认为你也可以从中受益。|2929174|希望这能有所帮助。|2929175|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/sync/bb887608.aspx^0|0|1J|P|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

First thought that comes to mind: Hasn't MS designed the DataSet and DataAdapter model to support scenarios like this?

I believe I read that MS changed their ADO recordset model to the current DataSet model so it works great offline too. And there's also this <a href="http://msdn.microsoft.com/en-us/sync/bb887608.aspx" rel="nofollow noreferrer">Sync Services for ADO.NET</a>

I believe I have seen code that utilizes the DataSet model which also uses foreign keys and they still sync perfectly when using the DataAdapter. Havn't try out the Sync Services though but I think you might be able to benefit from that too.

Hope this helps.

blocks|key|717179|text|@SqlMenace|type|unstyled|depth|inlineStyleRanges|entityRanges|data|717180|717181|+GUID还有其他问题，您可以看到GUID不是连续的，因此插入将分散在各处，这会导致页面拆分和索引碎片|blockquote|717182|717183|不是这样的。主键!=聚集索引。|offset|length|style|BOLD|717184|如果聚集索引是另一列(脑海中浮现出“inserted_on”)，那么插入将是顺序的，不会发生页面拆分或过度的碎片。|717185|entityMap^0|0|0|0|0|6|9|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|R|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|S|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

@SqlMenace

<blockquote>
 <blockquote>
 There are other problems with GUIDs, you see GUIDs are not sequential, so inserts will be scattered all over the place, this causes page splits and index fragmentation
 </blockquote>
</blockquote>

Not true. Primary key != clustered index.

If the clustered index is another column ("inserted_on" springs to mind) then the inserts will be sequential and no page splits or excessive fragmentation will occur.

blocks|key|3774558|text|您说得对，这是一个老问题，它有两个规范的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3774559|3774560|使用唯一标识符作为主键。请注意，如果您关心可读性，您可以滚动自己的惟一标识符，而不是使用GUID。唯一标识符将使用有关日期和机器的信息来生成唯一值。|unordered-list-item|3774561|使用'Actor‘%2B+identifier的组合键。每个用户都会获得一个数字参与者ID，新插入的行的键将使用该参与者ID以及下一个可用标识符。因此，如果两个参与者都插入了ID为"100“的新行，则不会违反主键约束。|3774562|3774563|就我个人而言，我更喜欢第一种方法，因为我认为组合键作为外键真的很乏味。我认为人类的可读性抱怨被夸大了--不管怎样，最终用户不应该知道任何关于你的密钥的事情！|3774564|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|N|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|P|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|L|$]]

You're correct that this is an old problem, and it has two canonical solutions:

<ul>
<li>Use unique identifiers as the primary key. Note that if you're concerned about readability you can roll your own unique identifier instead of using a GUID. A unique identifier will use information about the date and the machine to generate a unique value. </li>
<li>Use a composite key of 'Actor' + identifier. Every user gets a numeric actor ID, and the keys of newly inserted rows use the actor ID as well as the next available identifier. So if two actors both insert a new row with ID "100", the primary key constraint will not be violated.</li>
</ul>

Personally, I prefer the first approach, as I think composite keys are really tedious as foreign keys. I think the human readability complaint is overstated -- end-users shouldn't have to know anything about your keys, anyways!

blocks|key|5185152|text|@Portman默认PK+==聚集索引，创建主键约束会自动创建聚集索引，如果不想聚集，则需要指定non+clustered。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5185153|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

@Portman By default PK == Clustered Index, creating a primary key constraint will automatically create a clustered index, you need to specify non clustered if you don't want it clustered.

blocks|key|2929473|text|确保使用guid.comb+-处理索引内容。如果在那之后你正在处理性能问题，那么你很快就会成为伸缩方面的专家。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2929474|使用GUID的另一个原因是启用数据库重构。假设您决定对Customers实体应用多态性或继承或其他任何东西。现在，您希望客户和员工从Person派生，并让他们共享一个表。拥有真正唯一的标识符使得数据迁移变得简单。没有序列或整数标识字段可用。|2929475|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Make sure to utilize guid.comb - takes care of the indexing stuff. If you are dealing with performance issues after that then you will be, in short order, an expert on scaling.

Another reason to use GUIDs is to enable database refactoring. Say you decide to apply polymorphism or inheritance or whatever to your Customers entity. You now want Customers and Employees to derive from Person and have them share a table. Having really unique identifiers makes data migration simple. There are no sequences or integer identity fields to fight with.

blocks|key|722512|text|我将向您介绍What+are+the+performance+improvement+of+Sequential+Guid+over+standard+Guid?，它涵盖了GUID谈话。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|722513|考虑到人类的可读性，可以考虑分配机器ID，然后使用来自这些机器的序列号。不过，这需要管理机器ID的分配。可以在一列或两列中完成。|722514|不过，我个人更喜欢SGUID的答案。|722515|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid/170363#170363^0|6|23|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

I'm just going to point you to <a href="https://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid/170363#170363">What are the performance improvement of Sequential Guid over standard Guid?</a>, which covers the GUID talk.

For human readability, consider assigning machine IDs and then using sequential numbers from those machines as a possibility. This will require managing the assignment of machine IDs, though. Could be done in one or two columns.

I'm personally fond of the SGUID answer, though.

We are working on designing an application that is typically OLTP (think: purchasing system). However, this one in particular has the need that some users will be offline, so they need to be able to download the DB to their machine, work on it, and then sync back once they're on the LAN.

I would like to note that I know this has been done before, I just don't have experience with this particular model.

One idea I thought about was using GUIDs as table keys. So for example, a Purchase Order would not have a number (auto-numeric) but a GUID instead, so that every offline client can generate those, and I don't have clashes when I connect back to the DB.

Is this a bad idea for some reason?
Will access to these tables through the GUID key be slow?

Have you had experience with these type of systems? How have you solved this problem?

Thanks! 
Daniel

GUIDs as Primary Keys - Offline OLTP

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我们正在设计一个典型的OLTP应用程序(想想:采购系统)。但是，这个特别需要一些用户离线，因此他们需要能够将DB下载到他们的机器上，对其进行操作，然后在连接到LAN后再同步回来。我想指出的是，我知道以前也这样做过，我只是没有使用这种特殊模型的经验。我考虑过的一个想法是使用GUID作为表键。因此，例如，购买订单不会有一个...

问作为主键的GUIDs脱机OLTP
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问作为主键的GUIDs脱机OLTPEN