从int移到GUID作为主键 [英] Moving from ints to GUIDs as primary keys
问题描述
我使用带有整数主键的几个引用表.现在,我想将int更改为GUID,保留所有引用不变.最简单的方法是什么?
I use several referenced tables with integer primary keys. Now I want to change ints to GUIDs leaving all references intact. What is the easiest way to do it?
谢谢!
添加
我确实大致了解该过程,因此我需要更详细的建议,例如,如何填充新的GUID列.使用默认值newid()是正确的,但是对于已经存在的行呢?
I do understand the process in general, so I need more detailed advices, for example, how to fill new GUID column. Using default value newid() is correct, but what for already existing rows?
推荐答案
- 为Guid创建一个新列 主表中的值.使用 将其作为uniqueidentifier数据类型 不为null,默认值为newid(),因此 所有现有的行都将被填充.
- 创建新的uniqueidentifier列 在子表中.
- 运行更新语句以使用exisitng int关系引用实体来建立公会关系.
- 删除原始int列.
- Create a new column for the guid value in the master table. Use the uniqueidentifier data type, make it not null with a newid() default so all existing rows will be populated.
- Create new uniqueidentifier columns in the child tables.
- Run update statements to build the guild relationships using the exisitng int relationships to reference the entities.
- Drop the original int columns.
此外,由于Guid不像int标识列那样连续,因此请在数据/索引页中保留一些空间(指定fillfactor <100).这意味着插入内容可以在数据范围内的任何位置,并且如果页面100%充满,则会导致页面拆分.
In addition, leave some space in your data/index pages (specify fillfactor < 100) as guids are not sequential like int identity columns are. This means inserts can be anywhere in the data range and will cause page splits if your pages are 100% full.
这篇关于从int移到GUID作为主键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!