CREATE TABLE: SQL Server source table

In Materialize, you can create read-only tables from SQL Server sources created using the new syntax.

Syntax

To create a read-only table from a source connected (via native connector) to an external SQL Server database:

CREATE TABLE [IF NOT EXISTS] <table_name> FROM SOURCE <source_name> (REFERENCE <upstream_table>)
[WITH (
    TEXT COLUMNS (<column_name> [, ...])
  | EXCLUDE COLUMNS (<column_name> [, ...])
  | PARTITION BY (<column_name> [, ...])
  [, ...]
)]
;

SELECT refs.*
FROM mz_internal.mz_source_references refs, mz_sources s
WHERE s.name = '<source_name>' -- substitute with your source name
AND refs.source_id = s.id;

Details

DDL transaction block

For performance, when issuing multiple CREATE TABLE FROM SOURCE... statements, use within a transaction block.

Source-populated tables and snapshotting

Creating the tables from sources starts the snapshotting process. Snapshotting syncs the currently available data into Materialize. Because the initial snapshot is persisted in the storage layer atomically (i.e., at the same ingestion timestamp), you are not able to query the table until snapshotting is complete.

Supported data types

Materialize natively supports the following SQL Server types:

• tinyint
• smallint
• int
• bigint
• real
• double precision
• float
• bit
• decimal
• numeric
• money
• smallmoney
• char
• nchar
• varchar
• varchar(max)
• nvarchar
• nvarchar(max)
• sysname
• binary
• varbinary
• json
• date
• time
• smalldatetime
• datetime
• datetime2
• datetimeoffset
• uniqueidentifier

char and nchar columns

To preserve values exactly as SQL Server returns them, char and nchar columns are replicated as text rather than fixed-length. SQL Server and Materialize measure fixed-length character types differently, so replicating as text avoids truncation and padding mismatches.

To replicate tables that contain the following unsupported data types, you can use either the TEXT COLUMNS or the EXCLUDE COLUMNS option:

Handling table schema changes

The use of CREATE SOURCE (new syntax) with CREATE TABLE FROM SOURCE allows for the handling of the upstream DDL changes, specifically adding or dropping columns in the upstream tables, without downtime. For details, see SQL Server: Handling upstream schema changes with zero downtime.

See also Handling upstream operations for additional upstream operation considerations.

Handling upstream operations

This section describes how changes to upstream tables that Materialize ingests affect the corresponding Materialize tables.

Adding a column

When you add a new column to your upstream table, Materialize continues to ingest only the existing columns.

To incorporate the new column:

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, create a new table from the source. See Handle upstream column addition.

• If using the legacy CREATE SOURCE ... FOR ... syntax that creates subsources, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE. The re-added subsource includes the new column.

Dropping a column

Dropping columns that Materialize does not ingest (for example, columns added after the source was created, or columns that are excluded) is supported. As these columns were never ingested, you can drop them without issue.

If your Materialize source ingests a column, dropping that column from your upstream table puts the affected table into an error state.

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, you can safely drop a column by first ignoring it in Materialize. See Handle upstream column drop.

• If using legacy CREATE SOURCE ... FOR ... syntax, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE.

Changing constraints

Materialize ignores foreign key and CHECK constraint changes. You can add or drop them without affecting ingestion.

Adding a UNIQUE constraint does not affect ingestion. Dropping a UNIQUE constraint puts the affected table into an error state.

SQL Server does not allow dropping a PRIMARY KEY from a table while change data capture is enabled on it. A primary key that existed when Materialize began ingesting the table therefore cannot be dropped upstream.

Adding or removing a NOT NULL constraint on an ingested column requires an upstream ALTER COLUMN, which puts the affected table into an error state. See Changing a column’s data type.

Changing a column’s data type

Any upstream ALTER COLUMN on an ingested column puts the affected Materialize table into an error state. This covers every ALTER COLUMN operation, not just data-type changes. Changing a column’s collation, sparseness, masking, or nullability all error the table the same way. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Renaming a column

Renaming a column that Materialize ingests puts the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Removing a capture instance

SQL Server allows up to two capture instances to exist for a table at once. Materialize ingests from one of them.

Removing the capture instance that Materialize is using puts the affected table into an error state. Removing a capture instance that Materialize is not using does not affect ingestion.

Table-level operations

The following upstream operations put the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the affected table in Materialize to resume:

• Dropping a table (DROP TABLE).
• Renaming a table or moving it to a different schema.

Privileges

The privileges required to execute this statement are:

• CREATE privileges on the containing schema.
• USAGE privileges on all types used in the table definition.
• USAGE privileges on the schemas that all types in the statement are contained in.

Examples

Create a table

To create new read-only tables from a source table, use the CREATE TABLE ... FROM SOURCE ... (REFERENCE <upstream_table>) statement in a DDL transaction block. The following example creates read-only tables items and orders from the SQL Server source’s dbo.items and dbo.orders tables.

/* This example assumes:
  - In the upstream SQL Server, you have:
    - Enabled Change Data Capture (CDC) on the database and the tables.
    - A user and password with the appropriate access.
  - In Materialize:
    - You have created a secret for the SQL Server password.
    - You have defined the connection to the upstream SQL Server.
    - You have used the connection to create a source.

   For example (substitute with your configuration):
      CREATE SECRET sqlserver_pass AS '<password>'; -- substitute
      CREATE CONNECTION sql_server_connection TO SQL SERVER (
        HOST '<hostname>',          -- substitute
        PORT 1433,
        DATABASE <db>,              -- substitute
        USER <user>,                -- substitute
        PASSWORD SECRET sqlserver_pass
        -- [, <network security configuration> ]
      );

      CREATE SOURCE sql_server_source
      FROM SQL SERVER CONNECTION sql_server_connection;
*/

BEGIN;
CREATE TABLE items
FROM SOURCE sql_server_source (REFERENCE dbo.items)
;
CREATE TABLE orders
FROM SOURCE sql_server_source (REFERENCE dbo.orders)
;
COMMIT;

Creating the tables from sources starts the snapshotting process. Snapshotting syncs the currently available data into Materialize. Because the initial snapshot is persisted in the storage layer atomically (i.e., at the same ingestion timestamp), you are not able to query the table until snapshotting is complete.

To verify that the tables have been created, you can run SHOW TABLES to list all tables in the current schema:

SHOW TABLES;

The results should include the tables items and orders:

| name   | comment |
| ------ | ------- |
| items  |         |
| orders |         |

Inspect the table columns using the SHOW COLUMNS command:

SHOW COLUMNS FROM items;

The results should display the table columns, with types mapped from the upstream SQL Server table. For the list of supported SQL Server data types, refer to supported types.

| name     | nullable | type              | comment |
| -------- | -------- | ----------------- | ------- |
| id       | false    | integer           |         |
| item     | true     | character varying |         |
| quantity | true     | integer           |         |

Once the snapshotting process completes and the tables are in the running state, you can query them:

SELECT * FROM items ORDER BY id;

| id | item   | quantity |
| -- | ------ | -------- |
| 1  | widget | 5        |
| 2  | gadget | 2        |

Related pages

• CREATE SOURCE: SQL Server (New Syntax)
• DROP TABLE