ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016

Introduction

SQL Server 2016 introduces an interesting T-SQL enhancement to improve performance and reduce downtime ALTER TABLE WITH (ONLINE = ON | OFF). This statement will help you alter data types, change column/table collation values, change nullability settings, change column length, and many other options while the table remains online for user connectivity.

However, this feature is turned off by default in SQL Server 2016. In case, if you wish to utilise this feature then you would need to specify ALTER TABLE WITH (ONLINE = ON) option.

Comparison: ALTER TABLE Online vs. Offline

Highlights “Online vs. Offline” Comparison

Feature: Locking Mechanism

• Offline (DEFAULT): Takes an Sch-M (Schema Modification) lock, blocking all reads and writes.
• Online (ON): Uses low-priority locks, allowing concurrent user access for most of the duration.

Feature: User Connectivity

• Offline (DEFAULT): Connections are queued; “Table not found” or “Timeout” errors may occur.
• Online (ON): Continuous access; users can query the table while the column is being resized.

Feature: Use Case

• Offline (DEFAULT): Maintenance windows or small tables.
• Online (ON): Large-scale Production tables & Enterprise Edition environments.

Example ALTER TABLE WITH (ONLINE = ON | OFF)

DROP TABLE IF EXISTS ProductPrice
GO
CREATE TABLE ProductPrice (Price INT)
GO

INSERT INTO ProductPrice (Price) VALUES (1250);
INSERT INTO ProductPrice (Price) VALUES (1252);
INSERT INTO ProductPrice (Price) VALUES (1254);
INSERT INTO ProductPrice (Price) VALUES (1256);
INSERT INTO ProductPrice (Price) VALUES (1258);

ALTER TABLE ProductPrice
ALTER COLUMN Price DECIMAL (5, 2) WITH (ONLINE = ON)
GO

SELECT Price FROM ProductPrice
GO	

⚠️ Important: Enterprise Edition Requirement

The ALTER TABLE ... WITH (ONLINE = ON) syntax is an Enterprise Edition feature in SQL Server 2016. If you attempt to execute this command on Standard, Web, or Express editions, the operation will fail with the following error:

Error Message: > Msg 1712, Level 16, State 1, Line 10

Online index operations can only be performed in Enterprise edition of SQL Server.

The Fix: If you are not on Enterprise Edition, you must remove the WITH (ONLINE = ON) clause. However, be aware that the table will be locked (Sch-M lock) for the duration of the change, preventing user access.

Advanced: Handling Lock Contention with WAIT_AT_LOW_PRIORITY

A common challenge with ALTER TABLE ONLINE on busy production databases is the “final lock.” Even though the process is online, SQL Server needs a brief Sch-M (Schema Modification) lock at the very end to finalize metadata changes. If a long-running report is currently reading the table, your ALTER command will wait, potentially causing a “queue” of blocked queries behind it.

SQL Server 2016 introduced WAIT_AT_LOW_PRIORITY to solve this. This allows the ALTER command to wait behind other queries without blocking new incoming requests.

Key Parameters:

• MAX_DURATION: How many minutes the command will wait for the lock.
• ABORT_AFTER_WAIT: What to do when time runs out. You can choose SELF (cancel the Alter command) or BLOCKERS (kill the user queries holding the lock).

Syntax Example: ALTER TABLE ProductPrice ALTER COLUMN Price DECIMAL(5,2) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)));

This ensures that your schema change never accidentally “brings down” the site by creating a massive blocking chain.

Trending SQL Server Articles and Tips

Frequently Asked Questions (FAQ) on ALTER TABLE WITH (ONLINE=ON | OFF) T-SQL Enhancement in SQL Server 2016

1. What is the syntax for ALTER TABLE ALTER COLUMN ONLINE in SQL Server 2016? To perform an online column modification, use the WITH (ONLINE = ON) syntax. For example: ALTER TABLE Employees ALTER COLUMN LastName VARCHAR(100) WITH (ONLINE = ON);. This tells the SQL Engine to process the change while allowing concurrent SELECT, INSERT, and UPDATE operations on the table.

2. Can I use ALTER TABLE ONLINE in SQL Server Standard Edition? In SQL Server 2016, the ONLINE = ON option for ALTER TABLE is an Enterprise Edition exclusive feature. If you attempt to execute this on Standard Edition, you will encounter Error 1712: “Online index operations can only be performed in Enterprise edition of SQL Server.” Always verify your edition using SELECT @@VERSION before planning production schema changes.

3. Does SQL Server 2016 support ONLINE column changes for VARCHAR(MAX)? There are specific limitations regarding Large Object (LOB) data types. While SQL Server 2016 improved online operations, altering a column to or from VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX) generally requires an offline operation. Attempting an online change on these types may trigger an error stating the data type is not supported for online operations.

4. How does ALTER TABLE WITH (ONLINE = ON) reduce database downtime? The “Online” option reduces downtime by avoiding an Sch-M (Schema Modification) lock for the duration of the entire operation. Instead, it uses a row-versioning approach or low-priority locks, ensuring that users can continue to query the data. The only exclusive lock required is held for a very short duration at the very end of the process.

5. Is TempDB affected when running ALTER TABLE ONLINE operations? Yes, performing an online schema change increases TempDB usage. SQL Server creates a temporary version of the table to manage concurrent transactions while the data type or length is being modified. DBAs should monitor TempDB space and Transaction Log growth during large-scale online alterations to avoid “Log Full” errors.

6. Can I change a column from NULL to NOT NULL using the ONLINE option? Yes, SQL Server 2016 supports changing a column’s nullability online, provided the existing data does not contain NULL values. This is a significant T-SQL enhancement, as it allows for data integrity improvements without interrupting application availability.

7. Why is my ALTER TABLE ONLINE query running slower than an offline query? Online operations typically take longer than offline operations because the system must manage concurrent user activity and maintain row versions. While the Total Duration might be longer, the Service Availability is 100%, which is usually the priority for high-traffic, mission-critical production databases.

SQL Server 2016 T-SQL Enhancement Series

• MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE
• ALTER DATABASE SET AUTOGROW_ALL_FILES
• COMPRESS and DECOMPRESS Functions
• STRING_SPLIT and STRING_ESCAPE Functions
• FORMATMESSAGE Statement
• SERVERPROPERTY Function
• TRUNCATE TABLE WITH PARTITIONS
• DROP IF EXISTS
• ALTER TABLE WITH (ONLINE = ON | OFF)