SQL Server

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:

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:

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:

[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:

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

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:

//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{}

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.

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

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.

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:

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:

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.

Filtered indexes

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

//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:

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); 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.
• Declaring indexes and constraints: Go annotation reference.
• Gating destructive changes on any engine: Lint and gate unsafe SQL.