Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Online ALTER COLUMN Operation


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem

When a schema modification is performed on a database table, a special schema lock type SCH_M will be issued and will not be released until this schema modification is completed. If you try to perform a SELECT query on that table during that process, the query will be blocked by the schema change waiting for the change completion. Is there any way to perform the schema changes during working hours without blocking the SELECT queries running on the table?

Solution

SQL server 2016 brought us many new features and enhancements to existing features. One of these T-SQL enhancements is the ability to perform the table schema changes online, reducing downtime required to apply the change. The WITH (ONLINE = ON | OFF) is a new option in the ALTER TABLE…ALTER COLUMN T-SQL statement that allows you to perform changes on the columns such as changing the column data type, length, collation , sparseness and nullability while the table is online and available for users. This new feature is disabled by default and can be enabled within the ALTER TABLE...ALTER COLUMN WITH (ONLINE = ON) T-SQL statement.

Let us go through a practical demo to understand how this new feature works. We will create a new table Production.TransactionHistory_New on the MSSQLTipsDemo test database:

USE [MSSQLTipsDemo]
GO
CREATE TABLE [Production].[TransactionHistory_New](
 [TransactionID] [int] NOT NULL,
 [ProductID] [int] NOT NULL,
 [ReferenceOrderID] [int] NOT NULL,
 [ReferenceOrderLineID] [int] NOT NULL,
 [TransactionDate] [datetime] NOT NULL,
 [TransactionType] [nchar](1) NOT NULL,
 [Quantity] [int] NOT NULL,
 [ActualCost] [money] NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [Address] nvarchar(500) NOT NULL)
  ON [PRIMARY]
  GO

Then add records to the new table from the Production.TransactionHistory table:

INSERT INTO [Production].[TransactionHistory_New] SELECT * FROM [Production].[TransactionHistory]
GO

If we try to run a normal ALTER TABLE...ALTER COLUMN T-SQL statement to change the data type of the Address column from nvarchar(500) to varchar(600), which will run with session ID 53 in my example:

ALTER TABLE [Production].[TransactionHistory_New] ALTER COLUMN [Address] varchar(600) NOT NULL

At the same time, try to run the below SELECT statement from the same table, which will run with session ID 56 in my example:

SELECT TOP 10000 * FROM  Production.TransactionHistory_New ORDER BY [Address]

If we query the sys.sysprocesses system object to check the status of these two sessions:

Select spid, blocked,dbid,status,cmd from sys.sysprocesses Where spid=53 OR spid=56

The result will show us the SELECT statement is blocked by the ALTER COLUMN statement, and will not retrieve the data until the schema change is complete:

SELECT statement is blocked by the ALTER COLUMN statement, and will not retrieve the data until the schema change is complete

In this case, the ALTER COLUMN statement will be completed in 4ms without interruption as we can see from the time statistics result below:

the ALTER COLUMN statement will be completed in 4ms

If we try now to run the same ALTER COLUMN statement with the online execution enabled for that process as shown below:

ALTER TABLE [Production].[TransactionHistory_New] ALTER COLUMN [Address] varchar(600) NOT NULL
WITH (ONLINE=ON)

Then run the previous SELECT statement from the same table:

SELECT TOP 10000 * FROM  Production.TransactionHistory_New ORDER BY [Address]

And try again to query the sys.sysprocesses system object to check the status of these two sessions:

Select spid, blocked,dbid,status,cmd from sys.sysprocesses Where spid=53 OR spid=56

The result will show us that the ALTER COLUMN statement will not block the SELECT statement, and the SELECT statement will run concurrently, but this time using a parallel plan retrieving the data without waiting for the ALTER COLUMN statement to complete:

the ALTER COLUMN statement will not block the SELECT statement

The cost you will pay is the time required for this ALTER COLUMN statement to execute.  This second online execution will take about 30 seconds to complete as compared with the offline process that took only 4 ms:

ALTER COLUMN statement took 30 seconds to execute

So, you need to compromise between running the ALTER COLUMN statement offline taking less time, but blocking all other processes on that table, or running it online, taking longer time without blocking other processes on the table.

There are many limitations for the online ALTER COLUMN operation such as altering the column from NOT NULL to NULL, which is not supported if the column is involved in a non-clustered index. If we create a non-clustered index on the Address column in the previous table:

USE [MSSQLTipsDemo]
CREATE NONCLUSTERED INDEX [IX_TransactionHistory_New_Address] ON [Production].[TransactionHistory_New]
( [Address] ASC  )
 GO

Then try to ALTER that column using the online option:

ALTER TABLE [Production].[TransactionHistory_New] ALTER COLUMN [Address] varchar(600)  NULL
WITH (ONLINE=ON)

The ALTER operation will fail because the non-clustered index is dependent on that column as in the below error message:

ALTER operation will fail because the non-clustered index is dependent on that column

Other limitations for the online ALTER COLUMN operation are that it does not support ADD/DROP PERSISTED and ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION T-SQL statements. Altering from/to CLR data types, altering to XML datatype with a different schema collection and altering a table with change tracking enabled on that table are not allowed too for the online ALTER COLUMN statement.

Take into consideration that you can only use the online ALTER COLUMN to alter one column at a time and that this process requires twice the storage, as the original column in order to accommodate users connectivity during the new column creation.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools