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

by

Troubleshooting Guide: SQL Server Error 1741 — Cannot Create Sparse Column Set with Encrypted Sparse Columns

Why This Error Happens

This error occurs because SQL Server does not support combining column sets (sparse column sets used for SELECT * consolidation) with sparse columns that are also protected by Always Encrypted. The Database Engine cannot generate a unified XML representation for the column set when one or more underlying sparse columns contain encrypted data, since encryption metadata conflicts with sparse storage optimization rules. This is a design limitation rather than a configuration or permissions issue, meaning the table schema itself must be changed to resolve it.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Sparse Columns

  • Run a query against sys.columns and sys.column_encryption_keys to find which sparse columns in the affected table have encryption enabled.
  • Use the following T-SQL to list sparse columns and their encryption status:
  SELECT name, is_sparse, encryption_type_desc
  FROM sys.columns
  WHERE object_id = OBJECT_ID('YourTableName') AND is_sparse = 1;
  • Note down the specific column(s) causing the conflict before proceeding.

Step 2: Remove Encryption or Remove Sparse Attribute

  • Decide whether the column needs to remain encrypted or remain sparse, since both cannot coexist within a column set.
  • To remove encryption from the column, use SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) and run the Encrypt Columns Wizard in reverse to decrypt the specific column.
  • Alternatively, use PowerShell with the Invoke-SqlColumnMasterKeyRotation or Set-SqlColumnEncryption cmdlet to modify encryption settings on the column.
  • If encryption must be retained, instead drop the SPARSE attribute using:
  ALTER TABLE YourTableName ALTER COLUMN YourColumnName <datatype> NULL;

Step 3: Drop and Recreate the Column Set

  • Since column sets cannot be altered directly, drop the existing column set definition from the table.
  • Use SSMS Object Explorer, navigate to Databases > YourDatabase > Tables > YourTable > Columns, and manually remove the column set entry.
  • Recreate the table (or use ALTER TABLE ... ADD for a new column set) only after confirming no sparse column remains encrypted:
  ALTER TABLE YourTableName
  ADD YourColumnSetName XML COLUMN_SET FOR ALL_SPARSE_COLUMNS;

Step 4: Restart SQL Server Service to Clear Cached Metadata

  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Locate SQL Server (MSSQLSERVER) or your named instance, right-click, and select Restart.
  • This clears any cached execution plans or metadata that may still reference the invalid column set configuration.

Alternative or Verification Steps

  • Verify the fix by re-running the original CREATE TABLE or ALTER TABLE statement that triggered Error 1741; it should now complete without issue.
  • If the error persists, use SQL Server Profiler or Extended Events to trace the exact DDL statement and confirm no residual encrypted sparse columns remain linked to the column set definition.