Last post May 28, 2016 05:53 PM by skliz4rel
May 19, 2016 02:37 PM|skliz4rel|LINK
I have a database with a fast grow record base. Now I want to know how I can change the Primary key attribute to a Bigint. I created it with EF code first. I don't know how to change the primary key column to bigint of the live database. I am not getting
any work arrounds on this. The database is hosted on Azure.
I have connected to it with SQL Management studio but I can't see it. Is there any script that can allow me change the column datatype without any issue
like data loss or what so ever.
May 19, 2016 05:07 PM|PatriceSc|LINK
Do you have foreign keys using this primary key? The ALTER statement allows to change a column type and I never had any problem with that (at least when increasing the column capacity). Keep in mind the client side type needs to be changed as well.
Usually you do first in a dev and/or test environment to prepare all the steps so that you can then just run what you previously prepared
BTW if not done already make 100% sure this is needed. Do you have an estimate on the date at which you'll run out of available numbers?
May 19, 2016 05:59 PM|skliz4rel|LINK
Yea there are foriegn keys on some of the primary key.
I tried running sql script on one of the table but it did not work
ALTER TABLE dbo.Makeit
ALTER COLUMN MakeitID BIGINT
I got the error below. I tried doing this from SQL Management studio. db is hosted on azure.
Msg 5074, Level 16, State 1, Line 1
The object 'PK_dbo.Makeit' is dependent on column 'MakeitID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN MakeitID failed because one or more objects access this column.
May 20, 2016 05:32 AM|Weibo Zhang|LINK
You could first drop the constrain “PK_dbo.Makeit”,
change data type and reassign the primary key to the column “MakeitID”. For more things, please refer to below thread.
May 20, 2016 07:16 AM|PatriceSc|LINK
And similarly if referenced by a fk you'll likely have to drop it, change both the pk and fk column type before recreating it. Keep in mind you'll also have to change the type on the client side. I would suggest to test on a copy (at least with the same
structure) rather than doing that directly on your live database as it seems you are doing.
Also as hinted earlier I would make sure this is needed. As I said earlier I saw someone who wanted to do that while a computation shown he still had decades if not centuries before running out of keys.
May 28, 2016 05:53 PM|skliz4rel|LINK
I believe based on my estimate it would take 8 months to 1 year to run out of this size with a large traffic