SQL Reference

Complete reference for the SQL dialect supported by CSharpDB.

Data Types

CSharpDB supports 25 declared logical type kinds plus the generated ROWVERSION column declaration. Canonical declarations are returned by typed schema metadata; aliases parse to the same logical descriptor.

NULL is a value and runtime tag, not a declarable SQL column type. Ordinary types may be nullable unless constrained with NOT NULL; rowversion is always generated and non-nullable.

Facet rules

• Length n must be positive. Character lengths count Unicode scalar values, binary lengths count bytes, and bit-string lengths count bits.
• DECIMAL defaults to DECIMAL(18,2); DECIMAL(p) means DECIMAL(p,0). Assignment is exact and does not round excess fractional digits.
• Fractional-seconds precision p ranges from 0 through 7 for TIME, DATETIME2, DATETIMEOFFSET, and INTERVAL DAY TO SECOND. DATETIME(p) is rejected; use DATETIME2(p).
• TIMESTAMP(p) WITH TIME ZONE is temporal. Bare TIMESTAMP is a rowversion; TIMESTAMP(p) without WITH TIME ZONE is rejected.

Logical and physical types

Declared SQL types share six compact runtime tags—Null, Integer, Real, Decimal, Text, and Blob—without sharing logical rules. Boolean and integer widths use the Integer carrier; REAL and DOUBLE PRECISION use Real; character, temporal, interval, JSON, and XML use Text; binary, UUID, bit strings, and rowversion use Blob; exact decimal uses Decimal. Typed metadata and APIs preserve the logical descriptor.

INTEGER arithmetic is checked and remains INTEGER; an operation involving BIGINT produces BIGINT, while TINYINT and SMALLINT arithmetic widens to BIGINT. Small integer literals infer as INTEGER and larger literals as BIGINT. Boolean values are not ordinary numeric operands.

ROWVERSION and bare TIMESTAMP are generated column declarations, not cast or ALTER COLUMN TYPE targets. A table may contain one rowversion. It cannot be assigned, nullable, defaulted, collated, used as an identity, or included in a key, foreign key, or index. The legacy BLOB ROWVERSION NOT NULL declaration remains accepted.

Identifiers

SQL identifiers are limited to 128 UTF-16 code units 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)
                                         [MATCH SIMPLE]
                                         [ON DELETE RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT]
                                         [ON UPDATE RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT]],
                    ...,
                    [[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, ...])
                        [MATCH SIMPLE]
                        [ON DELETE RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT]
                        [ON UPDATE RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT]]
                );
                

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 [, ...]) [MATCH SIMPLE] [ON DELETE RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT] [ON UPDATE RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT];
                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 TYPE TEXT;
                ALTER TABLE table_name ALTER COLUMN column_name TYPE BLOB;
                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. Complete ready full-text families are supported too: all five owned metadata, term, posting, posting-chunk, and document-statistics stores are rebuilt from the new row IDs in the same atomic swap, while the logical full-text owner and its options remain unchanged. Collection indexes, incomplete or non-ready full-text families, other index kinds, 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.

Numeric type rewrites are bounded and exact. BIGINT-to-REAL accepts only values in the inclusive range -2⁵³ through 2⁵³; every INTEGER value is exactly representable. REAL-to-INTEGER accepts only finite, integral signed 32-bit values, while REAL-to-BIGINT uses the signed 64-bit range. NULL remains NULL. Affected ready ordinary and unique SQL indexes, including composite indexes, are rebuilt and swapped atomically with the table; unrelated index roots remain unchanged. An incompatible stored value, default, or uniqueness result rejects the operation and restores every original root.

TYPE BLOB on a TEXT column encodes each non-NULL value as UTF-8 and clears its TEXT collation metadata. TYPE TEXT on a BLOB column decodes strict UTF-8; any invalid byte sequence rejects the rewrite without changing the table. The resulting TEXT column starts with the default BINARY collation unless a later SET COLLATION selects another supported collation. These TEXT/BLOB changes currently require a dependency-free column, so even a ready SQL index on the column blocks the rewrite.

