SQL Reference

Complete reference for the SQL dialect supported by CSharpDB.

Data Types

CSharpDB uses a flexible type system. Arithmetic operators perform implicit coercion between numeric types where needed.

Identifiers

SQL identifiers are limited to 128 characters and cannot contain NUL.

Catalog lookup is ordinal case-insensitive for quoted and unquoted identifiers. Quoting preserves the identifier text but does not create case-sensitive lookup semantics.

CREATE TABLE "order details" (
                    "order id" INTEGER PRIMARY KEY,
                    "display ""name""" TEXT
                );
                

Statements

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name (
                    column_name type [PRIMARY KEY] [IDENTITY | AUTOINCREMENT] [NOT NULL]
                                     [COLLATE collation_name]
                                     [DEFAULT literal]
                                     [[CONSTRAINT name] CHECK (row_local_expression)]
                                     [REFERENCES other_table(column) [ON DELETE CASCADE | RESTRICT]],
                    ...,
                    [[CONSTRAINT name] CHECK (row_local_expression)],
                    [[CONSTRAINT name] PRIMARY KEY (column1 [, column2, ...])],
                    [[CONSTRAINT name] UNIQUE (column1 [, column2, ...])],
                    [[CONSTRAINT name] FOREIGN KEY (column1 [, column2, ...])
                        REFERENCES other_table(column1 [, column2, ...])
                        [ON DELETE CASCADE | RESTRICT]]
                );
                

Constraints:

Temporary Tables

CREATE TEMP TABLE [IF NOT EXISTS] temp_name (
                    column_name type [PRIMARY KEY] [IDENTITY | AUTOINCREMENT] [NOT NULL]
                                     [COLLATE collation_name],
                    ...
                );

                CREATE TEMPORARY TABLE [IF NOT EXISTS] temp_name (...);
                DROP TEMP TABLE [IF EXISTS] temp_name;
                PERSIST TEMP TABLE temp_name AS durable_name;
                

Temporary tables are session-scoped and backed by in-memory storage. Unqualified table names resolve to temporary tables first, then durable tables/views/external tables, so a temporary table can shadow a durable table for the current session. DROP TABLE name drops the temporary table first when such a shadow exists.

SELECT, INSERT, UPDATE, DELETE, and joins work against temporary tables through the normal SQL execution path. V1 supports columns, nullability, collation, integer primary key/identity behavior, and rowid fallback. V1 rejects temporary foreign keys, triggers, secondary indexes, external tables, validation rules, full-text indexes, ALTER TABLE, ANALYZE, and data hygiene operations.

Temporary tables do not appear in sys.tables, sys.objects, backups, checkpoints, or SaveToFileAsync. Current-session metadata is exposed through sys.temp_tables / sys_temp_tables and sys.temp_columns / sys_temp_columns.

PERSIST TEMP TABLE temp_name AS durable_name explicitly creates a new durable table using the temporary table schema and copies current rows through the normal durable mutation path. The durable target must not already exist. The command returns temp_table, target_table, and rows_persisted.

For embedded and ADO.NET connections, temporary tables live for the connection or database handle lifetime and are cleared when the session is disposed. Stateless HTTP/gRPC ExecuteSqlAsync rejects temporary table commands; use BeginTransaction plus ExecuteInTransaction for remote temporary workflows.

ALTER TABLE

ALTER TABLE table_name ADD COLUMN column_name type [constraints];
                ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (expression);
                ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name [, ...]);
                ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name [, ...]);
                ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name [, ...]) REFERENCES parent_table (column_name [, ...]) [ON DELETE RESTRICT | CASCADE];
                ALTER TABLE table_name DROP COLUMN column_name;
                ALTER TABLE table_name DROP CONSTRAINT constraint_name;
                ALTER TABLE table_name DROP PRIMARY KEY;
                ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT literal;
                ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
                ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
                ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL;
                ALTER TABLE table_name ALTER COLUMN column_name TYPE INTEGER;
                ALTER TABLE table_name ALTER COLUMN column_name TYPE REAL;
                ALTER TABLE table_name ALTER COLUMN column_name SET COLLATION collation_name;
                ALTER TABLE table_name ALTER COLUMN column_name DROP COLLATION;
                ALTER TABLE table_name RENAME TO new_name;
                ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
                ALTER TABLE table_name RENAME INDEX old_name TO new_name;
                

