l*****b
发帖数: 82 | 1 Hi, friends,
I have existed DB with Identity (1,1) primary key columns and it has huge
data. Now I have to insert non-auto-increment value in this primary key. Do
you have any idea on this? |
a9
发帖数: 21638 | 2 set xxx off?
2005
Do
【在 l*****b 的大作中提到】
: Hi, friends,
: I have existed DB with Identity (1,1) primary key columns and it has huge
: data. Now I have to insert non-auto-increment value in this primary key. Do
: you have any idea on this?
|
l*****b
发帖数: 82 | 3 Do you mean SET IDENTITY_INSERT XXX ON?
Does it have any issue if I wrap it in my high volume transactions?
【在 a9 的大作中提到】
: set xxx off?
:
: 2005
: Do
|
a9
发帖数: 21638 | 4 对，就是那个。我没在生产上用过，不知道。我就是在dts里用。
我也不知道手工insert以后，再自动的时候identity是几，呵呵。
【在 l*****b 的大作中提到】
: Do you mean SET IDENTITY_INSERT XXX ON?
: Does it have any issue if I wrap it in my high volume transactions?
|
i****a
发帖数: 36252 | 5 Should be fine
【在 l*****b 的大作中提到】
: Do you mean SET IDENTITY_INSERT XXX ON?
: Does it have any issue if I wrap it in my high volume transactions?
|
l*****b
发帖数: 82 | 6 I have another approach to change the PK column Identity (1,1) to non-auto-
increment. It looks like I have to create some temp columns or tables to
copy/remove/rebuilt the PK column. Do you think I could have other way to go
? Thank you. |
i****a
发帖数: 36252 | 7 are you trying to do this while the DB is live and busy?
auto-
to go
【在 l*****b 的大作中提到】
: I have another approach to change the PK column Identity (1,1) to non-auto-
: increment. It looks like I have to create some temp columns or tables to
: copy/remove/rebuilt the PK column. Do you think I could have other way to go
: ? Thank you.
|
l*****b
发帖数: 82 | 8 Hi, iMaJia, yes, I want to test if it is feasible. But I am afraid of the
performance and the role issue (need DDLadmin role as least). Do you have
other concern?
Otherwise, I have to change the PK column totally with clone/drop/rebuild.
【在 i****a 的大作中提到】
: are you trying to do this while the DB is live and busy?
:
: auto-
: to go
|
i****a
发帖数: 36252 | 9 that maybe a problem if you have concurrent inserts into the database
from different sources.
the auto ID will insert as the next biggest number in the column. so if
you manually set indentity_insert xxx off and adds a row of ID 1000,
next auto insert will be 1001. and when you process your manual row of
1001, you'll get the error.
one way to avoid this, that I can think of now is, reserver a gap for
yourself.
say your current ID is 1 - 1000, and you need to manually insert 500
records. reseed t
【在 l*****b 的大作中提到】
: Hi, iMaJia, yes, I want to test if it is feasible. But I am afraid of the
: performance and the role issue (need DDLadmin role as least). Do you have
: other concern?
: Otherwise, I have to change the PK column totally with clone/drop/rebuild.
|
i****a
发帖数: 36252 | 10 as for performance, do it in small batches to avoid big impact on the live
activity
the
have
clone/drop/rebuild.
【在 l*****b 的大作中提到】
: Hi, iMaJia, yes, I want to test if it is feasible. But I am afraid of the
: performance and the role issue (need DDLadmin role as least). Do you have
: other concern?
: Otherwise, I have to change the PK column totally with clone/drop/rebuild.
|
