CREATE TABLE

CREATE TABLE#

Creates a new table in the virtual database.

Syntax#

CREATE OR REPLACE TABLE IF NOT EXISTS table ( column type COLLATE collation IDENTITY ( seed , increment ) GENERATED ALWAYS AS ROW TRANSACTION_ID SEQUENCE_NUMBER START END HIDDEN NOT NULL column constraint table constraint PERIOD FOR SYSTEM_TIME ( start column , end column ) , ) WITH ( SYSTEM_VERSIONING = ON ( history options ) LEDGER = ON ( ledger options ) , )

A column definition gives a column name and data type, optionally followed by NULL / NOT NULL, a collation or a column constraint (PRIMARY KEY, UNIQUE or REFERENCES). A table constraint defines a PRIMARY KEY, UNIQUE, FOREIGN KEY or CHECK constraint over one or more columns; constraints can be named with CONSTRAINT name.

Querona also supports CREATE TABLE … AS SELECT (CTAS) to create and populate a table from a query in a single statement.

Constraints are declarative: Querona records them in table metadata, reports them through the catalog views (sys.key_constraints, sys.check_constraints, sys.indexes, INFORMATION_SCHEMA.TABLE_CONSTRAINTS) and includes them in the DDL sent to the data source, which enforces them. Querona itself does not verify constraint predicates on writes.

Arguments#

table

The name of the new table.

column definition

column_name data_type [ COLLATE collation ] [ IDENTITY [ ( seed , increment ) ] ] [ GENERATED ALWAYS AS { ROW | TRANSACTION_ID | SEQUENCE_NUMBER } { START | END } [ HIDDEN ] ] [ NULL | NOT NULL ] [ column_constraint ]. GENERATED ALWAYS AS ROW START | END declares a temporal table’s period column — see Temporal tables below; the TRANSACTION_ID and SEQUENCE_NUMBER variants declare a ledger table’s generated-always columns — see Ledger tables below.

column constraint

[ CONSTRAINT name ] { PRIMARY KEY | UNIQUE [ CLUSTERED ] | REFERENCES table ( column ) }. A PRIMARY KEY column becomes NOT NULL implicitly; a UNIQUE column stays nullable unless declared otherwise.

table constraint

[ CONSTRAINT name ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] ( column [ ASC | DESC ], … ), a FOREIGN KEY ( columns ) REFERENCES table ( columns ) constraint (with optional ON DELETE / ON UPDATE actions), a CHECK [ NOT FOR REPLICATION ] ( predicate ) constraint, or a PERIOD FOR SYSTEM_TIME ( start_column , end_column ) clause (see Temporal tables below). A table can have at most one PRIMARY KEY, one PERIOD FOR SYSTEM_TIME, and any number of UNIQUE and CHECK constraints.

Note

DEFAULT values, computed columns and inline INDEX definitions are not supported and are rejected with an error.

IDENTITY columns#

A column may be declared IDENTITY or IDENTITY ( seed , increment ) so the data source generates its values automatically on insert. Rules:

  • At most one identity column per table.

  • Allowed types: tinyint, smallint, int, bigint, decimal(p,0) or numeric(p,0).

  • The column is implicitly NOT NULL; declaring it NULL is an error, and it cannot also have a DEFAULT.

  • seed and increment are supplied together or not at all; the default is (1, 1). Both may be negative and increment cannot be 0. Both must fit in bigint; an out-of-range value is rejected.

  • NOT FOR REPLICATION is not supported and is rejected with an error.

Querona renders the declaration in the target provider’s native identity dialect or equivalent and never generates values itself — the connected data source always owns value generation:

  • SQL Server: rendered verbatim as IDENTITY(seed, increment).

  • PostgreSQL, Oracle, DB2, SAP HANA and Teradata: rendered as GENERATED BY DEFAULT AS IDENTITY (START WITH seed INCREMENT BY increment).

  • MySQL: any seed is accepted when increment is 1 — rendered as an AUTO_INCREMENT column, plus an AUTO_INCREMENT = seed table option when seed is not 1. The identity column must also be indexed (PRIMARY KEY or UNIQUE). Any other increment, or a non-indexed identity column, is a capability error.

  • StarRocks: only the default IDENTITY / IDENTITY(1, 1) on a column mapped to bigint is accepted — rendered as an AUTO_INCREMENT column. Any other seed/increment, or a non-bigint identity column, is a capability error.

  • Vertica: any seed and increment are accepted with full fidelity, through a named sequence Querona creates alongside the table (qua_identity_<table>_<column>, in the table’s schema) and assigns as the column’s default value. DROP TABLE also drops the sequence. If a sequence with that name already exists, the CREATE fails with an error.

  • Every other provider — including Azure Synapse Analytics and Spark, and generic ADO.NET, ODBC and JDBC connections — and tables materialized in Querona’s own in-memory or #temp storage, reject CREATE TABLE with an IDENTITY column.

