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

by

Troubleshooting Guide: SQL Server Error 11436

Why This Error Happens

This error occurs when attempting to add or modify a column to be a hidden column (typically used in temporal tables or computed column scenarios) while simultaneously marking it as NOT NULL, without providing a DEFAULT constraint. SQL Server requires a default value for non-nullable hidden columns because it needs to backfill existing rows with a valid value during the schema change, and without a default, the engine has no value to insert into pre-existing records. This is a strict validation rule enforced by the Database Engine during ALTER TABLE operations in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending ALTER TABLE Statement

  • Locate the exact T-SQL script that triggered Error 11436 in your SSMS query history or deployment script (DACPAC/SSDT).
  • Check if the column definition includes HIDDEN and NOT NULL together, such as:
    ALTER TABLE dbo.YourTable ADD NewColumn INT HIDDEN NOT NULL;

Step 2: Add a DEFAULT Constraint to the Column Definition

  • Modify the script to include a DEFAULT value so existing rows can be populated correctly.
  • Updated syntax should look like this:
    ALTER TABLE dbo.YourTable ADD NewColumn INT HIDDEN NOT NULL CONSTRAINT DF_NewColumn DEFAULT (0);
  • Ensure the default value is logically appropriate for your application (e.g., 0, GETDATE(), or an empty string).

Step 3: Validate Temporal Table Compatibility (If Applicable)

  • If this error occurs on a system-versioned temporal table, verify that the hidden PERIOD columns (SysStartTime/SysEndTime) are not conflicting with your new column logic.
  • Confirm that SYSTEM_VERSIONING is temporarily set to OFF if you need to manually adjust history table schemas before re-enabling it.

Step 4: Re-run the Schema Migration

  • Execute the corrected ALTER TABLE script against the target database.
  • If using SSDT or a DACPAC for deployment, republish the project after updating the .sql definition file to include the default constraint.

Alternative or Verification Steps

  • Verify the Fix: Run SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.YourTable') AND is_hidden = 1; to confirm the column exists and check sys.default_constraints to ensure the default is bound correctly.
  • Alternative Solution: If a default value is not logically viable for the business case, consider adding the column as NULLable first, backfilling the data via an UPDATE statement, and then altering the column to NOT NULL with HIDDEN once all rows contain valid data.