# SQL Server

SQL Server in Ptah - connection URLs, the supported T-SQL subset, collation-aware identifier comparison, and filtered indexes.

Source: https://docs.ptah.run/edge/databases/sqlserver/

Ptah supports SQL Server and Azure SQL as a deliberately conservative portable
subset under the canonical dialect name `sqlserver` (the aliases `mssql`,
`sql-server`, `sql_server`, and `tsql` normalize to it). This page covers the
connection URL, what the subset includes, and the two behaviors that most
often surprise SQL Server users: collation-aware identifier comparison and
filtered-index predicate spelling.
Commands that introspect this behavior require a live SQL Server database URL.

## Connecting

Pass a canonical `sqlserver://` URL; `mssql://` input is accepted and
normalized:

```bash
ptah db read --db-url "sqlserver://sa:$SA_PASSWORD@localhost:1433?database=app&encrypt=disable"
```

The Ptah-only `schema` query parameter selects the default schema for
introspection, migration metadata, and write helpers; it is removed before the
URL reaches the driver, and it defaults to `dbo`:

```bash
ptah db read --db-url "sqlserver://sa:$SA_PASSWORD@localhost:1433?database=app&schema=audit&encrypt=disable"
```

`--migrations-schema` still takes precedence for migration metadata placement.

## Supported surface

- T-SQL rendering with bracket-quoted identifiers and schema-qualified names
  such as `dbo.users`.
- `IDENTITY(start,increment)` for auto-increment columns and
  `NVARCHAR`/`NVARCHAR(MAX)` string mapping. A column's identity is compared;
  a change to it is refused by name, because `ALTER COLUMN` has no `IDENTITY`.
  Create the column, or the table, again with the identity it should have.
  `SERIAL`, `BIGSERIAL` and `SMALLSERIAL` are written as `INT`, `BIGINT` and
  `SMALLINT` with `IDENTITY(1,1)`, unless the field gives its own SQL Server
  type. A table takes one identity column (`Msg 2744`), so a serial column
  beside another auto-increment column is refused by name.
- Core table DDL: primary keys, unique constraints, foreign keys, `CHECK`
  constraints, and indexes with ordered ascending or descending key columns.
- Filtered indexes (see below).
- Views and triggers rendered from raw SQL definitions.
- Synonyms: declared, rendered, introspected from `sys.synonyms`, and diffed,
  including targets in another database or behind a linked server (see below).
- Extended properties at database, schema, table and column scope: declared,
  rendered, introspected from `sys.extended_properties`, and diffed (see
  below).
- Schemas the declared objects live in, created before them (see below).
- Scalar functions and procedures, with the principal they run as and the
  other options of their header (see below).
- Live introspection from `sys.tables`, `sys.columns`, `sys.indexes`, and
  related catalog views.
- Transactional migration apply for DDL SQL Server supports in transactions,
  and migration-run serialization through a session application lock.
- Transactional dev-database cleanup across supported user schemas, with a
  preflight that rejects replication-enabled databases and replicated tables.
- One-row upsert rendering to `MERGE` through the Go DML AST (an embedder
  surface, not a CLI command).

SQL Server has no native enum object, so enum annotations render as
`NVARCHAR(255)` columns with a generated `CHECK` constraint:

```sql
[status] NVARCHAR(255) NOT NULL CHECK ([status] IN ('active', 'blocked'))
```

## Synonyms

A synonym is an alias for another object, and it is the one schema object whose
target may live somewhere Ptah does not manage:

```go
//ptah:schema:synonym name="current_orders" schema="app" target="sales.orders"
type CurrentOrdersSynonym struct{}
```

```sql
CREATE SYNONYM [app].[current_orders] FOR [sales].[orders];
```

The target is written with as many parts as it needs. Two parts name an object
in this database; three name another database; four name a linked server. Ptah
records what it was given and never rewrites it, because the part count is what
tells SQL Server where to resolve the name, and adding a qualifier would turn a
remote reference into a local one.

