Recently, I was working on a task to encrypt a column in a table on SQL Server. The table was pretty large and contained about 300 million rows of data. Running the CMK column encryption wizard on the concerned column did not help as it ran for too long and failed every time due to some network or unknown reason. For this reason, I decided to partition the table into 3 smaller ones of 100 million rows each and attempt the encryption on this smaller data set.

Initially I tried going the NTILE route. My query was:

WITH Partitioned AS (
    SELECT *, NTILE(3) OVER (ORDER BY 
(SELECT NULL)) AS PartitionGroup 
    FROM MyDatabase.MyTable
)
SELECT * FROM Partitioned;

NTILE(3) is expected to divide the table into 3 roughly equal parts without overlapping and ensures non-repetition. ORDER BY (SELECT NULL) is a trick used to avoid applying any specific order when using window functions like NTILE, ROW_NUMBER, etc. This is helpful when the table has no natural ordering column (like an auto-incrementing ID) and you want an even-ish split across rows, regardless of content.

If you want a consistent and repeatable split, it’s better to use:

NTILE(3) OVER (ORDER BY [some stable column])

However, this method failed with the error – The database ‘tempdb’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

This is because for windowing functions, SQL Server is trying to compute the entire window at once in memory, which can overwhelm tempdb. The same issue occurs when using ROW_NUMBER as shown in the query below. I picked a high cardinality column txnID to act as my primary key. txnID’s are large numbers but stored as nvarchar. For example: “2024122210064506101371148“

SELECT *, ROW_NUMBER() OVER (ORDER BY txnID) as RowNum
INTO MyDatabase.MyTable_WithRowNum
FROM MyDatabase.MyTable

Safer Alternative

Instead of using NTILE, using a lightweight, hash-based approach that doesn’t require window functions can avoid heavy memory pressure. I tried executing the below query:

SELECT *,
    CAST(ABS(CHECKSUM(txnID)) AS BIGINT) % 3
AS PartitionGroup
INTO MyDatabase.MyTable_WithPartition
FROM MyDatabase.MyTable

CHECKSUM + ABS + % 3 gives 3 consistent buckets. It requires no memory-intensive windowing, runs in a single pass writing directly to disk, and is much lighter on tempdb.

However, I got the error: Arithmetic Overflow error converting expression to data type int.

This is because CHECKSUM() on an NVARCHAR can return an INT value that, when you apply ABS(), can exceed the range of INT due to how ABS(INT_MIN) behaves (i.e., ABS(-2,147,483,648) is still -2,147,483,648).

This is one of those weird edge cases in computer science. ABS(INT_MIN) returns a negative number (still INT_MIN) because the positive equivalent doesn’t exist in the signed 32-bit integer range (2,147,483,647).

Safe and Clean Fix

Instead of using ABS, which can overflow, use bitwise & to force positive values. I modified the query as follows:

SELECT *, (CHECKSUM(txnID) & 0x7FFFFFFF) % 3
AS PartitionGroup
INTO MyDatabase.MyTable_WithPartition
FROM MyDatabase.MyTable

This works because:

• 0x7FFFFFFF is the largest positive 32-bit signed int (2,147,483,647).
• & masks out the sign bit, avoiding overflow without needing ABS().
• Much faster and safer for large nvarchar columns.

Breaking this down further:

1. CHECKSUM(txnID)
   • This generates a 32-bit integer hash for the value of txnID.
   • Output is of type INT -> range: -2,147,483,648 to 2,147,483,647
   • It can be negative, depending on the string.
   • Depending on your choice of column and its datatype, you may or may not need this step.
2. 0x7FFFFFFF
   • This is the hexadecimal representation of the largest 32-bit signed integer:
     • 0x7FFFFFFF = 2,147,483,647 = INT_MAX
   • In binary, it’s all 1s except for the sign bit:
     • 0111 1111 1111 1111 1111 1111 1111 1111
3. & – Bitwise AND operator
   • This does a bit-by-bit comparison between the two numbers.
   • It clears the sign bit of the CHECKSUM() output, forcing it to be non-negative.

Example:

• Let’s say:
  • CHECKSUM('abc123') = -100
• Binary of -100:
  • 11111111 11111111 11111111 10011100 (2's complement)
• 0x7FFFFFFF:
  • 01111111 11111111 11111111 11111111
• Applying bitwise AND:
  • 01111111 11111111 11111111 10011100
• => result is 2147483548, a positive number.

After this, create the smaller tables:

SELECT * INTO MyDatabase.MyTable_Partition0 FROM
MyDatabase.MyTable_WithPartition WHERE
PartitionGroup = 0;

SELECT * INTO MyDatabase.MyTable_Partition1 FROM
MyDatabase.MyTable_WithPartition WHERE
PartitionGroup = 1;

SELECT * INTO MyDatabase.MyTable_Partition2 FROM
MyDatabase.MyTable_WithPartition WHERE
PartitionGroup = 2;

I now have 3 smaller tables to run my encryption on and they can be joined to retrieve the entire dataset.

Conclusion

SQL Server does not scale well on operations on large tables. For this reason, partitioning is an important paradigm. Sometimes, you might need to manually partition a table that was created without a pre defined partition logic or one without a set primary key. If the table is small (<1 million rows), windowing functions like NTILE() and ROW_NUMBER() that work in memory might be sufficient to do the job. However, if the table is very large (300 million rows in my example), you might want to use a hash function like CHECKSUM() along with a mod (%) operator to partition the rows and write them into equal-ish smaller tables. If the high cardinality key you select is of type nvarchar, you might additionally want to use the bitwise AND (&) operator in order to avoid overflows without needing ABS() which can break due to how ABS(INT_MIN) behaves