An implicit INSERT (no column list) skips the identity column — the data source generates its value. Explicitly targeting the identity column in an INSERT column list is rejected unless SET IDENTITY_INSERT has enabled it for that table:

Cannot insert explicit value for identity column in table '<table>' when IDENTITY_INSERT is set to OFF.

SELECT INTO does not carry the identity property to the target table — the copied column is a plain NOT NULL column, even when selecting directly from the source identity column.

Querona reports identity columns through sys.identity_columns (seed_value and increment_value) and through is_identity in sys.columns, and returns the configured seed and increment from IDENT_SEED and IDENT_INCR. Identity metadata already present on a SQL Server source table is imported during schema analysis, so pre-existing identity tables behave the same way through Querona without any DDL involved. A MySQL source’s AUTO_INCREMENT columns are imported the same way, but as the is_identity flag only — MySQL’s catalog does not expose a seed or increment to import.

SCOPE_IDENTITY() and @@IDENTITY return the last identity value generated by an INSERT in the current session, and NULL before any identity insert has happened in the session — or when the identity value was generated by any other data source, since capture happens only on the SQL Server family. Querona has no triggers, so the two are equivalent. Both are declared decimal(28,0) — narrower than SQL Server’s numeric(38,0), Querona’s TDS decimal-writer precision limit, the same shape already used by IDENT_SEED and IDENT_INCR. IDENT_CURRENT is not supported: its result is the data source’s live identity counter, which Querona has no mechanism to read at query-evaluation time.

Hidden columns#

A table imported from a SQL Server source can carry HIDDEN columns — for example the period columns of a system-versioned temporal table. Querona honors SQL Server’s own HIDDEN semantics for such a table: the columns are excluded from SELECT * and t.* expansion and from an implicit INSERT (no column list), while an explicit reference to a hidden column still works. sys.columns.is_hidden and generated_always_type / generated_always_type_desc report the same values the source reports.

SELECT * FROM dbo.Department;                          -- DeptID, DeptName only
SELECT DeptID, ValidFrom, ValidTo FROM dbo.Department;  -- hidden columns on explicit request
INSERT INTO dbo.Department VALUES (1, 'R&D');           -- implicit list = visible columns only

Note

HIDDEN columns reach Querona either by importing a table that already has them from a SQL Server source during schema analysis, or — for system-versioned temporal tables — from Querona’s own CREATE TABLE (see Temporal tables below), which accepts HIDDEN on a GENERATED ALWAYS AS ROW START / ROW END period column. Querona does not accept HIDDEN on an ordinary column.

Temporal tables#

Querona creates system-versioned temporal tables on a SQL Server 2016+ source — pushdown-only: the connected SQL Server executes the CREATE TABLE, including its own history table and versioning machinery, and Querona records the result in its own metadata so the table behaves the same way through Querona as it does against SQL Server directly. Every other provider, and a metadata-only (CREATE VIRTUAL TABLE) table, rejects a temporal CREATE TABLE with an error.

Run a system-versioned CREATE TABLE in autocommit mode. Querona rejects SYSTEM_VERSIONING inside an explicit transaction because it must inspect and register the source-created history table immediately after the source confirms the statement.

Querona does not auto-detect the source SQL Server version; the connection’s Server dialect must be set to SQL Server 2016 or newer in the connection settings. The default dialect is the base SQL Server tier, which rejects a temporal CREATE TABLE with the error:

CREATE TABLE statement is not supported by the SQL Server provider

A temporal table needs:

  • a PRIMARY KEY;

  • exactly one pair of datetime2 period columns, each declared NOT NULL and GENERATED ALWAYS AS ROW START / GENERATED ALWAYS AS ROW END (optionally HIDDEN — see Hidden columns above);

  • a PERIOD FOR SYSTEM_TIME ( start_column , end_column ) clause naming those two columns;

  • WITH ( SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = table [, DATA_CONSISTENCY_CHECK = ON | OFF ] [, HISTORY_RETENTION_PERIOD = INFINITE | count unit ] ) ] ), where unit is DAY(S), WEEK(S), MONTH(S) or YEAR(S).

