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:
Feb 12 2015 00:53:13
Copyright (c) Microsoft Corporation
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
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;
--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.
DBCC IND(N'dbname', N'dbo.a', 1, 1);
The results here were not surprising:
DBCC command 'IND' is not supported in this version of SQL Server.
sys.dm_db_database_page_allocations (the replacement for
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.
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:
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:
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:
— 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.