How to alter a large Azure table without timeout

Question

I am trying to alter a field definition in a large Azure table (20 Gb, 500M+ records)

from nvarchar(max) to nvarchar(16) using

ALTER TABLE [dbo].[MyTable] ALTER COLUMN [MyField] [nvarchar](16)

but I cannot find a way to execute this command without running into a timeout. From Visual Studio and SQL Server Management Studio I get a

Msg 10054, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

after about 30 min. When I run the command from the Azure Management Portal after a few minutes I get :

Msg -2, Level 11, State 0, Line 0 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How can such a long running query be executed on SQL Azure?

Answer 1

Try the v12 "WITH (ONLINE = ON)" option for your alter statement. See here for examples:

http://sqlperformance.com/2015/02/sql-performance/more-online-operations

Answer 2

you should try

ALTER TABLE [dbo].[MyTable] ALTER COLUMN [MyField] nvarchar NULL

Notice the NULL parameter...

Answer 3

One does not simply alter a column on a large table in SQL Azure

Instead do the following:

1. Create a new table with the desired schema.
2. Copy data from the original table to the new table in small batches that do not time out.
3. Drop original table.
4. Rename new table to the original table.

Answer 4

Well actually there is one option - scale your DB to higher performance tier, do the heavy lifting, and scale it back.

I have S2 database, tried to alter massive table, got timeout after 30 minutes. So I scaled it up to P2, alter done in 6 minutes. Scale back down - you will be charged for 1 hour of P2, which is currently 1 EUR. Peronally, I would give 1 EURo anytime to avoid dirty stuff like creating new tables, moving data maintainging consistency on live system, renaming tables etc...

Answer 5

Usually you see this in V11 and upgrading to V12 shouldn't have the problem of session throttling limits. The other idea is to add a null column / default value and incrementally fill the value and flip the bit when needed, until then your application should ignore the column.

Answer 6

Could you not add a new column that allowed null, copy the old column data into it, change the nullability to not allow null, drop the original column and then rename the new one to the old one?

The copy to new column may take time but it shouldn't take the table offline and I don't think the column drop or rename would take much time. Something like this:

ALTER TABLE dbo.Table_1 ADD col2 nvarchar(15) NULL
go
UPDATE dbo.Table_1 SET col2 = col1
ALTER TABLE dbo.Table_1 ALTER COLUMN col2 NVARCHAR(15) NOT NULL
ALTER TABLE dbo.Table_1 DROP COLUMN col1
EXEC sp_rename 'dbo.Table_1.col2', 'col1', 'COLUMN'