Defaults are validated against the target type rather than converted automatically. Use DROP DEFAULT before TYPE and SET DEFAULT afterward when the old literal is not valid for the target. EF Core emits that order for both Up and Down migrations, applies a requested BLOB-to-TEXT collation after the type change, and lets TYPE BLOB clear an old TEXT collation. EF migrations run the compound default/type/collation/nullability sequence in their surrounding migration transaction; manual replay must do the same so a later failure restores every facet.

SET COLLATION and DROP COLLATION are limited to TEXT columns. Type and 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 TEXT/BLOB changes and broader dependency rewriting 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, ...]);
                

SQL indexes accept INTEGER, TEXT, and REAL columns. REAL components use a canonical hashed equality path: equality predicates can use the index, including exact INTEGER/REAL numeric matches, but ordered scans, range pushdown, and using a REAL index to satisfy ORDER BY are not supported. An INTEGER-tag value stored in a REAL-indexed column must be within the exactly representable ±2⁵³ range; index creation, rewrite backfill, or a later write rejects an out-of-range value instead of rounding it. COLLATE remains valid only for TEXT index columns.

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]
                BEGIN
                    statement1;
                    [statement2;]
                    ...
                END;
                

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

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

Unsupported: Trigger WHEN conditions are rejected with a stable SyntaxError before trigger metadata is persisted. Put supported predicate logic in the trigger body until conditional triggers are implemented.

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.

EXPLAIN and EXPLAIN ANALYZE

EXPLAIN [FOR] SELECT ...;
                EXPLAIN [FOR] INSERT ...;
                EXPLAIN [FOR] UPDATE ...;
                EXPLAIN [FOR] DELETE ...;

                EXPLAIN ANALYZE [FOR] SELECT ...;
                EXPLAIN ANALYZE [FOR] INSERT ...;
                EXPLAIN ANALYZE [FOR] UPDATE ...;
                EXPLAIN ANALYZE [FOR] DELETE ...;

                EXPLAIN ESTIMATE FOR SELECT ...; -- legacy cardinality diagnostic
                

EXPLAIN binds the statement and returns the finalized physical operator structure without opening it. EXPLAIN ANALYZE executes the statement exactly once and adds per-operator actual rows, open loops, and inclusive elapsed time. Profiled DML performs the insert, update, or delete and uses the same auto-commit or explicit-transaction semantics as normal execution; rolling back the surrounding transaction rolls back the profiled mutation.

The physical plan is a stable structural rowset rather than formatted prose. It is available through direct execution, ADO.NET, HTTP, and gRPC using the normal SQL result path.

Plan output is bounded to 500 rows and a 256 KiB inline content budget, with text fields limited to 512 characters. The content budget limits the plan data before transport framing and serialization; it is not an exact HTTP, gRPC, or other serialized message-size guarantee. If a bound is reached, the final row is a diagnostic node with plan_truncated. Literal and prepared-parameter values are represented by placeholders in predicate metadata, including bounded partial-profile summaries attached to failures where safe.

To guarantee that ordinary EXPLAIN never triggers eager work, statements whose current planning path materializes data are rejected with a stable diagnostic. This currently includes WITH, subqueries, views, and duplicate-eliminating compound queries. Use EXPLAIN ANALYZE when those statements should be executed and profiled. Duplicate-preserving UNION ALL can be explained without execution.

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
                SELECT id FROM users WHERE id IN (SELECT id FROM other_table);
                SELECT id FROM users WHERE id NOT IN (SELECT id FROM other_table);
                
                -- EXISTS subquery
                SELECT id FROM users
                WHERE EXISTS (SELECT 1 FROM other_table WHERE other_table.id = users.id);
                

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: WITH RECURSIVE is rejected because 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.

XML_EXISTS and XML_VALUE accept an XML or text document and an XPath 1.0 expression. Their optional third argument is a JSON object mapping XPath prefixes to namespace URIs, for example XML_VALUE(payload, '/o:order/@id', '{"o":"urn:orders"}'). Every supplied argument is NULL-propagating.