**Ptah manages the alias and never the target.** A synonym pointing outside this
database is a supported declaration rather than an error — SQL Server does not
require the target to exist either — so nothing tries to create or drop the
object it points at. A plan creates the alias after the tables, views and
routines it creates, and drops it before any of them is dropped. A target
behind a linked server with no database part, `srv..dbo.orders`, keeps its
empty part.

A changed target is planned as a drop and a create in that order, because T-SQL
has no `ALTER SYNONYM` and `CREATE SYNONYM` refuses a name that already exists.

Targets are compared with the server's own bracket quoting normalized away, so a
declared `dbo.orders` and a stored `[dbo].[orders]` are the same target rather
than a difference reported on every run.

A synonym belongs to the synonym owner and is bound to SQL Server and Oracle,
the two targets that have one. A schema rendered or planned for any other
target leaves it out. Two declarations of one synonym are refused.

Go export preserves the alias, schema, target spelling, and comment, including
schemas that declare only synonyms. The annotation binds each alias to SQL
Server and Oracle. An explicit scope that names a different set is refused.

## Extended properties

Go export preserves database, schema, table, and column properties, including
empty string values and declaration comments. The `value` attribute is required;
`value=""` is valid. Export refuses `MS_Description` as an extended property
because Ptah represents it as the object's comment. Synonym and property export
refuse incomplete namespace or object coverage so an unread object cannot
become a planned removal through the exported file.

An extended property is a named value attached to a schema, a table, or a
column of one:

```go
//ptah:schema:extendedproperty name="retention" schema="app" table="orders" value="90d"
type OrdersRetentionProperty struct{}

//ptah:schema:extendedproperty name="classification" schema="app" table="orders" column="email" value="pii"
type OrdersEmailClassification struct{}
```

```sql
EXEC sp_addextendedproperty @name = N'retention', @value = N'90d', @level0type = N'SCHEMA', @level0name = N'app', @level1type = N'TABLE', @level1name = N'orders';
EXEC sp_addextendedproperty @name = N'classification', @value = N'pii', @level0type = N'SCHEMA', @level0name = N'app', @level1type = N'TABLE', @level1name = N'orders', @level2type = N'COLUMN', @level2name = N'email';
```

**The address is the identity.** SQL Server stores a property under a class and
up to two ids, so the same name on the database, on a schema, on a table of it,
and on a column of that table is four different properties. Which levels a
declaration sets decides its scope: **no level at all** is the database's own
property, `schema` alone is schema scope, adding `table` addresses the table,
and adding `column` addresses a column of it. A level without the one above it
is refused, because SQL Server has no level N without a level N-1.

```go
//ptah:schema:extendedproperty name="deployment_tier" value="production"
type DeploymentTierProperty struct{}
```

```sql
EXEC sp_addextendedproperty @name = N'deployment_tier', @value = N'production';
```

A database-scoped property passes no level, and that is not an omission: an
empty `@level0name` would be a property on a schema called `""`, which the
procedure also accepts and which belongs to nothing. It is in no schema either,
so `--schema` neither selects nor removes it — the same rule an extension
follows, where placement is not ownership.

`--include` and `--exclude` name a property as `name[type=extended_property]`,
qualified by its schema, or by its name alone for a property on the database.
A property on a table or a column follows its table: a scope keeps or leaves it
with the table, on the declaration and on the database alike.

A changed value plans `sp_updateextendedproperty` rather than a drop and an
add, so the property is never absent partway through a script. A `DROP TABLE`
in a SQL schema file removes the properties on the table and its columns, as
SQL Server drops them with the table, and a scope that leaves a table out
leaves its properties out. Properties are
emitted after every object one can hang off, and dropped before the objects
they belong to.

`MS_Description` is refused by name. Ptah already models it as the object's
comment — the reader turns it into one and the renderer writes it as one — and
accepting it here as well would give one live value two owners, each planning
against it without seeing the other.

