I've currently got a database with about 20 reference tables, i.e. stuff like products, assets, depots, users, etc. This information is stored in a central database and is downloaded to PDAs of engineers who are out on the road. Every table in my database has a PK of UniqueIdentifier (i.e. a GUID).
我目前有一个包含大约20个参考表的数据库,即产品,资产,软件仓库,用户等等。这些信息存储在*数据库中,并下载到外出工程师的PDA中。我的数据库中的每个表都有一个UniqueIdentifier PK(即一个GUID)。
I've realised after 2 years of product development that I never do and never will need to edit any of these ref tables. Therefor there's no need for them to have UniqueIdentifiers as primary keys.
经过两年的产品开发后我才意识到我从未做过,也从不需要编辑任何这些参考表。因此,他们不需要将UniqueIdentifiers作为主键。
As I'm serializing all my collections before I send them to the PDA over my webservice, the actual serialized data is huge due to the length of a GUID.
在我通过我的webservice将它们发送到PDA之前,我正在序列化我的所有集合,由于GUID的长度,实际的序列化数据是巨大的。
Anyway, getting to the point - I want to change all the PKs of the tables to IDENTITY Ints. Is there any easy way to do this, or am I going to have to go along the lines of severing all FKs, creating temp tables, and then writing some software to remap the data?
无论如何,要点 - 我想将表的所有PK改为IDENTITY Ints。有没有简单的方法可以做到这一点,或者我将不得不沿着切断所有FK,创建临时表,然后编写一些软件来重新映射数据的路线?
Thanks in advance.
提前致谢。
4 个解决方案
#1
If you have FK referencing your PKs, you're in big trouble. I'm guessing you went with GUIDs because of the distributed nature of your application (PDAs) and that perhaps the original intent might have been for new data to be added from those PDAs, in which case GUIDs are ideal. The down side is that you can't index on a GUID.
如果你有FK引用你的PK,那你就麻烦大了。我猜你是因为你的应用程序(PDA)的分布式特性而使用GUID,并且可能最初的意图可能是从这些PDA中添加新数据,在这种情况下GUID是理想的。缺点是您无法在GUID上编制索引。
My suggestion is not to use identity ints to begin with. First up, disable your FKs. Add a new column that is INT (not identity) for all references to that PK (ie all tables that have a FK to it as well). Then for each GUID, create a new int (the next value) and insert it against the guid, and all references to the guid in other tables. Then turn on new FKs for the ints, and then shift your PK to your new int column. Finally delete your GUID columns, and then rebuild your index.
我的建议是不要先使用身份注册。首先,禁用你的FK。为所有对该PK的引用添加一个新的INT(非标识)列(即所有对其具有FK的表)。然后为每个GUID创建一个新的int(下一个值)并将其插入guid,并在其他表中引用guid。然后打开整个新的FK,然后将PK转移到新的int列。最后删除GUID列,然后重建索引。
Hope that wasn't too garbled.
希望不要太乱码。
#2
First - LOL. I know a quite a few other people in your company... 'Wow - GUIDs are cool, I'm going to use them everywhere...' At least you recognize one of the issues with going this route.
首先 - 大声笑。我知道你们公司里有很多其他人......“哇 - GUID很酷,我会到处使用它们......”至少你认识到走这条路的问题之一。
Second - you may find these commands helpful:
第二 - 您可能会发现这些命令很有用:
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
<do stuff here>
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Third - the part where it says -do stuff here-
第三 - 它在这里说东西的部分 -
1) add an int autoincrementing identity column to ref table
2) set the current fk guid in data tables to the db key you just created in step 1
3) rinse/repeat for each ref table/data table combination
4) after all is done, refactor code to use id (int) instead of guid
I'd leave the guids alone for a few months just in case you need to adjust/fix anything. At some point in the future, you should be able to delete the guids from your ref tables.
为了防止你需要调整/修理任何东西,我会单独留下guids几个月。在将来的某个时候,您应该能够从ref表中删除guid。
#3
Well, what I do not understand is that on the one hand you state that you do not need the Id columns but on the other you are sending them via your web service.
好吧,我不明白的是,一方面你声明你不需要Id列,但另一方面你通过你的网络服务发送它们。
So, I would first look into my queries to see what is fetched and optimize there in terms of the returned resultset.
所以,我首先会查看我的查询以查看所获取的内容并根据返回的结果集进行优化。
Changing the PK's is big effort, but with the help of "SQL Compare" it can be done.
改变PK是很大的努力,但在“SQL Compare”的帮助下,它可以完成。
#4
As long as you have some field (or set of fields) that is unique, you can just reassign the PK to that field. i.e. if you don't need a guid, you probably don't need a IDENTITY surrogate key either. The easiest thing to do overall is just to use a PK on one or more non-null existing columns that are unique.
只要您有一些独特的字段(或字段集),您就可以将PK重新分配给该字段。即如果你不需要guid,你可能也不需要IDENTITY代理键。最简单的做法就是在一个或多个非空的现有列上使用PK,这些列是唯一的。
Otherwise, just add an IDENTITY column and reassign the PK to it. (It will populate when you save the altered table.) Then you can delete the column with the guid.
否则,只需添加IDENTITY列并将PK重新分配给它。 (当您保存更改的表时,它将填充。)然后您可以使用guid删除列。
(I'm assuming we're having this discussion because your guid column isn't currently used for anything. Even if it's a FK in another table, if you have unique columns to join on, you don't need it there, either.)
(我假设我们正在进行此讨论,因为您的guid列目前尚未用于任何内容。即使它是另一个表中的FK,如果您有唯一的列加入,您也不需要它,或者。)
If none of that matches your situation, post again with more detail. This can be done with little fuss or bother.
如果这些都不符合您的情况,请再次发布更多细节。这可以通过简单的麻烦或麻烦来完成。
(I'm not sure why you thought you needed uniqueidentifiers for your primary keys even if the tables were to be edited, BTW.)
(我不确定为什么你认为你的主键需要uniqueidentifier,即使要编辑表格,BTW。)
#1
If you have FK referencing your PKs, you're in big trouble. I'm guessing you went with GUIDs because of the distributed nature of your application (PDAs) and that perhaps the original intent might have been for new data to be added from those PDAs, in which case GUIDs are ideal. The down side is that you can't index on a GUID.
如果你有FK引用你的PK,那你就麻烦大了。我猜你是因为你的应用程序(PDA)的分布式特性而使用GUID,并且可能最初的意图可能是从这些PDA中添加新数据,在这种情况下GUID是理想的。缺点是您无法在GUID上编制索引。
My suggestion is not to use identity ints to begin with. First up, disable your FKs. Add a new column that is INT (not identity) for all references to that PK (ie all tables that have a FK to it as well). Then for each GUID, create a new int (the next value) and insert it against the guid, and all references to the guid in other tables. Then turn on new FKs for the ints, and then shift your PK to your new int column. Finally delete your GUID columns, and then rebuild your index.
我的建议是不要先使用身份注册。首先,禁用你的FK。为所有对该PK的引用添加一个新的INT(非标识)列(即所有对其具有FK的表)。然后为每个GUID创建一个新的int(下一个值)并将其插入guid,并在其他表中引用guid。然后打开整个新的FK,然后将PK转移到新的int列。最后删除GUID列,然后重建索引。
Hope that wasn't too garbled.
希望不要太乱码。
#2
First - LOL. I know a quite a few other people in your company... 'Wow - GUIDs are cool, I'm going to use them everywhere...' At least you recognize one of the issues with going this route.
首先 - 大声笑。我知道你们公司里有很多其他人......“哇 - GUID很酷,我会到处使用它们......”至少你认识到走这条路的问题之一。
Second - you may find these commands helpful:
第二 - 您可能会发现这些命令很有用:
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
<do stuff here>
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Third - the part where it says -do stuff here-
第三 - 它在这里说东西的部分 -
1) add an int autoincrementing identity column to ref table
2) set the current fk guid in data tables to the db key you just created in step 1
3) rinse/repeat for each ref table/data table combination
4) after all is done, refactor code to use id (int) instead of guid
I'd leave the guids alone for a few months just in case you need to adjust/fix anything. At some point in the future, you should be able to delete the guids from your ref tables.
为了防止你需要调整/修理任何东西,我会单独留下guids几个月。在将来的某个时候,您应该能够从ref表中删除guid。
#3
Well, what I do not understand is that on the one hand you state that you do not need the Id columns but on the other you are sending them via your web service.
好吧,我不明白的是,一方面你声明你不需要Id列,但另一方面你通过你的网络服务发送它们。
So, I would first look into my queries to see what is fetched and optimize there in terms of the returned resultset.
所以,我首先会查看我的查询以查看所获取的内容并根据返回的结果集进行优化。
Changing the PK's is big effort, but with the help of "SQL Compare" it can be done.
改变PK是很大的努力,但在“SQL Compare”的帮助下,它可以完成。
#4
As long as you have some field (or set of fields) that is unique, you can just reassign the PK to that field. i.e. if you don't need a guid, you probably don't need a IDENTITY surrogate key either. The easiest thing to do overall is just to use a PK on one or more non-null existing columns that are unique.
只要您有一些独特的字段(或字段集),您就可以将PK重新分配给该字段。即如果你不需要guid,你可能也不需要IDENTITY代理键。最简单的做法就是在一个或多个非空的现有列上使用PK,这些列是唯一的。
Otherwise, just add an IDENTITY column and reassign the PK to it. (It will populate when you save the altered table.) Then you can delete the column with the guid.
否则,只需添加IDENTITY列并将PK重新分配给它。 (当您保存更改的表时,它将填充。)然后您可以使用guid删除列。
(I'm assuming we're having this discussion because your guid column isn't currently used for anything. Even if it's a FK in another table, if you have unique columns to join on, you don't need it there, either.)
(我假设我们正在进行此讨论,因为您的guid列目前尚未用于任何内容。即使它是另一个表中的FK,如果您有唯一的列加入,您也不需要它,或者。)
If none of that matches your situation, post again with more detail. This can be done with little fuss or bother.
如果这些都不符合您的情况,请再次发布更多细节。这可以通过简单的麻烦或麻烦来完成。
(I'm not sure why you thought you needed uniqueidentifiers for your primary keys even if the tables were to be edited, BTW.)
(我不确定为什么你认为你的主键需要uniqueidentifier,即使要编辑表格,BTW。)