什么是唯一标识符类型？
在SQL Server中有一个类型uniqueidentifier？这是什么？它有什么作用？这和使用主键有什么区别
In SQL server there is a type uniqueidentifier? What is this? What does it do? What is the difference between this and using primary key
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
它是一个 GUID 列。
这意味着每次您在此表中插入一行时，SQL Server 都会生成一个新的 GUID 并将其放入此栏。这确保了该值不仅在这台计算机上而且在世界上所有计算机上都是唯一的。更新：
正如 @tdammers 在评论部分所指出的，插入行时不会自动生成新的 GUID，但可以通过定义
newid()作为列的默认值。It is a GUID column.
This means that every time you insert a row into this table SQL Server will generate a new GUID and put it into this column. This ensures that the value is unique and not only on this computer but over all the computers on the world.UPDATE:
As pointed out by @tdammers in the comments section new GUIDs are not automatically generated when inserting rows but this effect could be achieved by defining
newid()as default value for the column.uniqueidentifier是GUID的 SQL Server 名称（请参阅 http://en.wikipedia.org/wiki/Globally_unique_identifier）。中心思想是 GUID 足够大并且具有足够的熵，使得两个随机生成的 GUID 发生冲突的可能性极小。为了进一步减少冲突的可能性，大多数 GUID 生成器在散列过程中包含一些唯一标识主机的值，例如主 NIC 的 MAC 地址，并维护机器范围的计数器以避免同一机器上生成的 GUID 之间发生冲突。在 SQL Server 中，
uniqueidentifier只是另一种数据类型，如int、varchar等，但其特性使其适合作为主要数据类型钥匙。它比典型的自动增量整数大得多，但它使复制和迁移更加容易 - 如果使用 GUID，来自两个以前不相关的数据库的数据不会在主键上产生冲突，因为每个 GUID（概念上）都是全局唯一的。使用自动递增主键，您肯定会遇到重叠，因为两个表都曾经从 1 开始，并且修改主键值意味着您还必须修改引用该记录的任何外键值。这是可以做到的，但是比简单地使用 GUID 困难得多。A
uniqueidentifieris the SQL Server name for aGUID(see http://en.wikipedia.org/wiki/Globally_unique_identifier). The central idea is that a GUID is large enough and has enough entropy to make collisions of two randomly generated GUIDs extremely unlikely. To further reduce the likelyhood of collisions, most GUID generators include some value that uniquely identifies the host in the hashing process, such as the primary NIC's MAC address, and maintain a machine-wide counter to avoid collisions between GUIDs generated on the same machine.In SQL Server, a
uniqueidentifieris just another data type, likeint,varchar, etc., but its characteristics make it suitable as a primary key. It is much larger than a typical auto-increment integer, but it makes replication and migration much easier - data from two previously unrelated databases will not produce collisions on the primary keys if you use GUIDs, because each GUID is (conceptually) globally unique. With auto-incrementing primary keys, you will certainly get overlaps, because both tables once started at 1, and modifying the primary key values means you also have to modify any foreign key values referencing this record. It can be done, but it's much harder than simply using GUIDs.正如您已经提到的，以及其他人也指出的 - SQL Server 中的 GUID /
uniqueidentifier数据类型最初似乎是主键的绝佳候选者 - 让我警告您它存在的一些问题您以后可能会后悔的道路：您需要将主键分开，它是一个逻辑构造 - 它唯一标识您的行，它必须是唯一且稳定的并且不为空。 GUID 也适用于主键 - 因为它保证是唯一的。如果您使用 SQL Server 复制，则将 GUID 作为主键是一个不错的选择，因为在这种情况下，您无论如何都需要唯一标识 GUID 列。
SQL Server 中的聚集键是一种物理结构，用于数据的物理排序，而且要正确使用要困难得多。通常，SQL Server 索引女王 Kimberly Tripp 还需要一个良好的集群键，该键必须是唯一的、稳定的、尽可能窄的，并且理想情况下是不断增加的（INT IDENTITY 就是这样）。
请在此处查看她有关索引的文章：
以及 Jimmy Nilsson 的 GUID 作为主键的成本
对于集群键，因为它很宽，完全随机，因此会导致不良的索引碎片和较差的性能。此外，聚集键行也存储在每个非聚集（附加）索引的每个条目中，因此您确实希望保持较小的值 - GUID 为 16 字节，而 INT 为 4 字节，并且对于多个非聚集索引和数百万行，这会产生巨大的差异。
在 SQL Server 中，默认情况下您的主键是集群键 - 但并非必须如此。您可以轻松地使用 GUID 作为非聚集主键，并使用 INT IDENTITY 作为聚集键 - 只需要稍微注意一下即可。
As you mentioned already, and others also pointed out - the GUID /
uniqueidentifierdata type in SQL Server at first appears to be a great candidate for primary key - let me warn you about some problems it has down the road that you might regret later on:You need to keep apart the primary key which is a logical construct - it uniquely identifies your rows, it has to be unique and stable and NOT NULL. A GUID works well for a primary key, too - since it's guaranteed to be unique. A GUID as your primary key is a good choice if you use SQL Server replication, since in that case, you need an uniquely identifying GUID column anyway.
The clustering key in SQL Server is a physical construct is used for the physical ordering of the data, and is a lot more difficult to get right. Typically, the Queen of Indexing on SQL Server, Kimberly Tripp, also requires a good clustering key to be uniqe, stable, as narrow as possible, and ideally ever-increasing (which a INT IDENTITY is).
See her articles on indexing here:
and also see Jimmy Nilsson's The Cost of GUIDs as Primary Key
A GUID is a horribly bad choice for a clustering key, since it's wide, totally random, and thus leads to bad index fragmentation and poor performance. Also, the clustering key row(s) is also stored in each and every entry of each and every non-clustered (additional) index, so you really want to keep it small - GUID is 16 byte vs. INT is 4 byte, and with several non-clustered indices and several million rows, this makes a HUGE difference.
In SQL Server, your primary key is by default your clustering key - but it doesn't have to be. You can easily use a GUID as your NON-Clustered primary key, and an INT IDENTITY as your clustering key - it just takes a bit of being aware of it.