**A value Ptah cannot write back is reported and left alone.**
`sp_addextendedproperty` takes a `sql_variant`, so a property may hold an `int`
or a `date` as well as a string, and Ptah writes values as `N''` literals.
Re-emitting a non-string value through one would change its stored type, and
`CONVERT(NVARCHAR, value)` on a date answers a locale-dependent rendering
rather than the value. Such a property appears in a read with its base type and
no value, and a comparison plans nothing for it in either direction.

One kind of property is outside this scope and is not read at all: one on an
object other than a table — a view, a procedure, an index — which takes a
different `@level1type` than the one Ptah writes.

Realm cleanup still refuses a database whose database- or schema-scoped
properties it would leave behind. That guard predates this support and is
unchanged: adding cleanup for them is a destructive change with its own
measurement.

An extended property belongs to the SQL Server owner. A declaration is bound
to SQL Server, so a schema rendered or planned for another target leaves the
property out. Two declarations of one property are refused.

The owner compares, plans and reverses extended properties, and the HCL that
`schema inspect` writes carries them, as it does synonyms. A plan adds or
changes a property once the table or column it is on exists, and drops it
before that object is dropped.

```hcl
synonym "current_orders" {
  schema = schema.app
  target = "sales.orders"
}

extended_property "MS_Description" {
  schema = schema.app
  table  = "orders"
  column = "total"
  value  = "order total including tax"
}
```

The owner is written as plain strings rather than as `table.orders` and
`column.total` references, because that is what the statement carries:
`@level1name = N'orders'` is a name, and a reference would resolve to nothing
once `--include` left the table out. A block with no `schema` is a
database-scoped property, the address that passes no level at all.

**What a document cannot name it does not drop.** YAML has no key for either
object, and a `.sql` document is written back with `CREATE SYNONYM` and
`sp_addextendedproperty` but read as a schema holding neither, so a file in
those formats could not have declared one — and reading its silence as intent
would make this loop destructive:

```bash
ptah schema inspect --db-url "$SQLSERVER_URL" --format sql > out.sql
ptah schema apply --db-url "$SQLSERVER_URL" --to file://out.sql
```

Neither format claims the two objects, so the comparison withholds those
removals. HCL and a Go schema — `//ptah:schema:synonym` and
`//ptah:schema:extendedproperty` — can both declare the two objects and claim
them, so silence in either is a request to remove.

## Schemas

A schema on SQL Server is an ordinary object inside the connected database, so a
declaration that puts a table in one is asking for the schema too. Ptah creates
it before the objects that need it:

```sql
IF SCHEMA_ID('app') IS NULL
    EXEC('CREATE SCHEMA [app]');
CREATE TABLE [app].[widget] (
  [id] INT PRIMARY KEY
);
```

The guarded form is not a style choice. SQL Server has no
`CREATE SCHEMA IF NOT EXISTS`, repeating the statement answers `Msg 2714: There
is already an object named 'app' in the database`, and `CREATE SCHEMA` must be
the first statement of its batch — which is what the `EXEC` is for.

The schemas come from the objects being added, so a migration that adds nothing
emits no schema statement and a single-schema declaration emits none either. A
schema a document declares and puts nothing in is not created.

**MySQL, MariaDB, ClickHouse and Oracle do not do this**, and that is a
difference in what a schema IS rather than a gap. A schema there is a database —
or, on Oracle, a user — so creating one is `CREATE DATABASE` or `CREATE USER`,
an administrative act outside what a schema migration owns.

## Identifier collation

SQL Server compares object identifiers and routine execution principals using catalog collation rules — case,
accent, locale, kana, and width sensitivity all depend on the target catalog.
Ptah does not reproduce those rules locally; the live catalog is the source of
truth. When comparing against a live database, Ptah asks SQL Server to resolve
candidate identifier equivalence under `COLLATE CATALOG_DEFAULT` and carries
that resolved snapshot through comparison, planning, checkpoint generation,
and shadow verification (shadow execution is rejected when the shadow catalog
resolves names differently).