CREATE TABLE dbo.Employees (
    EmployeeID INT NOT NULL PRIMARY KEY,
    Salary MONEY NOT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory,
                              DATA_CONSISTENCY_CHECK = ON,
                              HISTORY_RETENTION_PERIOD = 6 MONTHS));

HISTORY_TABLE may be omitted, in which case the source auto-names the history table (SQL Server’s own MSSQL_TemporalHistoryFor<object_id> convention); DATA_CONSISTENCY_CHECK and HISTORY_RETENTION_PERIOD may likewise be omitted, taking SQL Server’s own defaults:

CREATE TABLE dbo.Department (
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

Either way, Querona registers the history table in its own metadata as soon as the source confirms the CREATE — with or without an explicit name — so it is queryable through Querona immediately, including after an UPDATE on the base table moves the pre-update row there. sys.tables reports the same temporal_type, temporal_type_desc, history_table_id and history_retention_period* values for the base table that SQL Server itself reports, and the registered history table reports temporal_type = 1 (HISTORY_TABLE).

Note

Querona’s ALTER TABLE only supports adding columns and renaming (see ALTER TABLE) — there is no ALTER TABLE … SET (SYSTEM_VERSIONING = …). A temporal table’s system-versioning cannot be turned off, and the table cannot be dropped, through Querona: both require SET (SYSTEM_VERSIONING = OFF) executed directly against the SQL Server source first.

FOR SYSTEM_TIME time-travel querying is not supported.

Ledger tables#

Querona creates ledger tables on a SQL Server 2022+ source — pushdown-only, the same model as temporal tables above: the connected SQL Server executes the CREATE TABLE, including its history table (if any), ledger view and versioning machinery, and Querona records the result in its own metadata so the table behaves the same way through Querona as it does against SQL Server directly. Every other provider, and a metadata-only (CREATE VIRTUAL TABLE) table, rejects a ledger CREATE TABLE with an error.

Run a ledger CREATE TABLE in autocommit mode. Querona rejects LEDGER inside an explicit transaction because it must inspect and register the source-created companion objects immediately after the source confirms the statement.

Querona does not auto-detect the source SQL Server version; the connection’s Server dialect must be set to SQL Server 2022 or newer in the connection settings. The default dialect is the base SQL Server tier, which rejects a ledger CREATE TABLE with the error:

CREATE TABLE statement is not supported by the SQL Server provider

A ledger table is either updatable or append-only:

  • An updatable ledger table — WITH ( SYSTEM_VERSIONING = ON [ ( HISTORY_TABLE = table ) ], LEDGER = ON [ ( LEDGER_VIEW = view [ ( TRANSACTION_ID_COLUMN_NAME = column [, SEQUENCE_NUMBER_COLUMN_NAME = column ] [, OPERATION_TYPE_COLUMN_NAME = column ] [, OPERATION_TYPE_DESC_COLUMN_NAME = column ] ) ] ) ] ) — also requires SYSTEM_VERSIONING = ON: it is a system-versioned table like a temporal table, but does not require a PERIOD FOR SYSTEM_TIME clause. Like a temporal table, it still requires a PRIMARY KEY, even when no PERIOD FOR SYSTEM_TIME is declared. It needs four bigint columns declared GENERATED ALWAYS AS TRANSACTION_ID START | END and GENERATED ALWAYS AS SEQUENCE_NUMBER START | END; any omitted from the column list are added automatically (HIDDEN, with SQL Server’s own default names), the same way omitted period columns are for a temporal table. SQL Server creates a history table for it, named or anonymous exactly like a temporal table’s.

    CREATE TABLE dbo.Accounts (
    AccountID INT NOT NULL PRIMARY KEY,
    Balance MONEY NOT NULL
)
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);

  • An append-only ledger table — WITH ( LEDGER = ON ( APPEND_ONLY = ON ) ) — accepts only INSERT; SQL Server blocks UPDATE and DELETE at the source. It needs only the two START columns (TRANSACTION_ID and SEQUENCE_NUMBER), added automatically if omitted, and has no history table.

    CREATE TABLE AccessControl.KeyCardEvents (
    EmployeeID INT NOT NULL,
    AccessOperationDescription NVARCHAR(1024) NOT NULL,
    [Timestamp] DATETIME2 NOT NULL
)
WITH (LEDGER = ON (APPEND_ONLY = ON));

