I have a brown-field SQL Server 2005 database that uses standard, unsorted GUIDs as the majority of the primary keys values and also in clustered indexes (which is bad for performance).
我有一个棕色字段SQL Server 2005数据库,它使用标准的,未排序的GUID作为大多数主键值以及聚簇索引(这对性能有害)。
How should I go about changing these to sequential GUIDs? One of the challenges would be to replace all of the foreign key values as I change each the primary key.
我该如何将这些更改为顺序GUID?其中一个挑战是在更改每个主键时替换所有外键值。
Do you know of any tools or scripts to perform this type of conversion?
您知道要执行此类转换的任何工具或脚本吗?
3 个解决方案
#1
remember that you can only use the newsequentialid() function as a default
请记住,您只能使用newsequentialid()函数作为默认值
so create a new table with 2 columns. Insert the key from you original table into this one (leave the other column out it will fill itself)
所以创建一个包含2列的新表。将原始表中的密钥插入此表中(将另一列留下,它将自行填充)
join back to the original table and update the PK with the newsequantialid, if you have cascade update the FKs should update by themselves
加入原始表并使用newsequantialid更新PK,如果你有级联更新,FK应该自己更新
#2
How do you know it's bad for performance?
你怎么知道它对性能有害?
The advantage of GUIDs is that you don't have to worry about multiple processes creating records at the same time. It can simplify your code considerably, depending on the program.
GUID的优点是您不必担心多个进程同时创建记录。它可以显着简化您的代码,具体取决于程序。
#3
SequentialGuids are best for performance when the Guid is the PK. This is the reason behind their existence.
当Guid是PK时,SequentialGuids最适合表现。这就是他们存在的原因。
#1
remember that you can only use the newsequentialid() function as a default
请记住,您只能使用newsequentialid()函数作为默认值
so create a new table with 2 columns. Insert the key from you original table into this one (leave the other column out it will fill itself)
所以创建一个包含2列的新表。将原始表中的密钥插入此表中(将另一列留下,它将自行填充)
join back to the original table and update the PK with the newsequantialid, if you have cascade update the FKs should update by themselves
加入原始表并使用newsequantialid更新PK,如果你有级联更新,FK应该自己更新
#2
How do you know it's bad for performance?
你怎么知道它对性能有害?
The advantage of GUIDs is that you don't have to worry about multiple processes creating records at the same time. It can simplify your code considerably, depending on the program.
GUID的优点是您不必担心多个进程同时创建记录。它可以显着简化您的代码,具体取决于程序。
#3
SequentialGuids are best for performance when the Guid is the PK. This is the reason behind their existence.
当Guid是PK时,SequentialGuids最适合表现。这就是他们存在的原因。