More online operations available now – or soon

I was running some tests in SQL Database and discovered at least one new operation that supports ONLINE = ON. This is on a very recent version, by the way – SELECT @@VERSION; continues to yield an old build number, but the proof is in the build date:

This version of Azure SQL Database supports the ONLINE = ON option for ALTER TABLE ... ALTER COLUMN.

Let's say you have a table with a nullable column:

CREATE TABLE dbo.a(id INT PRIMARY KEY, x VARCHAR(255));

INSERT dbo.a(id, x) SELECT TOP (1) [object_id], name FROM sys.all_objects;

And now you decide to make that column not nullable, you can do this (assuming there are no NULLs):

ALTER TABLE dbo.a
  ALTER COLUMN x VARCHAR(255) NOT NULL
  WITH (ONLINE = ON);

You can also do things like change the collation, the data type or the size of the column:

ALTER TABLE dbo.a 
ALTER COLUMN x NVARCHAR(510)    -- changed data type and length
  COLLATE Albanian_BIN NOT NULL -- changed collation and nullability
  WITH (ONLINE = ON);

In current versions of SQL Server (and previous versions of Azure SQL Database), the ONLINE = ON hint was not supported for ALTER TABLE, and without the option, this was a blocking and size-of-data operation. To be fair, the first time I ran the code, I could only prove that the version with ONLINE = ON ran successfully, not that it worked as advertised.

I ran this code with ONLINE = ON and without:

CREATE TABLE dbo.a(id INT PRIMARY KEY, x VARCHAR(255));

INSERT dbo.a(id, x) SELECT TOP (1) [object_id], name FROM sys.all_objects;

-- placeholder;

ALTER TABLE dbo.a 
  ALTER COLUMN x NVARCHAR(510)
  COLLATE Albanian_BIN NOT NULL 
  -- WITH (ONLINE = ON);

-- placeholder;

DROP TABLE dbo.a;

In the --placeholder spot, I tried a few things to determine any difference in behavior (this was our production SQL Database, so I didn't want to use enough data or create enough activity that the difference would be obvious). I wanted to check in both scenarios whether the page had changed (indicating a true online operation) or if the values were updated in place on the existing pages (a not-so-online operation). I could have also expanded the test to see how many new pages were created if the pages were full and/or all 255 characters were used, but I thought that just seeing whether the pages changed would be enough.

I tried DBCC IND():

DBCC IND(N'dbname', N'dbo.a', 1, 1);

The results here were not surprising:

And sys.dm_db_database_page_allocations (the replacement for DBCC IND):

SELECT allocated_page_page_id
  FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID(N'dbo.a'),1,1,N'LIMITED')
  WHERE is_iam_page = 0;

This yielded an empty result set – I believe it is by design that this dynamic management function does not expose any physical information in Azure SQL Database.

Next, I tried a trick with fn_PhysLocCracker, which folks like Michelle Ufford (@sqlfool) have blogged about before:

SELECT l.page_id FROM dbo.a
  OUTER APPLY sys.fn_PhysLocCracker(%%PhysLoc%%) AS l;

Success! This returned values for the pages used in the scan against dbo.a, and it is clear that in the ONLINE = ON version, the data is moved to new pages (presumably leaving the old ones available throughout the operation), and without the hint, the data and metadata is updated in place:

Comparing pages under standard ALTER COLUMN behavior (left) with ONLINE = ON (right)

Another thing I wanted to compare was the execution plans. I might not see much in Management Studio, but in SQL Sentry Plan Explorer Pro, I can see the full call stack, including what goes on behind the scenes of some DDL commands. Our tool didn't disappoint – while it didn't present an actual plan for the in-place update variation, it too demonstrates that there is a significant behavior difference when using ONLINE = ON:

Comparing plans under standard ALTER COLUMN behavior (left) with ONLINE = ON (right)

Of course, you will only see this difference if you meet all of the other conditions required for online operations (many are similar to the requirements for online index rebuild) in the recently-updated documentation.

Now, if you're not using SQL Database, how does that help you? After all, this syntax does not parse correctly even in SQL Server 2014 Cumulative Update #6 (12.0.2480). Well, Microsoft has not exactly been guarding the fact that the development pattern has become "cloud first, then box" – as Mark Souza recently implied when he tweeted about the new row-level security feature introduced first in Azure SQL Database:

Row level security. Asked for a lot by #sqlserver community. http://t.co/pp0sNr8Nt5 Cloud first but you know what that means. It's coming

— Mark Souza (@mark_AzureCAT) February 8, 2015

This means that these online operations are likely coming to your local copy of SQL Server at some point soon, too. Like many other online operations, though, keep in mind that these things tend to be reserved for Enterprise Edition.