>>>Hi,
>>>
>>>I need to change a PK field type of a table (from NUMERIC(6,0) to INT since the value is approaching 1,000,000). I have done it for other customers but here (for this customer) I have some concerns. The number of rows in the table is about 700,000 and the rows have many columns. So the change in SSMS takes some time. I connect to the customer SQL Server from my SSMS while VPN to their network.
>>>
>>>My question is, where does the amount of memory matter for this change, on my computer or on the customer server where SQL Server is installed?
>>
>>The SQL Server computer's memory will play an effect, your local computer memory doesn't matter.
>>
>>I think you probably would like to create a new table with the correct structure, move rows from the old table, rename the old, rename the new. If there are FK relations involved it may be even more problematic.
>
>Interesting that you are saying that my computer memory does not matter. Hopefully the customer server has enough memory to make this change.
>
>As far as creating new table, I will let the SSMS do that. When I changed this table on other customers' computers, it took some time but was done without issues. I understand that SSMS makes a copy of the table and this is what I was concerned about, if the SQL Server server has enough memory for a temporary copy of the table.
>
>Thank you.
At least make sure to make a script for the changes. I think SSMS will do the change using temp table.
If it's not broken, fix it until it is.
My Blog