Specifying Default Column Values

A default is a value SQL Server inserts into a column automatically whenever an INSERT statement doesn't supply one of its own. A default is useful in two common situations: when you want to guarantee a column is never left blank, and when a large share of your rows share the same value for a column anyway, saving every caller the trouble of typing it out each time. A classic example is a column storing the state where an employee lives: if the overwhelming majority of your employees live in California, declaring a default of 'CA' on that column means most INSERT statements never have to mention it at all, and the column still ends up correct.

SQL Server 2025 implements this with a DEFAULT constraint, attached to the column at the point it's declared. A default can hold a literal constant, a built-in system function, a sequence value, or an expression, not just a fixed value typed in quotes, which is a good deal more flexible than it might first appear.

When you define a default inside a CREATE TABLE statement, give it an explicit name, following the pattern CONSTRAINT DF_<TableName>_<ColumnName>, rather than letting SQL Server generate one on its own. An unnamed default gets a system-generated name that looks something like DF__Orders__3F466844, which makes the constraint awkward to find, modify, or drop later, and can break schema-comparison or deployment scripts that expect a stable, predictable name. The same explicit-naming discipline applies to every constraint type, primary keys and foreign keys included, but it matters especially here, since a default is one of the constraints you're most likely to need to change down the road.

CREATE TABLE Sales.Orders
(
    OrderID INT IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_Orders_OrderID PRIMARY KEY CLUSTERED,

    -- string literal default
    OrderStatus VARCHAR(20) NOT NULL
        CONSTRAINT DF_Orders_OrderStatus DEFAULT ('Pending'),

    -- numeric literal default
    DiscountPercent DECIMAL(5, 2) NOT NULL
        CONSTRAINT DF_Orders_DiscountPercent DEFAULT (0.00),

    -- system function default
    CreatedAt DATETIME2(7) NOT NULL
        CONSTRAINT DF_Orders_CreatedAt DEFAULT (SYSDATETIME()),

    CreatedBy NVARCHAR(128) NOT NULL
        CONSTRAINT DF_Orders_CreatedBy DEFAULT (SUSER_SNAME())
);

A DEFAULT expression can contain literal constants; niladic system functions such as SYSDATETIME(), SUSER_SNAME(), ORIGINAL_LOGIN(), HOST_NAME(), NEWID(), and NEWSEQUENTIALID(); a scalar user-defined function; or NEXT VALUE FOR <SequenceName>. What it can't do is reference another column in the same table or contain a subquery. If a column's value genuinely needs to depend on other columns in the same row, say, an ExtendedPrice column that should always equal Quantity * UnitPrice, that calls for a computed column instead of a default:

ExtendedPrice AS (Quantity * UnitPrice) PERSISTED

A computed column recalculates its value from the other columns in the row every time they change; a default only ever supplies a value once, at the moment a row is inserted and the column is otherwise left unspecified. Confusing the two is a common mistake: reaching for a default when what you actually need is a value derived from the rest of the row.

A table already in production often needs a default added after the fact, either on a column that already exists or on a new one you're introducing, and SQL Server behaves a little differently depending on which.

Attaching a default to an existing column binds a new constraint to it with a FOR clause. This only applies to rows inserted from that point forward; it does nothing to rows that already exist in the table.

ALTER TABLE Sales.Orders
ADD CONSTRAINT DF_Orders_OrderStatus
DEFAULT ('Pending') FOR OrderStatus;

Adding a brand-new column with a default behaves differently depending on whether the column allows NULL. If the new column is declared NOT NULL, SQL Server populates the default into every existing row automatically, as a fast, metadata-only operation that doesn't have to rewrite the table's data pages. If the new column is declared NULL instead, that automatic backfill doesn't happen; existing rows simply get NULL in the new column, unless you append WITH VALUES to force the default into them too:

ALTER TABLE Sales.Orders
ADD IsArchived BIT NULL
    CONSTRAINT DF_Orders_IsArchived DEFAULT (0) WITH VALUES;

Without WITH VALUES here, every existing order would end up with NULL in IsArchived rather than 0, even though the column's own default says otherwise, simply because a nullable column's default is only ever consulted for new rows unless you explicitly ask for it to apply retroactively.

If you'd rather use the graphical designer instead of T-SQL, SQL Server Management Studio exposes the same setting under a column's properties:

1. In Object Explorer, expand your database and the Tables folder, then right-click the table and choose Design.
2. Select the column you want to give a default to in the column grid at the top of the designer.
3. In the Column Properties pane below, find the Default Value or Binding property and type the literal, expression, or system function you want used.
4. Press Ctrl+S, or click the Save icon, to apply the change.

Behind the scenes, this generates exactly the same ALTER TABLE ... ADD CONSTRAINT ... DEFAULT statement shown above, just without a name you chose yourself; SSMS assigns its own system-generated constraint name unless you go back afterward and rename it. For that reason, many SQL Server developers still prefer writing the ALTER TABLE statement by hand for anything going into source control or a deployment script, where a stable, predictable constraint name actually matters, and reserve the designer for quick, exploratory changes on a development database.

