How to fix Error 2728 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 2728 — “Cannot partition on more than %d columns”

Why This Error Happens

This error occurs because SQL Server 2025 enforces a hard architectural limit on the number of columns (typically 16) that can be used in a single partition function’s column list. The root cause is almost always a schema design issue where a table or index definition attempts to reference a partition scheme with a composite key exceeding this threshold. This is not a bug but a built-in constraint to protect query optimizer performance and metadata integrity.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Partition Function

  • Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
  • Run the following query to locate functions nearing or exceeding the limit:
SELECT name, fanout, boundary_value_on_right 
FROM sys.partition_functions;
  • Cross-reference the function name with the CREATE TABLE or CREATE INDEX script that triggered the error.

Step 2: Refactor the Partitioning Key

  • Reduce the number of columns used in the PARTITION FUNCTION definition to stay within the 16-column limit.
  • Prioritize retaining only the columns essential for partition elimination (usually date or region-based keys) in the function syntax: CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000);
  • Move non-essential columns into the table schema as standard indexed columns rather than partitioning keys.

Step 3: Utilize Computed Columns for Complex Logic

  • If multiple columns are required for logical partitioning, create a single persisted computed column that concatenates or hashes the values.
  • Use this single computed column as the input for the partition function instead of multiple individual columns.
  • Ensure the computed column is marked as PERSISTED to allow it to be used in the partition scheme.

Step 4: Validate Compatibility Level

  • Check the database compatibility level to ensure it aligns with SQL Server 2025 standards, as older compatibility levels might have different internal limits handling.
  • Run: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • Update if necessary: ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

  • Verification: Re-run the original CREATE TABLE or ALTER TABLE script. If the schema change applies without throwing Error 2728, the fix is confirmed.
  • Alternative: If business logic strictly requires more than 16 distinct values for partitioning, consider implementing a custom hashing algorithm at the application layer to merge keys into a single VARBINARY or INT column before insertion, bypassing the need for native multi-column partitioning.