SQL Server相关表主键由GUID改为BigInt的方法
我有两个包含 10-2000 万行的表，它们具有 GUID 主键，并且至少有 12 个通过外键关联的表。每个基表有 10-20 个索引。
我们正在从 GUID 主键转向 BigInt 主键。我想知道是否有人对方法有任何建议。现在，这是我正在考虑的方法：
- 删除所有涉及的表上的所有索引和 fkey。
- 将“NewPrimaryKey”列添加到每个表中
- 在两个基表上创建密钥标识
- 编写数据更改脚本“更新表 x，设置 NewPrimaryKey = y，其中 OldPrimaryKey = z
- 将原始主键重命名为“oldprimarykey”
- 将“NewPrimaryKey”列重命名为“PrimaryKey” '
- 脚本返回所有索引和 fkey
这看起来是一个好方法吗？ 有谁知道有一个工具或脚本可以帮助解决这个问题？
TD：根据附加信息进行编辑。请参阅此博客文章，该文章介绍了 GUID 为主时的方法：http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid /12749/Default.aspx
I have two tables with 10-20 million rows that have GUID primary keys and at leat 12 tables related via foreign key. The base tables have 10-20 indexes each.
We are moving from GUID to BigInt primary keys. I'm wondering if anyone has any suggestions on an approach. Right now this is the approach I'm pondering:
- Drop all indexes and fkeys on all the tables involved.
- Add 'NewPrimaryKey' column to each table
- Make the key identity on the two base tables
- Script the data change "update table x, set NewPrimaryKey = y where OldPrimaryKey = z
- Rename the original primarykey to 'oldprimarykey'
- Rename the 'NewPrimaryKey' column 'PrimaryKey'
- Script back all the indexes and fkeys
Does this seem like a good approach?
Does anyone know of a tool or script that would help with this?
TD: Edited per additional information. See this blog post that addresses an approach when the GUID is the Primary: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12749/Default.aspx
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你的方法就是我会做的。
你真的需要bigint吗？常规 4 字节 int 将达到 20 亿 (2,147,483,647)。
int、bigint、smallint 和tinyint
Your approach is how I would do it.
Do you really need bigint? a regular 4 byte int will go to 2 billion (2,147,483,647).
int, bigint, smallint, and tinyint
听起来这个策略肯定会起作用——删除约束，从它们下面更改列（类型更改，名称保持不变），然后重新创建约束是相当优雅的。
最终目标是删除 GUID 列吗？如果是这样，除非复制或重建表，否则您实际上不会回收空间，因此可能需要进行以下调整：
...
4.编写数据更改脚本“更新表x，设置NewPrimaryKey = y，其中OldPrimaryKey = z
5.删除原始主键为“oldprimarykey”
6.将“NewPrimaryKey”列重命名为“PrimaryKey”
7.编写所有索引和fkey的脚本（构建聚集索引“重建”表）
8.对于所有没有聚集索引的表，做一些事情来确保它们得到重建并回收它们的空间（例如构建然后删除聚集索引）
不用说，在生产上运行之前在开发盒上进行测试！
It certainly sounds like this strategy would work -- dropping the constraints, changing the column out from underneath them (type changes, name remains the same), and then recreating the constraints is fairly elegant.
Is the goal to ultimately drop the GUID columns? If so, you won't actually reclaim the space unless the tables are copied or rebuilt, so maybe the following adjustment:
...
4.Script the data change "update table x, set NewPrimaryKey = y where OldPrimaryKey = z
5.Drop the original primarykey to 'oldprimarykey'
6.Rename the 'NewPrimaryKey' column 'PrimaryKey'
7.Script back all the indexes and fkeys (building clustered indexes "rebuilds" tables)
8.For all tables that don't have clustered indexes, do something to make sure they get rebuilt and their space is reclaimed (such build and then drop a clustered index)
Needless to say, test it on a dev box before running on Production!
我还要补充一点：
在开始之前，请确保您有良好的当前备份。
将服务器更改为在单用户模式下运行（首先通知用户中断期）。
您不希望用户在此期间尝试输入数据。
I'd also add:
Make sure you have a good current backup before starting.
Change the server to run in single user mode (notify users of an outage period first).
You do not want users to try to enter data while this is going on.