0

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?

Improve this question
12
  • 1
    I assume that the column, therefore, is defined as int NULL at the moment? If so, changing it to NOT NULL does mean that the data engine will need to check every single value in the column to ensure that are no NULL values.
    – Thom A
    Commented Oct 10 at 15:45
  • The dim_order_item_id column in my table is currently an INT NOT NULL. I need to change it to BIGINT because we're close to the maximum limit of the INT range. The table has page compression enabled. When I change the column from INT to BIGINT, the operation is quick, but I noticed that the column is now NULL, even though it was previously NOT NULL.
    – Shoaib Maroof
    Commented Oct 10 at 15:47
  • 2
    So is the actual problem that you changed the column from int NOT NULL to bigint NULL and now want the change from bigint NULL to bigint NOT NULL to be metadata only? As I stated before, if you change a NULL column to a NOT NULL column you force the data engine to have to check every row. The data engine doesn't know that no rows have a NULL value, so it has to check.
    – Thom A
    Commented Oct 10 at 15:50
  • 4
    I'm currently using this technique for a table that didn't have compression at first. It's definitely my favorite technique when the operation can't be metadata-only (which is also true in my case).
    – Aaron Bertrand
    Commented Oct 10 at 16:01
  • 2
    Anyway, that technique is more work, but it makes "how long does the data movement take" largely irrelevant - as long as it finishes before the int actually overflows.
    – Aaron Bertrand
    Commented Oct 10 at 16:09

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.