How to make a datatype change to run as Meta Data operation

Hi Mates,

Today I am going to share an very interesting post. I consider this as very special because what makes you special from others is to keep your knowledge updated.

Okay we have a scenario where we are running out of integer max Value which is 
2147483646.

As we know Developer has come back to us asking to run the below command

alter table XYZ alter column XYZID bigInt not null;

As explained in my previous post https://www.chiranjeevivamsydba.com/2020/07/lessons-that-needs-to-be-known-when.html our systems are running in ALWAYS ON with 6 replicas (3 on Primary DC and other in remote DC)

This operation will consume a lot of log space and would cause outages if not planned properly.

If we are not taking the advantages of newer version eventually we need to break Always ON and change the database in to simple recovery model and need to follow of what has been explained in my previous post.

I always search if there are any improvements or enhancements that has been made in Latest Versions. 

We are currently running on SQL Server 2016.  To make this operation run in less than a second we can use of the below technique.

My table has 120806197  rows and its size is 9.3 GB (Doesn't matter how big it is) 

1) Enable compression on the table (In my case it's already with Page Compression)

2) My table doesn't have Clustered index so it is HEAP and it has couple of non clustered indexes so we need to enable compression on all of them. It can be either row or Page. 

ALTER INDEX ix_TEST1  ON ABC REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);  

ALTER INDEX ix_TEST2  ON ABC REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE); 

That's it we are done. The above commands has consumed 4 GB of log space.  We can check if compression is enabled or not using the below command. 

SELECT * FROM sys.partitions a
INNER Join sys.indexes b ON b.object_id = a.object_id AND b.index_id = a.index_id
WHERE a.data_compression > 0 and a.object_id=872298813

alter table ABC alter column ABCID bigInt not null; (change from int to bigint)

This is how we should make use of enhancements. After this if needed we can remove compression on Indexes using the below commands


ALTER INDEX ix_TEST1  ON ABC REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);  

ALTER INDEX ix_TEST2  ON ABC REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); 

Happy Reading........

Comments