Default changes affect future writes; SET NOT NULL and named CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY additions validate existing rows before changing metadata. A logical single-column or composite primary key is added with an engine-owned unique backing index and makes every participating column NOT NULL. A single INTEGER primary key uses the physical row key. On populated tables, validated non-NULL unique column values become the new row IDs while every ready ordinary/unique SQL, constraint-internal, and foreign-key-internal index is rebuilt and swapped atomically with the table. Full-text, collection, other, and non-ready indexes reject the physical rekey before mutation.

DROP CONSTRAINT constraint_name requires the stored name and is the form generated by EF Core. DROP PRIMARY KEY targets the current key without requiring its name, including a legacy unnamed key. Either form preserves NOT NULL on former key columns. A primary or unique candidate key cannot be removed while a foreign key depends on it unless an equivalent ordered UNIQUE candidate remains.

Index rename preserves the physical index and its uniqueness behavior while updating durable catalog metadata. Unconstrained DROP COLUMN uses a transactional shadow B+tree rewrite and preserves row ids and unaffected indexes. Adding a column with a typed literal default backfills populated tables through the same rewrite path. DROP COLUMN rejects indexed/key/FK/check-dependent columns and stored-view, trigger, or validation-rule dependencies rather than weakening them.

TYPE INTEGER and TYPE REAL provide a bounded, exact numeric rewrite. INTEGER-to-REAL accepts only values in the inclusive range -2⁵³ through 2⁵³. REAL-to-INTEGER accepts only finite, integral values in the signed 64-bit range. NULL remains NULL. An incompatible stored value or default rejects the operation without changing the original root; use DROP DEFAULT before the type change and SET DEFAULT afterward when the old default is not valid for the target type. EF Core emits DROP DEFAULT, TYPE, SET DEFAULT, and nullability changes in the required order. EF migrations run that compound sequence in their surrounding migration transaction; manual replay must do the same so a failed conversion restores every facet.

SET COLLATION and DROP COLLATION are limited to TEXT columns. Numeric type and TEXT collation changes stream rows through transactional shadow roots, preserve physical row ids, revalidate CHECK constraints, and persist across reopen. For collation changes, ready ordinary and unique SQL indexes that inherit the column collation are rebuilt and swapped atomically with the table; explicit-collation and unrelated index roots remain unchanged. A newly colliding unique value rejects the rewrite and restores every original root. Primary/unique key constraints, incoming/outgoing foreign keys, full-text or collection dependencies, and non-ready indexes remain unsupported. The conservative preflight also rejects any view that references the table, any trigger owned by the table, a cross-table trigger that references the column, and an applicable validation rule. Indexed numeric changes, dependency rewriting, and TEXT/BLOB type conversions remain unsupported.

DROP TABLE

DROP TABLE [IF EXISTS] table_name;
                

CREATE INDEX

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
                ON table_name (column1 [, column2, ...]);
                

DROP INDEX

DROP INDEX [IF EXISTS] index_name;
                

CREATE VIEW

CREATE VIEW [IF NOT EXISTS] view_name AS select_statement;
                

DROP VIEW

DROP VIEW [IF EXISTS] view_name;
                

CREATE TRIGGER

