WitSQL

witsqlsqlreferencedialect

WitSQL is the SQL WitDatabase accepts. This section is the reference for it, and it is the only place on this site that explains syntax; everything else links here.

What it covers

Area Statements
Types The type system, aliases, and what a value becomes at runtime
DDL CREATE, ALTER and DROP for tables, indexes, views, triggers and sequences
DML INSERT, UPDATE, DELETE, MERGE, RETURNING
Queries SELECT, joins, CTEs, window functions, set operations
Functions Strings, numbers, dates, JSON, aggregates and the rest
Routines CREATE FUNCTION, CREATE PROCEDURE, CALL
Transactions BEGIN, COMMIT, savepoints, isolation, locking hints
Compatibility Where the dialect differs from the servers it stands in for
INFORMATION_SCHEMA Reading a schema with SQL

The shape of the dialect

The spelling follows SQLite, because that is the spelling most .NET developers have seen in an embedded database, and because it keeps a query portable between the two.

The coverage aims somewhere else. WitDatabase exists to stand in for a full database server, so the question this dialect has to answer is whether a statement written for one keeps working here. That is why stored procedures, user-defined functions, MERGE, window functions, INFORMATION_SCHEMA and five isolation levels are present in an embedded engine, none of which the comparison with SQLite would have called for.

Differences are found by running the same statements against real servers and comparing the answers, rather than by reading a specification. What that turns up is on compatibility.

Conventions

Keywords are shown in upper case and are not case-sensitive. SELECT and select are the same.

Identifiers are case-insensitive and are matched that way, so Users and users name the same table. Quote an identifier with double quotes or square brackets when it collides with a keyword:

sql
SELECT "Text", [Order] FROM Messages;

Some keywords cannot stand as bare column names, most of them type names, and quoting them works. The set shrinks as each one is found: Key, Top, Apply, Lateral, Function, Procedure, Call, Language, Returns and Return all parse as column names now, and ROWID and LEVEL always did. See limitations.

Parameters come in five spellings, so a statement written for another database usually runs:

sql
SELECT * FROM Users WHERE Id = @id;     -- named, and what this documentation uses
SELECT * FROM Users WHERE Id = :id;     -- named, colon
SELECT * FROM Users WHERE Id = $id;     -- named, dollar
SELECT * FROM Users WHERE Id = ?;       -- positional
SELECT * FROM Users WHERE Id = ?1;      -- numbered

Statements are separated by semicolons. Comments are -- to end of line and /* ... */ across lines.

What is not here

No stored program flow: the body of a procedure is a sequence of statements, and there is no IF, no WHILE and no local variables. See routines for what a routine body can contain.

No schemas in the SQL Server sense. There is one namespace, and dbo.Users will not parse.

No spatial types, no full-text search.

Where to go next

  • Types, the place to start if you are writing DDL
  • Queries, if you are writing a select
  • Compatibility, if you are porting from another database