Sometimes you might need to change the attributes of a table column, for instance to increase the column capacity due to changed requirements, increased data amount, or lack of data capacity planning. Here are the typical actions for altering a column in an SQL Server table:

• Change the data type: This is usually when you come close to the maximum value supported by the actual data type (typically from smallint to int, or from int to bigint).
• Change the size: This is a common case for poorly planned string columns; the current column size cannot accept all the required data.
• Change the precision: This is when you need to store more precise data, usually due to changed requirements.
• Change the collation: This is when you have to use a different (usually case- sensitive) collation for a column due to changed requirements.
• Change the null-ability: This is when the requirements are changed.

To demonstrate what happens when you perform an ALTER column action, you first need to create a sample table. Run the following code to accomplish this:

USE WideWorldImporters; 
DROP TABLE IF EXISTS dbo.Orders; 
CREATE TABLE dbo.Orders( 
id INT IDENTITY(1,1) NOT NULL, 
custid INT NOT NULL, 
orderdate DATETIME NOT NULL, 
amount MONEY NOT NULL, 
rest CHAR(100) NOT NULL DEFAULT 'test', 
CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (id ASC) 
); 
GO 

To populate the table efficiently, you can use the GetNums function created by Itzik Ben-Gan. The function is available at http://tsql.solidq.com/SourceCodes/GetNums.txt. Here is the function definition:

CREATE OR ALTER FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE 
AS 
RETURN 
  WITH 
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), 
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), 
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), 
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), 
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), 
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), 
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum 
             FROM L5) 
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n 
  FROM Nums 
  ORDER BY rownum;

Now you can run the following code to populate the table with 10 million rows:

INSERT INTO dbo.Orders(custid,orderdate,amount) 
SELECT  
  1 + ABS(CHECKSUM(NEWID())) % 1000 AS custid, 
  DATEADD(minute,    -ABS(CHECKSUM(NEWID())) % 5000000,  '20160630') AS orderdate, 
  50 + ABS(CHECKSUM(NEWID())) % 1000 AS amount 
FROM dbo.GetNums(1,10000000); 

Now, once you have created and populated the table, suppose you need to change the data type for the column amount to decimal. To see what happens during the alter-column action, you need to open two connections. In the first connection, you will have the code to change the data type of the column:

ALTER TABLE dbo.Orders ALTER COLUMN amount DECIMAL(10,2) NOT NULL; 

In the second one, you simply try to return the last two rows from the table just to check whether the table is available for querying. Use the following code for the query in the second connection:

USE WideWorldImporters; 
SELECT TOP (2) id, custid, orderdate, amount 
FROM dbo.Orders ORDER BY id DESC; 

Now, execute the code from the first connection and then from the second. You can see that both commands are running. Actually, the ALTER COLUMN command is running, while the second query is simply waiting—it is blocked by the ALTER command. During the changing of the data type, the table is not available for querying. You can see additional details. You would need to establish a third connection and put the following code there (you need to replace 66 with the session ID from the second connection) and repeat the previous two steps by changing the data type of the amount column to money:

SELECT request_mode, request_type, request_status, request_owner_type 
FROM sys.dm_tran_locks WHERE request_session_id = 66; 

The following is the result of this command:

request_mode  request_type  request_status  request_owner_type
------------  ------------- -------------- ---------------- 
S              LOCK          GRANT         SHARED_TRANSACTION_WORKSPACE  
IS             LOCK          WAIT          TRANSACTION

You can see that the query could not get IS lock because the ALTER column action is performed as a transaction. Therefore, the table is not available for querying and the query from the second connection has to wait until the ALTER command is done.

This behavior is analogous to creating nonclustered indexes offline. While creating an index online has been achievable since 2005, ALTER column was the only operation that was offline until SQL Server 2016.

When you specify the ONLINE = ON option, SQL Server 2016 creates a new shadow table with the requested change, and when it's finished, it swaps metadata with very short schema locks. This leaves the table available, even for changes, except those that could create a dependency for the altering column.

Now you will repeat the three steps from the preceding example (assuming the column amount has the money data type), but this time with the ONLINE = ON option. You need to modify the command from the first connection to the following code:

USE WideWorldImporters; 
ALTER TABLE dbo.Orders ALTER COLUMN amount DECIMAL(10,2) NOT NULL WITH (ONLINE = ON); 

The code in the second connection does not need to be changed:

SELECT TOP (2) id, custid, orderdate, amount 
FROM dbo.Orders ORDER BY id DESC; 

Now, execute the command all over again and then run the query from the second connection. You can see that the first command is running, and that the query from the second connection instantly returns results. The table is available for querying, although the data type for one column is being changed. This is a very important feature for systems that need to be continually available.

Altering online capabilities does not remove the usual limitations for changing column attributes. If a column is used in an index, or an expression in a filtered index or filtered statistics, you still cannot change its data type.

However, with the ONLINE = ON option, you can alter a column even if user-created statistics on this column exist. This was not possible prior to SQL Server 2016 (and it is still not possible in the offline mode). To demonstrate this, you will create a user statistic object on the amount column:

USE WideWorldImporters; 
CREATE STATISTICS MyStat ON dbo.Orders(amount); 

An attempt to change the data type of the column amount should fail:

ALTER TABLE dbo.Orders ALTER COLUMN amount DECIMAL(10,3) NOT NULL; 

This command immediately generates the following error message:

Msg 5074, Level 16, State 1, Line 76
The statistics 'MyStat' is dependent on column 'amount'.
Msg 4922, Level 16, State 9, Line 76
ALTER TABLE ALTER COLUMN amount failed because one or more objects access this column. 

However, when you specify the ONLINE = ON option, the same command will work:

ALTER TABLE dbo.Orders ALTER COLUMN amount DECIMAL(10,3) NOT NULL WITH (ONLINE = ON); 

The statistics are available to the Query Optimizer during the command execution; it is invalidated after the change is done and it must be updated manually.

Like the online index (re)build option, this excellent feature is available in the Enterprise Edition only.