CREATE TRIGGER [IF NOT EXISTS] trigger_name
                {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
                ON table_name
                [FOR EACH ROW]
                [WHEN condition]
                BEGIN
                    statement1;
                    [statement2;]
                    ...
                END;
                

Triggers can reference NEW and OLD row aliases in their body and WHEN condition:

• INSERT triggers: NEW is available
• DELETE triggers: OLD is available
• UPDATE triggers: both NEW and OLD are available

DROP TRIGGER

DROP TRIGGER [IF EXISTS] trigger_name;
                

ANALYZE

ANALYZE table_name;
                

Collects per-column statistics (distinct count, min/max, frequency histograms, quantile buckets) and index prefix statistics used by the query planner for cardinality estimation and operator selection. See Query Execution Pipeline for details on how statistics influence planning.

Data Manipulation

INSERT

INSERT INTO table_name [(column1, column2, ...)]
                VALUES (value_or_DEFAULT1, value_or_DEFAULT2, ...);

                INSERT INTO table_name DEFAULT VALUES;
                

Column list is optional when providing values for all columns in declaration order. Omitted columns and explicit DEFAULT markers use their literal column defaults; explicit NULL remains NULL.

UPDATE

UPDATE table_name
                SET column1 = expression1 [, column2 = expression2, ...]
                [WHERE condition];
                

DELETE

DELETE FROM table_name
                [WHERE condition];
                

SELECT

SELECT [DISTINCT] column_list
                FROM table_reference
                [JOIN ...]
                [WHERE condition]
                [GROUP BY column1 [, column2, ...]]
                [HAVING condition]
                [ORDER BY column1 [ASC | DESC] [, ...]]
                [LIMIT count]
                [OFFSET skip];
                

Column List

SELECT *                              -- all columns
                SELECT column_name                    -- single column
                SELECT column_name AS alias           -- aliased column
                SELECT table.column_name              -- qualified column
                SELECT expression                     -- computed value
                SELECT aggregate_function(...)        -- aggregate
                

FROM and JOIN

FROM table_name [AS alias]
                
                -- Join types
                INNER JOIN table_name ON condition
                LEFT  JOIN table_name ON condition
                RIGHT JOIN table_name ON condition
                CROSS JOIN table_name
                

All join types except CROSS JOIN require an ON condition.

Subqueries

-- Scalar subquery (must return a single value)
                SELECT (SELECT MAX(age) FROM users) AS max_age;
                
                -- IN subquery
                WHERE column IN (SELECT id FROM other_table)
                WHERE column NOT IN (SELECT id FROM other_table)
                
                -- EXISTS subquery
                WHERE EXISTS (SELECT 1 FROM other_table WHERE condition)
                

Data Hygiene

CSharpDB includes SQL-first data hygiene commands for duplicate cleanup, audit-only validation rules, and relationship auditing. These commands return normal query-shaped results through ExecuteSqlAsync, ADO.NET, Admin query tabs, HTTP, gRPC, and the CLI.

FIND DUPLICATES

FIND DUPLICATES IN table_name ON expression [, expression ...];
                

Scans the target table, evaluates the ON expressions for each row, and returns one row per duplicate group. Text keys use existing column or expression collation behavior, so COLLATE NOCASE can be used directly in the key list.

Result columns:

FIND DUPLICATES IN Customers ON Email COLLATE NOCASE;
                FIND DUPLICATES IN Contacts ON FirstName, LastName, Phone;
                

DEDUP

DEDUP table_name ON expression [, expression ...] KEEP FIRST | LAST;
                

Deletes non-winner rows for each duplicate group. KEEP FIRST keeps the lowest primary-key value when the table has a primary key, otherwise the lowest rowid. KEEP LAST keeps the highest primary-key value, otherwise the highest rowid. Deletes run through the normal table mutation path, including indexes, foreign keys, triggers, WAL, and transaction rollback.

Result columns: table_name, duplicate_group_count, rows_deleted, rows_kept.

DEDUP Customers ON Email COLLATE NOCASE KEEP FIRST;
                

MERGE DUPLICATES

MERGE DUPLICATES table_name ON expression [, expression ...];
                

Selects the same deterministic winner as KEEP FIRST, fills only NULL winner columns when exactly one non-null duplicate value is available, reports merge conflicts when multiple different values are found, and then deletes the duplicate rows through the normal mutation path.

Result columns: table_name, duplicate_group_count, rows_updated, rows_deleted, merge_conflict_count, merge_conflicts.

MERGE DUPLICATES Customers ON Email COLLATE NOCASE;
                

CREATE VALIDATION RULE

CREATE VALIDATION RULE rule_name
                ON table_name[.column_name]
                AS expression
                MESSAGE 'message text';
                

Validation rules are stored as database metadata and evaluated only when VALIDATE TABLE is executed. V1 rules are audit-only; they do not block INSERT or UPDATE.

Rules are stored in the hidden internal table __validation_rules. That table is hidden from normal table/object listings and exposed through sys.validation_rules and sys_validation_rules.

CREATE VALIDATION RULE ValidEmail
                ON Customers.Email
                AS Email LIKE '%@%'
                MESSAGE 'Email must contain @';

                SELECT rule_name, table_name, column_name, expression_sql, message
                FROM sys.validation_rules;
                

VALIDATE TABLE

VALIDATE TABLE table_name;
                

Evaluates enabled validation rules for the table and returns one row per violation. A rule fails when the expression returns false or NULL.

Result columns: rule_name, table_name, column_name, rowid, primary_key, message.

FIND ORPHANS

FIND ORPHANS IN child_table;
                FIND ORPHANS IN child_table.child_column REFERENCES parent_table.parent_column;
                

Without an explicit REFERENCES clause, CSharpDB uses declared foreign-key metadata for the child table. With explicit references, both tables and columns are validated before running the check. NULL child values are ignored.

Result columns: constraint_name, child_table, child_column, child_rowid, child_value, parent_table, parent_column.

FIND ORPHANS IN Bookings;
                FIND ORPHANS IN Bookings.BookId REFERENCES Books.Id;
                

Performance is proportional to the requested hygiene work: duplicate detection scans the target table and groups keys in memory; validation is table rows times enabled rules; orphan detection uses parent index lookups when available or a parent value set built from one scan.

Common Table Expressions (CTEs)

WITH cte_name [(column1, column2, ...)] AS (
                    select_statement
                )
                [, another_cte AS (...)]
                SELECT ... FROM cte_name ...;
                

Multiple CTEs can be chained with commas. Optional column name lists rename the CTE's output columns.

Note: The RECURSIVE keyword is parsed but recursive CTE execution is not yet implemented.

Set Operations

select_statement UNION     select_statement
                select_statement UNION ALL select_statement
                select_statement INTERSECT select_statement
                select_statement EXCEPT    select_statement
                

UNION removes duplicates while UNION ALL preserves duplicates and NULL rows. Compound queries support trailing ORDER BY, LIMIT, and OFFSET applied to the combined result.

Expressions and Operators

Arithmetic

Comparison

Logical

Comparisons with NULL produce UNKNOWN (represented as NULL). AND, OR, and NOT apply SQL three-valued truth tables.

Special Expressions

LIKE wildcards:

Functions

Aggregate Functions

Used with or without GROUP BY. All except COUNT(*) ignore NULL values.

SELECT COUNT(DISTINCT status), AVG(age) FROM users;
                

Scalar Functions

The three ordinal search functions return 1 for true, 0 for false, and NULL when either argument is NULL. They use .NET ordinal UTF-16 code-unit semantics and do not apply collation, wildcard, or escape rules.

Parameters

Named parameters are supported in value positions using the @ prefix:

SELECT * FROM users WHERE name = @name AND age > @minAge;
                INSERT INTO users (name, age) VALUES (@name, @age);
                UPDATE users SET name = @name WHERE id = @id;
                DELETE FROM users WHERE id = @id;
                

Parameters cannot be used in identifier positions (table names, column names).

Collations

Collations control how TEXT values are compared and sorted. They can be specified at the column level in CREATE TABLE or at the expression level using the COLLATE operator.

-- Column-level collation
                CREATE TABLE products (
                    name TEXT COLLATE NOCASE
                );
                
                -- Expression-level collation
                SELECT * FROM products ORDER BY name COLLATE NOCASE_AI;
                

Limitations

Window functions are available as an experimental in-memory slice: one compatible specification per SELECT with ROW_NUMBER, RANK, DENSE_RANK, and non-distinct COUNT, SUM, AVG, MIN, or MAX. Star and mixed-expression projections preserve SELECT-list order without exposing internal window slots, and long sort/evaluation loops poll cancellation. Named windows, explicit frames, navigation functions, mixed grouping, incompatible specifications, and disk spill are not yet supported.

Built-in scalar and aggregate metadata is queryable through sys.functions (also sys_functions). Logical primary/unique key columns are exposed through sys.key_constraints. The SQL overview lists the currently registered built-in families.

The following SQL features are not currently supported:

• CASE / WHEN expressions
• CAST expressions (implicit coercion only)
• RETURNING clause on INSERT/UPDATE/DELETE
• UPSERT / ON CONFLICT / INSERT OR REPLACE
• Recursive CTE execution (WITH RECURSIVE is parsed but not evaluated)
• Unregistered vendor-specific functions such as STRFTIME, CEIL, and POWER
• Advanced window forms (LAG, LEAD, named windows, and explicit frames)
• Stored procedures
• ON UPDATE foreign-key actions, deferred constraints, and MATCH FULL/MATCH PARTIAL

Current DEFAULT support is limited to typed literals and NULL. CHECK expressions must be deterministic and row-local; parameters, functions, subqueries, qualified references, and cross-column references from a column-scoped CHECK are rejected.