Transact-SQL has no ALTER CONSTRAINT statement, so changing a default's value means dropping the existing constraint and adding a new one in its place. Wrapping both statements in a single transaction keeps the table from being left without a default, even momentarily, if something goes wrong between the two steps.

BEGIN TRANSACTION;

ALTER TABLE Sales.Orders
DROP CONSTRAINT DF_Orders_OrderStatus;

ALTER TABLE Sales.Orders
ADD CONSTRAINT DF_Orders_OrderStatus
DEFAULT ('Processing') FOR OrderStatus;

COMMIT TRANSACTION;

This is exactly why naming every default constraint explicitly, as recommended above, pays off down the line: dropping DF_Orders_OrderStatus is a single, readable line. If an older table was built with an unnamed default instead, you'll need to look up its system-generated name first before you can drop it, which means querying sys.default_constraints:

SELECT dc.name AS DefaultConstraintName,
       c.name  AS ColumnName,
       dc.definition AS DefaultExpression
FROM sys.default_constraints AS dc
INNER JOIN sys.columns AS c
    ON dc.parent_object_id = c.object_id
   AND dc.parent_column_id = c.column_id
WHERE dc.parent_object_id = OBJECT_ID('Sales.Orders');

That query returns the constraint's actual name alongside the column it's attached to and the expression it evaluates, everything you need to write a correct DROP CONSTRAINT statement without guessing.

Default values aren't limited to simple scalar types. SQL Server 2025's native JSON type accepts a default too, typically an empty JSON object produced by the built-in JSON_OBJECT() function, so that a column meant to hold structured metadata is never left in an invalid, unparseable state:

OrderMetadata JSON NOT NULL
    CONSTRAINT DF_Orders_OrderMetadata DEFAULT (JSON_OBJECT())

This follows the same rule as every other default: it only fires for a row where the column was left out of the INSERT entirely. A caller who explicitly passes an empty string or a malformed JSON fragment for that column still gets rejected or stored as given; the default doesn't repair bad input, it only fills in missing input. Columns that need a value genuinely computed from other data in the same row, rather than a fixed starting point, still belong to a computed column as described above, not a default, regardless of the datatype involved.

A default only comes into play in specific circumstances, and knowing exactly which ones prevents a few unpleasant surprises later.

• The column is left out of the INSERT list entirely. This is the ordinary case: omit the column, and SQL Server quietly substitutes its default.
• The DEFAULT keyword is passed explicitly. You can force a column back to its defined default inside an INSERT ... VALUES or UPDATE ... SET statement, which is handy for resetting a row without having to remember what the default's current value actually is:

  
  INSERT INTO Sales.Orders (DiscountPercent, OrderStatus, CreatedAt)
  VALUES (5.00, DEFAULT, DEFAULT);
  
  UPDATE Sales.Orders
  SET OrderStatus = DEFAULT
  WHERE OrderID = 1042;
  

• Every column has a default or allows NULL. In that case, SQL Server will let you insert a row with no values specified at all:

  
  INSERT INTO Sales.Orders DEFAULT VALUES;
  

• It does not fire for an explicit NULL. This is the trap that catches people most often. If an INSERT INTO ... SELECT statement returns NULL for a column that's explicitly named in the column list, SQL Server inserts that NULL as-is; it does not fall back to the column's default, and the statement fails outright if the column is NOT NULL. Wrapping a nullable source column in ISNULL(src.Status, 'Pending') when selecting from another table avoids the problem entirely, by never letting the explicit NULL reach the INSERT in the first place:

  
  INSERT INTO Sales.Orders (OrderStatus, DiscountPercent)
  SELECT ISNULL(src.Status, 'Pending'), src.Discount
  FROM Staging.LegacyOrders AS src;
  

A default is often the practical answer to a question the previous lesson raised: how do you make a column NOT NULL without forcing every caller to supply a value for it on every single insert? Pair the two attributes, and the column gets the best of both: SQL Server still enforces that the column can never hold NULL, while the default quietly fills in a reasonable value whenever a caller doesn't provide one of their own. OrderStatus VARCHAR(20) NOT NULL CONSTRAINT DF_Orders_OrderStatus DEFAULT ('Pending') from the CREATE TABLE example above is exactly this pairing in practice.

It's worth being precise about where the two attributes stop overlapping, though. Nullability governs what's allowed to be stored in the column, no exceptions, regardless of how a value got there. A default only governs what happens when a value is missing from the statement altogether. A column can be NOT NULL with no default at all, in which case every INSERT simply has to supply a value or the statement fails; equally, a column can have a default while still allowing NULL, in which case an explicit NULL is still accepted and stored exactly as given, rather than being quietly swapped out for the default. The two attributes solve related but genuinely different problems, and declaring one doesn't automatically imply anything about the other.

In the next lesson, you'll learn how to use the IDENTITY property to have SQL Server generate sequential numeric values for a column automatically, along with the ROWGUIDCOL flag for globally unique identifiers.