When XML_VALUE evaluates to a node-set, no matching nodes returns NULL, exactly one node returns its XPath string value, and more than one matching node reports a type mismatch. XPath scalar results use XPath string conversion. Use an explicitly singular expression such as (/root/item)[1] when appropriate.

XML documents are parsed with bounded, secure settings; DTD declarations and external entities are prohibited. The supported syntax is the function-style API above. Standard SQL XMLEXISTS(... PASSING ...), XML_TABLE, and XML path indexes are not currently supported.

Window Functions

Window functions are a completed, bounded in-memory SQL slice. Supported ranking functions are ROW_NUMBER(), RANK(), and DENSE_RANK(). Supported aggregate windows are non-distinct COUNT, SUM, AVG, MIN, and MAX. Navigation and value functions include LAG(value[, offset[, default]]), LEAD(value[, offset[, default]]), FIRST_VALUE(value), and LAST_VALUE(value).

An explicit ROWS frame may use either ROWS frame_bound (ending at CURRENT ROW) or ROWS BETWEEN frame_bound AND frame_bound. A bound may be UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, or UNBOUNDED FOLLOWING; offset n must be a nonnegative integer literal, and invalid start/end combinations are rejected. Ranking functions and LAG/LEAD ignore the frame; aggregate windows and FIRST_VALUE/LAST_VALUE evaluate against it. Without an explicit frame, an ordered window uses the peer-aware default through the current peer group, while a window without ORDER BY uses the whole partition.

SELECT
                    department,
                    salary,
                    ROW_NUMBER() OVER (
                        PARTITION BY department
                        ORDER BY salary DESC
                    ) AS position,
                    AVG(salary) OVER (
                        PARTITION BY department
                        ORDER BY salary DESC
                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
                    ) AS moving_average,
                    LAG(salary, 1, 0) OVER pay_order AS previous_salary
                FROM employees
                WINDOW pay_order AS (
                    PARTITION BY department
                    ORDER BY salary DESC
                );
                

Named definitions use WINDOW name AS (...) and are referenced with OVER name; names are matched case-insensitively. Window inheritance and extensions such as OVER (name ...) are not supported. Expressions whose resolved windows have exactly the same PARTITION BY and ORDER BY specifications can share one execution stage even when their frames differ. Incompatible partitioning or ordering specifications are rejected explicitly instead of sharing an incorrect sort order.

Ascending window order places NULL values first and descending order places them last. Explicit NULLS FIRST/NULLS LAST syntax is not supported. Window queries support prepared execution and poll cancellation during buffering, sorting, and evaluation. See WindowExecution for the finite partition and stage-buffer limits; exceeding either limit returns ResourceLimitExceeded.

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 execution is deliberately in-memory and bounded. Disk spilling is deferred and does not block completion of this SQL slice because configured limits fail predictably with ResourceLimitExceeded. RANGE, GROUPS, EXCLUDE, DISTINCT window aggregates, IGNORE NULLS/RESPECT NULLS, window inheritance, incompatible window specifications, and window queries mixed with ordinary aggregates, GROUP BY, HAVING, or subqueries are not 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
• INTERSECT ALL and EXCEPT ALL (only UNION ALL is supported)
• Recursive CTE execution (WITH RECURSIVE is rejected)
• Unregistered vendor-specific functions such as STRFTIME, CEIL, and POWER
• Advanced window forms beyond the bounded slice described above
• SQL CREATE PROCEDURE and CALL statements; use the supported client procedure catalog/API instead
• Deferred foreign-key constraints and MATCH FULL/MATCH PARTIAL
• Trigger WHEN conditions

Current DEFAULT support is limited to typed literals and NULL. Immediate referential mutations use the normal UPDATE pipeline, including rowversion, triggers, checks, other foreign keys, unique indexes, and transactional rollback. Under MATCH SIMPLE, a SET DEFAULT result with any NULL component satisfies the foreign key; a fully non-NULL result without a matching parent fails without partial changes. CHECK expressions must be deterministic and row-local; parameters, functions, subqueries, qualified references, and cross-column references from a column-scoped CHECK are rejected.