I converted the full healthcare_dba schema to T-SQL for SQL Server 2025 — five tables across two schemas, every key and index, and a complete rewrite of the one piece of custom logic in the database: a generic PL/pgSQL audit-logging trigger with no direct T-SQL equivalent.
I created both schemas (cms, audit) and all 5 tables in T-SQL, including all 4 primary keys, the 1 foreign key, and all 8 secondary indexes — matching the source exactly. I decided to migrate cms.staging_raw as-is, keeping every column NVARCHAR to mirror the source's raw landing-table structure rather than tightening its types.
| PostgreSQL | SQL Server | Notes |
|---|---|---|
| character varying(n) | NVARCHAR(n) | Unicode-safe for names/addresses |
| text | NVARCHAR(MAX) | No fixed length in source |
| numeric (no precision) | DECIMAL(10,4) / DECIMAL(18,2) | Fixed precision chosen per column — coded values vs. currency amounts |
| bigint (BIGSERIAL-backed) | BIGINT IDENTITY(1,1) | provider_services.id, data_access_log.log_id |
| timestamp with time zone | DATETIMEOFFSET | Preserves time zone info |
| inet | NVARCHAR(45) | No native network-address type in SQL Server |
| jsonb | JSON | SQL Server 2025's native JSON type — the one PostgreSQL-specific type that mapped cleanly |
| Composite primary key | PRIMARY KEY (col1, col2) | user_state_access — both columns listed directly |
PostgreSQL used one generic function, audit.log_data_changes(), shared by both triggers. It relied on dynamic trigger metadata (TG_TABLE_NAME, TG_TABLE_SCHEMA, TG_OP) and to_jsonb(OLD) / to_jsonb(NEW) to log a full row snapshot on every INSERT, UPDATE, and DELETE — regardless of which table fired it.
-- PostgreSQL: one function, shared by every trigger
INSERT INTO audit.data_access_log (schema_name, table_name, operation, old_data, new_data)
VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP,
to_jsonb(OLD), to_jsonb(NEW));T-SQL has no equivalent to one function shared across multiple table triggers with dynamic table awareness — every trigger is bound to exactly one table. So I wrote two separate triggers instead of one shared function.
-- T-SQL: per-row JSON snapshot via correlated FOR JSON PATH
(SELECT i2.* FROM inserted i2 WHERE i2.rndrng_npi = i.rndrng_npi
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)I tested both triggers by inserting, updating, and deleting a throwaway test row on cms.providers. All three operations logged correctly: INSERT showed NULL old_data with the new row populated, UPDATE showed both old and new data reflecting the actual change, and DELETE preserved the last known state with NULL new_data — then I cleaned up the test rows before moving on.