Compatibility

witsqlcompatibilityportingsqlitereference

Differences are found by running the same statements against real database servers and comparing what comes back, rather than by reading a specification. What follows is what that turns up.

Most people arrive here through EF Core, and that path is much shorter, so it comes first. The sections after it are for porting SQL by hand.

What travels without changing

Ordinary work moves unedited. CREATE TABLE with constraints, INSERT, UPDATE, DELETE, SELECT with joins and grouping, CTEs, window functions, MERGE, savepoints, isolation levels. A schema written for a full server usually lands here as it is.

Several dialects are accepted deliberately, so a query written for one of them runs:

Written for Accepted here
SQL Server TOP n, CROSS APPLY, OUTER APPLY, DATETIME2, UNIQUEIDENTIFIER
MySQL LIMIT offset, count
PostgreSQL LIMIT n OFFSET m, LATERAL, ON CONFLICT, RETURNING, NEXTVAL
SQLite INSERT OR IGNORE, INSERT OR REPLACE, STRFTIME, LAST_INSERT_ROWID

If you are porting through EF Core

Most of the section above stops applying, because you are not writing the schema. Change the Use… call, generate a migration, and the provider emits the DDL: identifiers get quoted, so a column named Text needs nothing from you, there is no dbo. or public. to remove, and LINQ is translated into this dialect rather than the one you remember.

What remains splits in two.

Differences the migration will tell you about

These surface at migrations add or at database update, as an exception or as an operation that does not generate.

Four migration operations are refused, with an exception naming the table and the way around it: adding a primary key to an existing table, dropping one, renaming an index, and changing a column's type. All four mean rebuilding the table, and the migration has to say so explicitly.

That last one is the difference this scenario actually has. Porting SQL by hand, you write CREATE TABLE once. Through EF Core the migrations accumulate, and sooner or later one of them changes a column's type, which is an ordinary edit to a model. Here it stops database update, and the migration needs rewriting as a rebuild: create the new table, copy the rows, drop the old one, rename.

Two entity types differing only in case collapse into one table, since identifiers are case-insensitive here.

A composite key containing a store-generated property is refused when the model is built, naming the entity and the key. Value generation is tied to the row counter, which can only stand behind a single-column key.

Spatial types are not supported, so NetTopologySuite has nothing to map onto.

Differences the migration will not tell you about

These apply cleanly and go wrong later, which makes them the ones to look for on purpose.

HasFilter reaches the SQL and the index is created, and the planner will never choose it. You pay for its maintenance on every write and get nothing back unless you name it. See indexing.

NOW() is UTC, and so is the DATETIME DEFAULT a migration emits. If the database you came from defaulted to local time, every row written from now on is offset by your machine's timezone and nothing reports it.

Joins are slower, and the gap grows with each table. A LINQ query with several Includes against a wide model is where you will meet it. AsSplitQuery() is often the answer here, since several small queries suit this engine better than one large join.

One process holds the file. A model that assumed a shared server behind several workers needs rethinking; see choosing an API.

Stored procedures do not come across. EF Core will not scaffold them into a migration, and anything you move by hand has to fit inside what a routine may contain here, which is considerably less than SQL Server allows. See routines.

Worth doing once, early

Generate the initial migration against the real model, apply it to an empty database, and read the result:

sql
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Orders';
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
SELECT * FROM INFORMATION_SCHEMA.INDEXES;

That finds the model differences in an afternoon rather than in the release where they matter. Then run the slowest handful of your real queries under EXPLAIN, which finds the runtime ones.

Coming from SQLite

The spelling is closest here, and three things still differ.

Some keywords cannot be used as bare column names, most of them type names: Text, Int, Decimal. SQLite accepts them unquoted. Quote them, and they work. The set is smaller than it was and keeps shrinking as cases are reported.

Types are real. SQLite has type affinity and will store a string in an integer column. Here a column has a type and values are converted or refused. That is usually what you wanted; it is occasionally a surprise on a schema that relied on affinity.

An unknown type name still becomes text, as it does in SQLite, so a typo in a type name is accepted silently. See types.

Coming from PostgreSQL

One namespace. There are no schemas, so public.users does not parse. Drop the qualifier.

No arrays, no hstore, no custom types. JSON and JSONB both exist and are the same type here.

No set-returning functions, and no table-valued functions of your own; a user-defined function returns a scalar. See routines.

LATERAL works in SQL and not through EF Core. The engine has it, and the provider refuses to translate a correlated Take rather than emitting SQL it cannot read.

Sequences are read with NEXTVAL and CURRVAL as you would expect, and there is no serial shorthand: use BIGINT PRIMARY KEY AUTOINCREMENT.

Case folding differs. PostgreSQL folds unquoted identifiers to lower case and treats quoted ones as case-sensitive. Here identifiers are case-insensitive throughout, quoted or not, so "Users" and users are the same table. A schema that relied on two tables differing only in case will not port, and nothing else will notice.

Coming from SQL Server

No schemas, so dbo. comes off everything.

No IDENTITY. Use PRIMARY KEY AUTOINCREMENT, on a single-column key.

No MERGE ... OUTPUT, no OUTPUT clause at all. RETURNING covers the same ground on INSERT, UPDATE and DELETE.

Stored procedures are much smaller here. No IF, no WHILE, no local variables, no OUT parameters, no multiple result sets. A procedure body is a sequence of statements and the last statement's result is the result. Procedural logic has to move into the application. See routines.

SET TRANSACTION ISOLATION LEVEL works and takes the same five levels.

Things to check whichever you came from

DDL is not transactional. PostgreSQL rolls back a CREATE TABLE; this engine does not. A migration framework that relies on wrapping a schema change in a transaction to make it atomic gets no such thing here.

Isolation above READ COMMITTED is optimistic. PostgreSQL and SQL Server block; here nothing blocks and the transaction fails at commit instead. Code ported from either will have no retry path in it. SERIALIZABLE also permits write skew, which PostgreSQL's serializable does not.

Joins are slower here, and the gap grows with each table added to a query. A report built on a five-table join is the thing to measure first. See limitations.

Partial indexes are maintained and never chosen. A CREATE INDEX ... WHERE that your old database relied on costs writes here and buys nothing. Replace it with a full index on the same column. See indexing.

NOW() is UTC. So are CURRENT_DATE and CURRENT_TIME. If your schema stored local time by default, the LOCAL* functions are there, and moving to UTC is usually the better answer.

A DEFAULT that calls a function has to name a function this engine has, and is refused when written rather than when a row arrives. That is stricter than most and catches the porting error early.

What has no equivalent

Spatial types and functions. Full-text search. Materialised views. Table-valued functions. Foreign data wrappers, linked servers, and anything else that reaches outside the file.

Checking for yourself

INFORMATION_SCHEMA is queryable, so the fastest way to find out whether a schema arrived intact is to ask:

sql
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Orders';
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
SELECT * FROM INFORMATION_SCHEMA.INDEXES;

And EXPLAIN says what a ported query actually does here, which is worth running on anything that used to be fast elsewhere.

Where to go next