An offline, dialect-only comparison cannot know the target collation, so it
confirms only exact-spelling identity. When it checks table, column, or index
names for conflicts, it treats two ASCII names that differ after ASCII case
folding as distinct, because no SQL Server collation makes them equal. Names that differ only in ASCII case, or
where either name has a non-ASCII character, may be one name under some
collation. Planning treats them as potential conflicts and rejects the
ambiguity instead of guessing. This makes offline SQL Server planning
intentionally stricter than live planning. Embedders choose between the live-aware and offline
comparison APIs — see [Public Go API](../../extend/public-api/).

## Filtered indexes

Declare a filtered index with the index annotation's `condition` (or `where`)
attribute:

```go
//ptah:schema:index name="idx_active_users" fields="status" condition="status = 1"
```

The predicate renders verbatim as `CREATE INDEX ... WHERE status = 1`. SQL
Server cannot alter a predicate in place, so a changed predicate plans as
`DROP INDEX` plus `CREATE INDEX ... WHERE` with the new predicate.

SQL Server stores predicates in a canonical spelling — `status = 1` comes back
as `([status]=(1))` — and Ptah normalizes bracket quoting, parenthesized
numeric literals, case, and whitespace before deciding whether an index
changed. Rewrites beyond that spelling (such as the `N'...'` prefix on Unicode
string literals) are not reconstructed, so those predicates compare as changed
on every run. If a filtered index keeps reporting drift after it was applied,
read the stored spelling with `ptah db read` and use it in the annotation; the
rendered SQL always preserves your annotation text verbatim.

## Column defaults

SQL Server keeps a column default as a constraint of its own, so `ALTER
COLUMN` cannot set or drop it. Ptah creates a default without a name, in
`CREATE TABLE` and in a migration alike, so the server names it, and the name
differs from one database to the next. A migration therefore does not write the
name down: it reads it from `sys.default_constraints` when it runs. This
statement replaces the default of `users.status`:

```sql
EXEC sp_executesql N'DECLARE @drop nvarchar(max) = (SELECT N''ALTER TABLE [dbo].[users] DROP CONSTRAINT '' + QUOTENAME(dc.name) FROM sys.default_constraints AS dc WHERE dc.parent_object_id = OBJECT_ID(N''[dbo].[users]'') AND dc.parent_column_id = COLUMNPROPERTY(dc.parent_object_id, N''status'', ''ColumnId'')); EXEC (@drop); ALTER TABLE [dbo].[users] ADD DEFAULT ''active'' FOR [status];';
```

It drops the current default, if the column has one, and adds the declared
default. It is one statement because the constraint name is held in a variable,
and a variable does not outlive its batch. A removed default is the same
statement without the `ADD DEFAULT`. The safety report treats the drop as a
default change rather than as the removal of a constraint that protects data:
a removed default is a warning, and a replaced one is safe.

SQL Server refuses to change the data type of a column that has a default, so
a plan that changes the type drops the default first and sets it again after
the `ALTER COLUMN`. It does this for a longer length of the same type too,
which the server would accept with the default in place. A nullability change
leaves the default where it is.

## Routines

A function or a procedure is written `CREATE OR ALTER`. Its header's `WITH`
list states how it runs, and `sys.sql_modules` reports each option back:

| Header | `sys.sql_modules` | Ptah |
| --- | --- | --- |
| none, or `EXECUTE AS CALLER` | `execute_as_principal_id` `NULL` | security `INVOKER` |
| `EXECUTE AS OWNER` | `execute_as_principal_id` -2 | security `DEFINER` |
| `EXECUTE AS 'auditor'` | `execute_as_principal_id` the user's id | security `DEFINER`, principal `auditor` |
| `SCHEMABINDING` | `is_schema_bound` 1 | kept |
| `RETURNS NULL ON NULL INPUT` | `null_on_null_input` 1 | kept |
| `RECOMPILE` | `is_recompiled` 1 | kept |

Parameter declarations accept the optional `AS`: `@amount AS DECIMAL(10, 2)`
and `@amount DECIMAL(10,2)` compare as the same parameter. The SQL Server
owner normalizes the list before comparison and routine-option matching.
Parameter names, order, and type dimensions remain significant.

