The Great Integer To Bigint Compression Swindle
Thanks for watching!
Video Summary
In this video, I dive into the often-overlooked complexities of converting an integer column to a big int in SQL Server without downtime, debunking some common misconceptions. I explain why many blog posts and articles might lead you to believe that simply adding compression is enough for such a change, but reality paints a much more intricate picture. I share real-world scenarios where this conversion has caused significant headaches due to dependencies and other constraints that aren’t always immediately apparent. To help viewers navigate these challenges, I also highlight Andy Malin’s detailed blog post on the subject, offering a practical approach using shadow tables and triggers for minimal disruption.
Full Transcript
Alright, I lied a little bit. I had one video left to record that I forgot about. And I’m going to do that real quick because I just want it off the board. This video is about the great integer to big int compression lie. There’s a lot of blog posts out there that talk about this neat trick if you want to convert a column from an integer to a big integer with no downtime and all that. You know, oh, it just, it happens instantly, blah, blah, blah. There’s a lot of caveats to that that don’t get talked about in these blog posts. A lot of people have fooled you into thinking that it’s as simple as just, oh, just, you know, make an index with compression and all of a sudden it’s done. It’s not, not quite true. There are a lot of things that could be, a lot of other things that can get in the way of what, what seems like a very simple thing. And all these other blog posts. But before, before we do that, I need to tell you the truth about a few things. Like, like the fact that you can become a member of this channel for four bucks a month and support the fantastic unparalleled work that I put forward on YouTube to help educate you about SQL Server stuff.
There’s a link right down there. If you, if you don’t have the four bucks or more, depending on your, your, your feelings of generosity, you can like, you can comment, you can subscribe and the channel will grow in other important ways. You know, there’s, it’s not just money. What do you call it? Influence. My sphere of influence will grow. If you need help with SQL Server stuff, including making columns go from integer to big integer, you can hire me to do all of these things at a very reasonable rate. Uh, and I’ll be very happy to help you. So we’ll, we’ll both have these things to look forward to. Uh, if you would like my training, you can get all of it for about 150 us dollars at that site up there using that discount code.
And again, there is a link for that right in this area somewhere, uh, where, where you can just click on a fully assembled thing and just show up and start using it and watching it and getting smarter and being a better human being and all that other good stuff. SQL Saturday, New York city, uh, coming up in May 10th, uh, at the Microsoft offices in times square. Uh, if you would like to come see me and a whole bunch of other smart folks talk about SQL Server data stuff in general, uh, then you can buy a ticket. It’s wonderful. All you have to do is a little search for a SQL Saturday NYC 2025. Guarantee you’ll find it. But with that out of the way, let’s talk a little bit about how these people hornswoggle you into thinking that compression, uh, makes this change easy. So I’m going to create a table here.
And the, the column that we’re going to care about is this integer column, right? ID. And we’re going to have a clustered primary key on that column. And then we’re going to have some other indexes that reference that column along with one index that does not reference that column at all. All right. So let’s create this table and we’re not even going to put any data in this table because I just want to sort of show you the complexity of what we’re dealing with.
Now, uh, all of the indexes on this table, the clustered primary key and all of the nonclustered indexes have compression on them. So this should be immediate and instant. But if we run this, run this author statement and try to have, um, try to make that change. Oops. Whoopsie daisy. Uh, you’re going to see this error here. Uh, this object, uh, this index and this index and this index and this index are all dependent on this column.
So we can’t just change this column. This is just indexes on the table that we care about. So imagine if we have other stuff that references this column, say foreign keys, uh, indexed views, uh, any, anything like that. If you have, um, if you have objects created with schema binding, like functions or views or something like that, um, you, you could, the, the, the, the, the, the making this, making this change to this column is far more than a case of just saying, oh, I added compression and now I can do it.
No, it’s not. It’s a lot more involved. If like in, you know, uh, if, if you’ve got a big table and you need to get rid of indexes or you need to remove a clustered primary key, uh, from that, table, whoo, boy, you’re not going to have fun. So we would need to drop the constraint, right? The primary, the primary key constraint, which on a big table, not a good time, right? Converting that table to a heap, uh, and then drop the one, two, three nonclustered indexes that we would care about here, right? That, that, that, that, that we got the error for. And then now finally we could make this change. But again, this doesn’t take into account any other relational dependencies on this column that might exist in your database. If we had more stuff going on, this just wasn’t just like a single table that I’m creating for this demo in my useless crap database.
We would have to do a lot more work to make, to make this happen instantly. Then after, you know, at the end of that, we would have to go and add everything back. This whole thing right here, like this whole thing would be fully logged because you would have to get rid of the clustered primary key. And then adding it back, adding these three indexes back, those would also all be fully logged. So is it really worth going through all the trouble with compression to change a column instantly?
It doesn’t feel very instant to me. And this is something that I’ve had to actually help people with, not just write a blog post about that was like, oh yeah, look, it can do it. No. Doing this in real life is a lot harder and a lot more complicated. If you want to see a good blog post about how to do this with very little downtime, don’t judge me. I don’t use Bing. I don’t use Edge very much on this thing. But if you go to Andy Malin Alter Online, Andy has, and I’m going to put this link in the video description.
Andy Malin has a blog post that I think, I think, how long ago was this? 2017. And he talks about, wait, is this the right one? No, this is rebuild a very large primary key index. Man, Bing sucks. Jesus. All right, there it is. Changing a column from int to big int without downtime from 2019. Thanks, Andy. So I’m going to put the link to this correct blog post that for some reason Bing thought should be second in the results.
Oh, man. Microsoft keeps thinking it can compete with these things. Like, it thinks Bing can compete with Google. It thinks Fabric can compete with Databricks. It thinks Azure can compete with AWS.
And, oh, boy, you just waste everyone’s time and money with all this nonsense. So Andy has a great post where he walks through creating a shadow table, creating a trigger to move stuff over, and then how to do the swap and migrate data and everything. So I’m going to put this in the blog post because this is a far more reasonable approach to making the change.
I’m actually going to copy it right now in front of you because I don’t want to forget. So this is a much more reasonable change that you could make. Rather, a much more reasonable set of steps to take to make the change than just saying, oh, I added the compression.
No, it’s done. Because, man, I’ll tell you. The number of people who have needed to fix that and tried to follow the compression thing and just ended up with a sea of red text errors is huge.
I’ve had to get involved with these situations a bunch of times. No one’s very happy about it because they’re like, I read these blog posts. All these smart people told me all I needed was compression.
But no, no, no, no, no. You need a lot more than that. You need to ditch all those dependencies before compression kicks in to the point where it can help. Now, to be, I mean, not even to be fair to the other blog posts, but to be fair to people who want to make this change, Microsoft, this should just be a metadata-only change anyway.
Like, all you have to do is start accounting for new stuff in the metadata as a big int and, like, and say, like, allocate bytes based on that. And then just say the old stuff is now a big int. But even though you know it’s an int that only takes up four bytes, it doesn’t matter until you, like, rebuild or, like, even maybe even reorg the index or something like that.
Like, there’s just no reason for this to be as painful as it is. It’s pretty stupid, actually, that we have to go through this much trouble and the compression would even, like, come into the picture with all the dependencies and stuff that you still need to get rid of. So, I don’t know.
Maybe eventually someday we’ll get some pain relief in this area because, you know, integers aren’t getting any smaller. People, data isn’t getting any smaller. People keep adding new stuff in.
And, boy, this is just going to get worse and worse. So, hopefully, Microsoft addresses this major scalability flaw, stops wasting money on things like video games, search engines, crappy cloud services, and fabric, and lousy AI gamble. I don’t know.
You know, maybe put some money into your core products and stop asking and not stop treating people who have already bought it as, like, screw you. You’re already stuck with us. You know, like, help people who have had your product and been paying for it for a while out a little bit. It’s a nice thing to do.
At some point, you’ve got to dance with who brung you. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. Now I’m really taking a break and shutting everything down because I have completed my ring cycle. So, goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.