I have a dimension table bi_tb_sales.dim_order_item that is approximately 22 GB in size, with an index size of around 0.14 GB. This table is partitioned on the dim_order_item_id column, also serving as its primary key. The column has page compression enabled, and the current maximum ID value is 2,068,937,613, close to the INT range's maximum limit. The dim_order_item_id is an internal surrogate key generated by BI using ROW_NUMBER() and needs to be NOT NULL.
When I execute the following command to change the data type of the dim_order_item_id column from INT to BIGINT, it appears to be a metadata-only operation:
ALTER TABLE bi_tb_sales.dim_order_item
ALTER COLUMN dim_order_item_id BIGINT;
However, when I modify the command to include the NOT NULL constraint, the operation requires significantly more processing:
ALTER TABLE bi_tb_sales.dim_order_item
ALTER COLUMN dim_order_item_id BIGINT NOT NULL;
My Questions:
Why does the first operation (changing to BIGINT without the NOT NULL constraint) qualify as a metadata-only operation, while the second operation (changing to BIGINT with NOT NULL) does not?
Is there a workaround allowing me to change the column type to BIGINT and enforce the NOT NULL constraint while keeping it as a metadata operation?