The options Ptah keeps are read from a SQL schema file and from a connected
database, compared, and planned. A routine whose options change is replaced
with the declared `WITH` list, and the rollback writes the options it held. A
SQL schema file states every option a routine has, so a routine declared there
without `SCHEMABINDING` loses it. A Go annotation and an Atlas HCL block have
no way to state these options, so a routine declared there keeps the ones it
has. A plan that would replace such a routine, for example for a new body, is
refused, because the replacement would drop them.

`EXECUTE AS SELF` is refused when a SQL schema file is read. It means the
account that runs the `CREATE`, which can be a different user each time the
schema is applied. Write `EXECUTE AS 'name'` with that user instead.

Every other option is refused by name, because Ptah cannot write it and compare
it back: `ENCRYPTION`, `NATIVE_COMPILATION`, `INLINE` and `FOR REPLICATION`
among them. A SQL schema file that writes one is refused when it is read, and
a database that holds a routine created with `ENCRYPTION`,
`NATIVE_COMPILATION` or an explicit `INLINE` setting is refused when it is
read, naming the routine. This includes `INLINE = OFF` on a function that
cannot be inlined. `CALLED ON NULL INPUT` is the default and is accepted.

T-SQL has no volatility clause. SQL Server judges a function deterministic
from its body and `SCHEMABINDING`, and no declaration states that verdict, so
the reader reports every routine `VOLATILE`.

## Limitations

- No PostgreSQL-style extensions and no materialized views.
- Row-level security is a `SECURITY POLICY`, declared by a Go row-level
  security annotation scoped to SQL Server. Its `USING` is a filter predicate
  and its `WITH CHECK` a block predicate, each a call of a two-part inline
  table-valued function, and `FOR INSERT`, `UPDATE` or `DELETE` names the
  block predicate's operation. A declaration T-SQL has no form for, such as
  one with a `TO` role list, an inline expression, or a table switch, is
  refused when the schema is read, because SQL Server scopes a predicate
  inside the function body. SQL Server stores some predicate arguments
  rewritten, such as a `CAST`; a comparison against a connected database asks
  the server how it stores the declaration.
- Column drift planning handles type, nullability, and default changes (see
  [Column defaults](#column-defaults)); generated-expression, unique, and
  `CHECK` changes need a manual migration.
- Automatic column removal is rejected, because dependent constraints,
  defaults, and indexes must be dropped in the correct order first.
- Routines have no overloads. A schema that declares two functions, or two
  procedures, of one name with different parameters is refused before any
  statement is planned. Ptah writes `CREATE OR ALTER`, and measured on SQL
  Server 2022 the second replaces the first without an error.
- A synonym's target is recorded and resolved by the server, not validated by
  Ptah: a synonym naming an object that does not exist is created successfully
  and fails when something uses it, which is SQL Server's own behavior.
- View and trigger introspection records the persisted definition text without
  normalizing it into drift-safe definitions.
- Index planning preserves key order, direction, filtered predicates, and
  `INCLUDE` columns in the order written. A change to the included columns
  rebuilds the index with `DROP INDEX` and `CREATE INDEX`.
- Engine-specific options such as `WITH (ONLINE = ON)` are not planned.
- Dev-database cleanup rejects database replication, replicated tables, and
  unsupported database-scoped artifacts before its first DDL statement.
- A column declared both `PRIMARY KEY` and `UNIQUE` is refused rather than
  rendered. SQL Server rejects `a INT PRIMARY KEY UNIQUE` outright, where the
  other engines fold it, so there is no rendering of that column this server
  accepts and emitting the key alone would discard what the author wrote. Write
  the key alone, or write `a INT UNIQUE, PRIMARY KEY (a)`, which is a different
  statement this server does accept and which Ptah renders as the primary key
  plus the unique constraint it produces.

## Next steps

- Comparing engine depth before committing: [Database support matrix](../support-matrix/).
- Declaring indexes and constraints: [Go annotation reference](../../reference/go-annotations/).
- Gating destructive changes on any engine: [Lint and gate unsafe SQL](../../versioned/lint/).