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

by

Troubleshooting Guide: SQL Server Error 11903

Why This Error Happens

This error occurs when creating or altering a table that includes a hidden column (used with features like period columns in system-versioned temporal tables) that is also marked as NOT NULL, but no DEFAULT constraint has been supplied. SQL Server 2025 requires a default value for non-nullable hidden columns because the engine needs a way to populate that column automatically for existing or newly inserted rows without explicit values. Without this default, the engine cannot guarantee data integrity for rows where the hidden column value is not explicitly provided, so the CREATE TABLE or ALTER TABLE statement is rejected.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column Definition

  • Locate the CREATE TABLE or ALTER TABLE script that triggered Error 11903.
  • Check for any column defined with HIDDEN and NOT NULL keywords together, commonly seen in temporal table period columns (e.g., SysStartTime, SysEndTime).
  • Confirm whether a DEFAULT constraint is missing from that column definition.

Step 2: Add a DEFAULT Constraint to the Hidden Column

  • Modify the T-SQL script to include a DEFAULT clause for the non-nullable hidden column.
  • Example syntax:
ALTER TABLE dbo.YourTable
ADD SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
    CONSTRAINT DF_SysStartTime DEFAULT SYSUTCDATETIME(),
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
    CONSTRAINT DF_SysEndTime DEFAULT '9999-12-31 23:59:59.9999999';
  • Ensure the default value type matches the column’s data type to avoid implicit conversion errors.

Step 3: Re-run the Table Creation or Alteration Script

  • Execute the corrected script in SQL Server Management Studio (SSMS) or Azure Data Studio.
  • Confirm there are no additional syntax errors by checking the Messages tab in SSMS.
  • If using automated deployment (e.g., SSDT or DACPAC), redeploy the updated schema package.

Step 4: Validate Server and Database Compatibility Level

  • Open SSMS and run the following to confirm the database compatibility level supports hidden period columns:
SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
  • If the compatibility level is below 160 (SQL Server 2022) or the 2025 equivalent, update it:
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

  • After applying the fix, run sp_help 'YourTable' or query sys.columns to confirm the hidden column now has an associated default constraint listed under sys.default_constraints.
  • If the error persists, consider removing the HIDDEN attribute temporarily to isolate whether the issue is related to the hidden property or the NOT NULL/DEFAULT combination itself.