Default values

Emulation context

In Microsoft SQL Server, columns can be assigned default values using the DEFAULT keyword in a CREATE TABLE or ALTER TABLE statement. These defaults are automatically applied when an INSERT operation omits a value for that column. Common use cases include assigning timestamps (GETDATE()), default statuses ('Active'), or generating identifiers (NEWID()).

For example:

CREATE TABLE ExampleTable (
    ID uniqueidentifier DEFAULT NEWID() ROWGUIDCOL,
    Name varchar(50) NOT NULL,
    Status varchar(20) DEFAULT 'Active', 
    CreatedAt DATETIME2(6) DEFAULT GETDATE()
);

In Fabric Warehouse, column-level default constraints are not supported. The DEFAULT keyword in table definitions is not honored, and values are not auto-generated during insert unless explicitly provided. This creates a compatibility gap where logic relying on implicit defaults must be rewritten or made explicit.

Emulation strategy

SQL Tran emulates default values by rewriting INSERT statements that depend on implicit defaults into versions that explicitly supply those values. The emulation process includes:

  • Removing DEFAULT expressions from table definitions. SQL Tran comments out default constraints using /* SQLTRAN FIX: DEFAULT ... */.

  • Preserving the column structure but stripping unsupported features such as ROWGUIDCOL.

  • Rewriting INSERT statements that omit defaultable columns by injecting the literal or function values directly (e.g., GETDATE(), 'Active', NEWID()).

  • Retaining explicit uses of the DEFAULT keyword in INSERT ... VALUES statements when syntactically valid.

  • Expanding INSERT INTO ... DEFAULT VALUES into a full column list with literal defaults, when possible.

This transformation ensures that data insertion behavior remains consistent with SQL Server, even though Fabric does not support implicit default application.

Code example

SQL Server:

CREATE   PROCEDURE [dbo].[TestDefaultValues]
AS
BEGIN
	CREATE TABLE ExampleTable (
		ID uniqueidentifier DEFAULT NEWID() ROWGUIDCOL,
		Name varchar(50) NOT NULL,
		Status varchar(20) DEFAULT 'Active', 
		CreatedAt DATETIME2(6) DEFAULT GETDATE(),
	);

	INSERT INTO ExampleTable (ID, Name, Status, CreatedAt)
	VALUES (NEWID(), 'Alice', 'Inactive', SYSUTCDATETIME());

	INSERT INTO ExampleTable (Name)
	VALUES ('Bob');

	INSERT INTO ExampleTable (ID, Name, Status, CreatedAt)
	VALUES (DEFAULT, 'Charlie', DEFAULT, DEFAULT);

	INSERT INTO ExampleTable DEFAULT VALUES;
END;

Fabric Warehouse (generated by SQL Tran):

CREATE   PROCEDURE [dbo].[TestDefaultValues]
AS
BEGIN
	CREATE TABLE ExampleTable (
		ID uniqueidentifier /* SQLTRAN FIX: DEFAULT NEWID() */ /* SQLTRAN FIX: ROWGUIDCOL */,
		Name varchar(50) NOT NULL,
		Status varchar(20) /* SQLTRAN FIX: DEFAULT 'Active' */, 
		CreatedAt DATETIME2(6) /* SQLTRAN FIX: DATETIME2(6) */ /* SQLTRAN FIX: DEFAULT GETDATE() */,
	);

	INSERT INTO ExampleTable (ID, Name, Status, CreatedAt)
	VALUES (NEWID(), 'Alice', 'Inactive', SYSUTCDATETIME());

	
INSERT
	INTO ExampleTable ([ID], Name, [Status], [CreatedAt])
	VALUES (NEWID(), 'Bob', 'Active', GETDATE());
 /* SQLTRAN FIX: INSERT INTO ExampleTable (Name)
	VALUES ('Bob'); */

	INSERT INTO ExampleTable (ID, Name, Status, CreatedAt)
	VALUES (DEFAULT, 'Charlie', DEFAULT, DEFAULT);

	
INSERT
	INTO ExampleTable ([ID], [Status], [CreatedAt])
	DEFAULT VALUES;
 /* SQLTRAN FIX: INSERT INTO ExampleTable DEFAULT VALUES; */
END;

Important notes

  • Table defaults not supported: Fabric does not support column-level DEFAULT constraints. SQL Tran removes them during translation, preserving the original logic through comment annotations.

  • No impact on updates: Default values apply only during INSERT. Updates never trigger default expressions in either SQL Server or Fabric. SQL Tran does not alter update logic in this context.

  • DEFAULT keyword compatibility: SQL Tran retains use of the DEFAULT keyword in INSERT statements when present in the source. In Fabric, this keyword is accepted and inserts the implicit default value for the column data type (e.g., NULL for most types), since Fabric does not support default constraints.

  • DEFAULT VALUES transformed: SQL Tran rewrites INSERT INTO ... DEFAULT VALUES into a form with explicit column names and manually applied defaults. This avoids runtime failure due to missing required values.

  • Defaults during external data ingestion: When ingesting data using Fabric-supported tools, default expressions defined in the target table schema are not automatically applied. If default values are required, they must be added explicitly as part of the ingestion logic.

  • Manual review recommended: All logic that previously relied on automatic default assignment should be reviewed to ensure correctness in the translated output and external ingestion processes.

PreviousIdentity columnsNextSequences

Last updated