0

I have a big table (500 000 000 rows) like

create table x
(
    id int not null identity(1,1) primary key,
    name nvarchar(MAX),
    type int
)

The value id is already near MAXINT, that is why I need to change the type from INT to BIGINT.

This query works but takes very long

alter table [dbo].[x]  
    alter column id bigint;

What I can do to change the type faster?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ZedZip
  • 5,794
  • 15
  • 66
  • 119
  • Yes, may be, but that answer is not suitable. I have tried to add a new column and update it, it works 15 hours. – ZedZip Aug 30 '17 at 08:03
  • 1
    How about creating another table y, with `id` column in `bigint`, remain the rest of the columns, then run `Insert Select` to new table? – Jacky Aug 30 '17 at 08:19
  • @Oleg The answers suggest to use a while loop or to copy to a new table. Are you sure none of the answers work for you? – MatSnow Aug 30 '17 at 08:21
  • 1
    `int` and `bigint` are fixed size types. This means that the update changes the row layout which in turn means every single row has to be changed. Also, the PK is going to double in size meaning lots of page splits/data movement. I don't think there's going to be a "quick" way. – Damien_The_Unbeliever Aug 30 '17 at 08:21
  • Set up a new table with the right layout (preferrably in a new database, or even another server), then parallel bulk copy your data, then recreate indexes, then make everything use the new table. That's about as fast as it gets, because one way or another, all data will need to be written again. All 500M rows of it. – Jeroen Mostert Aug 30 '17 at 08:34
  • @Damien_The_Unbeliever yes, you are right, I have tried 3 different methods, all of them take too long – ZedZip Aug 30 '17 at 08:37

0 Answers0