Freemat format int into long code#

The object ‘PK_BigTable’ is dependent on column ‘ID’.ĪLTER TABLE ALTER COLUMN ID failed because one or more objects access this column. Unfortunately, SQL Server won’t let me do that. Let’s also alter that ID column now: ALTER TABLE dbo.BigTable ALTER COLUMN ID bigint WITH(ONLINE=ON) I’ve just been messing with AnotherID so far.

The perceptive reader has probably noticed that my sample table has two int columns: ID & AnotherID. Data Compression is pretty awesome, isn’t it? Part of the data compression magic is to essentially treat fixed-length numbers as variable-length to save on storage–so the ints stay as ints, and the ALTER is just a metadata change. This time, it took only 4 milliseconds, and generated a mere 8 bytes transaction log. I’m going to use row compression, but page compression would work, too: DROP TABLE IF EXISTS dbo.BigTable ĬONSTRAINT PK_BigTable PRIMARY KEY CLUSTERED (ID) WITH (DATA_COMPRESSION = ROW) -now with compressionĪnd now, I am going to do the exact same ALTER that previously took 2 minutes and 13GB of transaction log: ALTER TABLE dbo.BigTable ALTER COLUMN AnotherID bigint WITH(ONLINE=ON) Let’s set up the same exact test case, except this time, we’ll specify the clustered index to use index compression. Is there a “Go Fast” button?ĭata compression can help us tons here. That’s a lot of extra disk space for the convenience. Except…let’s do the math: 13GB (transaction log usage) ÷ 4,851,495 rows (in my sample table) × 2,147,483,647 rows (the int limit) = 5.6 TB of transaction log growth. The alter is pretty easy, and it’s online, so the transaction log bloat is a pretty decent cost to pay. If other transactions were running during the ALTER, the transaction log would have grown to the cumulative total of both my alter, and everything else that ran during the alter. It also caused my transaction log to grow to nearly 13GB. I should be able to change the AnotherID column to be a bigint without any fuss: ALTER TABLE dbo.BigTable ALTER COLUMN AnotherID bigint WITH(ONLINE=ON) Thanks to the ALTER TABLE.ALTER COLUMN.WITH (ONLINE=ON) syntax. I did this in a brand-new database on SQL Server 2019, and it generates a table with 4,851,495 rows, and takes up 9.29GB. INSERT INTO dbo.BigTable (AnotherID, Filler)įROM sys.objects o, sys.objects o1, sys.objects o2

Freemat format int into long code#

(If you want to exhaust the int limit for your testing, just change the last line in this code from GO 5 to GO 2000.) On my laptop, this takes 90 seconds to run: DROP TABLE IF EXISTS dbo.BigTable ĬONSTRAINT PK_BigTable PRIMARY KEY CLUSTERED (ID) On my laptop, that’s “big enough” to make the various scripts slow enough to measure, but fast enough to be reasonable for a demo. I’m going to insert about 5 million rows in my table. Let’s change some data types We’ll create a “big” table to test with

The DBA is left with the task of implementing that data type change, and now that it has almost 2 billion rows, it’s a well-established table and uptime during the change is a major consideration.

The int to bigint conversion is one of the most popular data type changes I see–A developer inevitably creates the table thinking they will never have more than 2 billion rows… then some years or months later 2 billion becomes a reality. This wonderful syntax now allows you to alter a column from int to bigint without causing major blocking. SQL Server 2016 introduced the ability to do ALTER TABLE.ALTER COLUMN as an online operation using the WITH (ONLINE=ON) syntax in Enterprise Edition.

Changing a column from int to bigint has gotten a lot easier since I started working on SQL Server back at the turn of the century.