Both kinds also get a ledger view — a system-generated view over the ledger table (joined with its history table, for an updatable ledger table) that reports every row-level INSERT and DELETE operation, including the DELETE-then-INSERT pair an UPDATE produces. Its name defaults to <table>_Ledger, or the name given by LEDGER_VIEW = view; its four operation columns default to ledger_transaction_id, ledger_sequence_number, ledger_operation_type and ledger_operation_type_desc, or the names given by the matching LEDGER_VIEW sub-option above:

CREATE TABLE dbo.Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    Amount MONEY NOT NULL,
    StartTx  BIGINT GENERATED ALWAYS AS TRANSACTION_ID START HIDDEN NOT NULL,
    EndTx    BIGINT GENERATED ALWAYS AS TRANSACTION_ID END  HIDDEN NULL,
    StartSeq BIGINT GENERATED ALWAYS AS SEQUENCE_NUMBER START HIDDEN NOT NULL,
    EndSeq   BIGINT GENERATED ALWAYS AS SEQUENCE_NUMBER END  HIDDEN NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory),
      LEDGER = ON (LEDGER_VIEW = dbo.OrdersLedgerView (
                       TRANSACTION_ID_COLUMN_NAME = tx_id,
                       SEQUENCE_NUMBER_COLUMN_NAME = seq_no,
                       OPERATION_TYPE_COLUMN_NAME = op_type,
                       OPERATION_TYPE_DESC_COLUMN_NAME = op_type_desc)));

A table can combine ledger with an explicit temporal PERIOD FOR SYSTEM_TIME, as above — ledger and temporal versioning are independent features that happen to share the same GENERATED ALWAYS / SYSTEM_VERSIONING machinery.

Querona registers the history table (if any) and the ledger view in its own metadata as soon as the source confirms the CREATE, so both are queryable through Querona immediately — including the ledger view showing rows for writes already made through Querona. sys.tables reports the same ledger_type, ledger_type_desc, ledger_view_id and history_table_id values for the base table that SQL Server itself reports (history_table_id is NULL for an append-only ledger table); the registered history table reports ledger_type = 1 (HISTORY_TABLE), and the registered ledger view reports ledger_view_type = 1 (LEDGER_VIEW) in sys.views.

Note

Querona’s ALTER TABLE only supports adding columns and renaming (see ALTER TABLE) — there is no ALTER TABLE … SET (LEDGER = …) toggle, and once created a ledger table’s ledger status can never be removed (SQL Server itself enforces this, for both Querona-created and source-native ledger tables). Unlike a temporal table, though, dropping a ledger table needs no preceding ALTER: SQL Server renames a dropped ledger table (and, for an updatable ledger table, its history table and ledger view) rather than physically removing it, retaining it for audit purposes and marking it is_dropped_ledger_table = 1 in sys.tables.

An append-only ledger table rejects UPDATE and DELETE at the SQL Server source; Querona performs no pre-validation of its own and passes the source’s error through to the client — the same as an explicit INSERT into any GENERATED ALWAYS column, temporal or ledger.

Examples#

Create a table with a primary key:

CREATE TABLE dbo.Product (
    Id     int           NOT NULL PRIMARY KEY,
    Name   nvarchar(100) NOT NULL,
    Price  decimal(10,2) NULL
);

Create a table with named unique and check constraints:

CREATE TABLE dbo.Product (
    Id    int           NOT NULL,
    Code  nvarchar(20)  NOT NULL,
    Price decimal(10,2) NOT NULL,
    CONSTRAINT PK_Product PRIMARY KEY (Id),
    CONSTRAINT UQ_Product_Code UNIQUE (Code),
    CONSTRAINT CK_Product_Price CHECK (Price >= 0)
);

Create a table with an identity column, insert without naming it, then read back the configured seed and increment:

CREATE TABLE dbo.Product (
    Id    int          IDENTITY(1000, 10) PRIMARY KEY,
    Code  nvarchar(20) NOT NULL
);

INSERT INTO dbo.Product (Code) VALUES ('WIDGET-1'), ('WIDGET-2');   -- Id: 1000, 1010

SELECT IDENT_SEED('dbo.Product'), IDENT_INCR('dbo.Product');       -- 1000, 10

Create and populate a table from a query (CTAS):

CREATE TABLE dbo.ActiveProduct AS
SELECT Id, Name, Price
FROM   dbo.Product
WHERE  Discontinued = 0;

See also