Sorted by how likely you are to hit it in your first week.
The database will not open
A second process has it. One process holds a database file, and a second is refused. This is usually a running application, a debugger session you forgot, or Studio. Close whichever one you did not mean. See choosing an API.
The password is wrong. The error says so and names the password rather than reporting a decryption failure from somewhere deeper. Salt and iteration count live in the file, so no connection-string setting can cause this. See encryption.
The format is newer than the build. A database written by a later engine is refused rather than read wrongly. Upgrade the application, or go back to the copy you took before the upgrade. See file format.
The file was written by a process that died. Pages reach the disk by eviction with nothing ordering them against the header, so an abrupt end can leave a database that will not reopen. There is no repair for this; restore a copy. See limitations.
A second process is starting while the first is shutting down. The wait for the file lock is
bounded rather than instant, Connection Timeout seconds by default, precisely so that restarting a
host does not fail on the overlap. If it still fails, something is genuinely holding the file.
The connection string is refused
Parallel Mode or Max Writers. Both were removed and are rejected with advice rather than
ignored. Delete the keyword. See connection strings.
A setting disagrees with the database. A database records what it was built with, and opening
it with a different Store, Cache or Journal is refused rather than silently giving you
something else. Leave the keyword out and the file supplies it.
Encryption with no Password. Refused, because it would produce an unencrypted database that
looks encrypted in a config file.
A statement will not parse
A column named after a keyword. Some keywords cannot stand unquoted as column names, most of
them type names: Text, Int, Decimal. Quote the identifier with double quotes or square
brackets. See limitations.
SET NEW.column = ... in a trigger. Assigning to NEW does not parse. Set the value in the
statement, or use a computed column. See DDL.
IF, WHILE or a local variable in a routine. None of them exist. A function body is one
expression and a procedure body is a sequence of statements. See routines.
A schema-qualified name. There is one namespace, so dbo.Users and public.users do not
parse. Drop the qualifier.
A statement fails at run time
A commit throws a concurrency exception. Under MVCC a conflict is found at commit, not at the write. Something you touched moved since your snapshot. Read the current state and decide again rather than retrying the same write. See transactions.
A migration stops. Four operations are refused with a message naming the table: adding or dropping a primary key on an existing table, renaming an index, and changing a column's type. All four mean rebuilding the table. See the EF Core provider.
A schema change half happened. DDL is not rolled back, so a script that failed on its fourth statement has applied its first three. Read what is there rather than assuming the transaction undid it.
ADD COLUMN ... NOT NULL is refused. The table has rows, and they would all get NULL in a
column that forbids it. Give the column a DEFAULT.
Statement nesting exceeded. Statements nest at most 32 deep, usually reached by a trigger that writes to its own table. It is a catchable error rather than a crash.
An unknown function. A function name the engine does not have is refused when the statement is
written, including inside a CHECK, a DEFAULT or an index expression. Check the spelling against
functions.
Something is slower than expected
Run EXPLAIN first. It says whether an index was chosen and which, which answers most of these
before anything else is tried.
A partial index. CREATE INDEX ... WHERE is maintained and never chosen, so it costs you writes
and answers no query. Replace it. See indexing.
A join over several tables. Only an INNER or LEFT equi-join can get a hash join; the rest
are nested loops, and the cost grows with each table. Check EXPLAIN to see which you got. Through
EF Core, AsSplitQuery() often helps. See benchmarks.
A large write outside a transaction. One commit per statement flushes once per statement. Wrap it, which is also what makes it atomic against a process that dies.
Store=lsm with MVCC on. Multi-version concurrency control costs an order of magnitude more
over the LSM store than over the B+Tree store, and MVCC is the default. See
storage engines.
A missing index on a foreign key. A foreign key constraint does not create one. This is the most common cause of a query that was fast in development.
Values come back wrong
A column is text when it should not be. An unrecognised type name is stored as text rather than
refused, so a typo in CREATE TABLE produces a text column silently. Check the column in
INFORMATION_SCHEMA.
Times are offset by your timezone. NOW(), CURRENT_DATE and CURRENT_TIME are UTC, including
in a DEFAULT. The LOCAL* functions are the local equivalents. See
functions.
VERSION() says 1.0.0. It is a hardcoded string and does not report the engine version.
Where to go next
- Limitations, the full list of what the engine does not do
- Compatibility, if you are porting from another database
- Studio, for looking at a database rather than reasoning about it
- Issues, if what you are holding is none of the above