# Round-Tripping Bitwarden and Smartstore Through a Database Diagram: What SQL Server Lost and What We Fixed

> We put two real SQL Server schemas, Bitwarden (a SQL Server database project) and Smartstore (Entity Framework Core), through Schemity and rebuilt a database from the SQL it wrote. Before this round of fixes Bitwarden's script failed, `varchar` came back as `nvarchar`, the live path turned `tinyint`, `money` and `datetime` into wider types, and filtered indexes lost their `WHERE`, so a copy refused rows the original accepts. After the fixes Smartstore comes back with no difference, Bitwarden differs only in two foreign keys created `WITH NOCHECK`, and 21 behaviour probes match.

Source: https://schemity.com/blog/sqlserver-round-trip-bitwarden-smartstore/

A database diagram that reads a SQL Server schema and writes it back should give you the same database, and before this round of fixes Schemity did not. We took two real open-source schemas, Bitwarden server v2026.10.0 (a SQL Server database project, 64 tables) and Smartstore 6.4.0 (Entity Framework Core, 118 tables), put each through Schemity from a script and from the live database, and rebuilt a database from the SQL Schemity wrote. Bitwarden's script failed, every `varchar` came back as `nvarchar`, and the copies accepted and refused different rows from the originals. After the fixes, Smartstore comes back with no catalog difference at all on either path, Bitwarden differs only in two foreign keys it created `WITH NOCHECK`, and both behave exactly like their originals in 21 behaviour probes.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. This is the third post in a series that tests the round trip on each database it supports, after [Mastodon and Saleor on PostgreSQL](https://schemity.com/blog/postgres-round-trip-mastodon-saleor/) and [Invoice Ninja and Sylius on MySQL](https://schemity.com/blog/mysql-round-trip-invoice-ninja-sylius/). Reading a schema you did not write is how you understand your schema before changing it, and SQL Server tested the parts of the diagram the other two never reached: its own type names, and indexes that carry more than their key columns.

## Which two schemas, and which two paths?

Two schemas built two different ways. Bitwarden's comes from a SQL Server database project (`Sql.sqlproj`) applied by its own migrator, so it is hand-written T-SQL with procedures and views. Smartstore's comes from its installer, which runs on Entity Framework Core with FluentMigrator.

| | Bitwarden server v2026.10.0 | Smartstore 6.4.0 |
| --- | --- | --- |
| What it is | Password manager | E-commerce platform |
| Built by | SQL Server database project, Bitwarden's migrator | Entity Framework Core and FluentMigrator, the web installer |
| Tables | 64 | 118 |
| Columns | 705 | 1,225 |
| Foreign keys | 88: 43 `ON DELETE CASCADE`, 2 `ON DELETE SET NULL`, 2 `WITH NOCHECK` | 122: 86 `ON DELETE CASCADE`, 20 `ON DELETE SET NULL` |
| Indexes besides primary keys | 117 | 260 |
| Filtered (`WHERE`) | 22, 3 of them unique | 5, all unique |
| Covering (`INCLUDE`) | 19 | 4 |
| With a `DESC` key | 6 | 0 |
| Keys | mostly `uniqueidentifier`, 4 `IDENTITY` | 104 `IDENTITY` |
| `varchar` / `tinyint` / `money` / `datetime` | 75 / 47 / 3 / 3 | 0 / 0 / 0 / 1 |
| `CHECK` / defaults / unique constraints | 1 / 40 / 5 | none |
| Also has | 61 views, 574 procedures, 3 functions, 5 table types | nothing |

SQL Server has no `pg_dump` or `mysqldump`. The script for the Import SQL path came from sqlpackage, Microsoft's command-line tool, with `/a:Extract /p:ExtractTarget=File`, which writes the whole schema as one `.sql` file. Each schema then went through Schemity on two paths:

- **Import SQL:** that script, read by Schemity's SQL parser, with no connection.
- **Live connection:** Schemity's SQL Server reader connected to the original database and read its catalog views.

Both paths end with the diagram's SQL applied to an empty database on the same SQL Server 2022 server. The runs used Schemity's own code from a development build, called from scripts rather than through the app's window. The fixes described below are in that build and ship in the next release; until then, the released app has the same type and index losses as the "before" column.

## How do you prove a rebuilt SQL Server schema is the same?

The same two checks as the MySQL post.

**The catalog diff.** A query over SQL Server's catalog views (`sys.columns`, `sys.indexes`, `sys.foreign_keys` and the rest) lists one line per fact for the original and for the copy: every column with its type, length or precision, nullability, default, identity and collation; every primary key, unique constraint and index with its keys in order, sort direction, `INCLUDE` columns, filter and clustering; every foreign key with its actions and whether SQL Server trusts it; every `CHECK`. The two lists are diffed line by line.

**The behaviour probes.** A probe is a few statements in one transaction that is rolled back: insert 256 into a `tinyint`, insert two rows with no order into a filtered unique column, delete a group and count its members. Its outcome is "accepted" or "refused at step N" with SQL Server's message number, and where it ends with a `SELECT`, the value read back is part of the outcome. A foreign key a probe is not about is switched off with `NOCHECK` inside the transaction, which the rollback undoes.

| Check | What it compares | What it catches |
| --- | --- | --- |
| Catalog diff | Columns, keys, indexes, foreign keys, as SQL Server's catalog reports them | Anything missing, added or changed in the schema |
| Probes, accept or refuse | Which rows each database accepts | A type range, unique index or foreign key that behaves differently |
| Probes, read back | What each database stored | A value rounded or encoded differently |

The 21 probes are 12 for Bitwarden and 9 for Smartstore. Smartstore's installer seeds rows in 35 tables and Bitwarden's migrator logs its scripts in one, which the copies do not have; every probe uses rows of its own.

## What failed before the fixes?

Everything below is from Schemity's code just before these fixes (commit `a599d7a5`), on both paths this time.

**Three of the four scripts failed under sqlcmd.** Bitwarden's Import SQL script stopped at:

```text
Msg 8166, Level 16, State 1, Line 983
Constraint name '#Cols_pkey' not permitted. Constraint names cannot begin with a number sign (#).
```

Both live-path scripts stopped at the first filtered index with Msg 1934, `CREATE INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'`. Smartstore's Import SQL script ran only because its filtered indexes had already lost their filters. With the phantom table removed by hand and the scripts run with `QUOTED_IDENTIFIER ON`, as the app's own connection has it, all four copies could be compared.

| | Bitwarden, Import SQL | Bitwarden, live | Smartstore, Import SQL | Smartstore, live |
| --- | --- | --- | --- | --- |
| Script ran | no, Msg 8166 | no, Msg 1934 | yes | no, Msg 1934 |
| `varchar` written as `nvarchar` | 75 | 75 | 0 | 0 |
| `tinyint` / `money` / `datetime` widened | 0 | 47 / 3 / 3 | 0 | 0 / 0 / 1 |
| Filtered indexes changed or dropped | 22 of 22 | 20 of 22 | 5 of 5 | 0 of 5 |
| Covering indexes without `INCLUDE` | 19 of 19 | 19 of 19 | 4 of 4 | 4 of 4 |
| Indexes that lost a `DESC` key | 6 | 6 | 0 | 0 |
| Clustered index made nonclustered | 0 | 0 | 1 | 1 |
| Probes that match the original | 10 of 12 | 7 of 12 | 8 of 9 | 8 of 9 |

The probes that failed are the ones that matter to the data:

| Probe | Original | Before the fixes | After |
| --- | --- | --- | --- |
| Bitwarden: `gw-Ā` into a `varchar(50)` | stores `gw-A` | stores `gw-Ā` | stores `gw-A` |
| Bitwarden: 256 into a `tinyint` | refused, Msg 220 | accepted (live) | refused |
| Bitwarden: an amount above `money`'s range | refused, Msg 8115 | accepted (live) | refused |
| Bitwarden and Smartstore: `10:00:00.001` into a `datetime` | stores `.000` | stores `.0010000` (live) | stores `.000` |
| Bitwarden: two active tax rates for one postal code | refused, Msg 2601 | accepted (Import SQL) | refused |
| Bitwarden: two inactive tax rates for one postal code | accepted | refused (live) | accepted |
| Smartstore: two reward point records with no order | accepted | refused (Import SQL) | accepted |

Cascades on delete, the `CHECK`, defaults, `IDENTITY`, case-insensitive uniqueness and foreign keys all survived. Each failure had its own cause.

### Why did Import SQL read a procedure as a table?

Because a script splits on more than `GO`. A sqlpackage or SSMS script separates batches with `GO`, and a procedure is one batch, but its body holds statements of its own, ended by semicolons. Bitwarden has 574 procedures, and one of them contains `CREATE TABLE #Cols (...)`, a temporary table. Split at its semicolons, the body became separate statements, and that one was read as a table. Import SQL now keeps a `CREATE PROCEDURE`, `FUNCTION`, `TRIGGER` or `VIEW` batch whole and skips it, and names the 61 views it skipped. A diagram holds tables; a live connection reads the views as read-only tables.

![Schemity's Import SQL summary for Bitwarden's sqlpackage script on an empty diagram: the last rows of the table list, each marked New, then the note that 61 views are not imported, naming them, the line Skipped 643 statements that are not tables, such as views, sequences and procedures, and the button Import (64 new, 0 updated)](https://schemity.com/images/blog/sqlserver-round-trip-bitwarden-smartstore/bitwarden-import-summary.webp)

### Why did the script fail under sqlcmd?

Because a filtered index needs a session setting that sqlcmd leaves off. SQL Server refuses to create a filtered index unless `QUOTED_IDENTIFIER` is `ON`, and [sqlcmd's own documentation](https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility) says its default is `OFF` unless you pass `-I`. SSMS connects with it on and its Generate Scripts writes it before each table; the script sqlpackage extracted for this test carries no `SET` at all, so it fails under sqlcmd the same way. Schemity's SQL Server scripts now start with `SET ANSI_NULLS ON` and `SET QUOTED_IDENTIFIER ON`, so View SQL's script runs the same in sqlcmd as in SSMS.

### Why is varchar not nvarchar?

Because they store different things. A `varchar` holds one byte a character in the column's code page, here `SQL_Latin1_General_CP1_CI_AS`; an `nvarchar` holds Unicode at two bytes a character. The probe shows the difference: `Ā` is not in that code page, so the original's `varchar(50)` stored `gw-Ā` as `gw-A`, while the copy, with `nvarchar(50)`, kept `gw-Ā`. The copy also doubled the bytes of 75 columns, and an index on one of them counts the doubled size against SQL Server's limit on an index key.

Schemity's SQL Server support used a reduced type list, and both paths folded a declared type into a wider one. The live reader read `varchar` and `char` as `nvarchar` and `nchar`, `tinyint` as `smallint`, `money` as `decimal`, and `datetime` and `smalldatetime` as `datetime2`. Each fold changes a value or a range:

- `tinyint` holds 0 to 255, so the original refuses 256 with Msg 220; `smallint` takes it.
- [money](https://learn.microsoft.com/en-us/sql/t-sql/data-types/money-and-smallmoney-transact-sql) stops at 922,337,203,685,477.5807; `decimal(19,4)` goes higher, so the copy accepted an amount the original refuses with Msg 8115.
- [datetime](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql) rounds to increments of `.000`, `.003` or `.007` seconds, so `10:00:00.001` is stored as `10:00:00.000`; `datetime2(7)` keeps it.

The diagram now keeps each type as SQL Server declares it on both paths, and the [field editor](https://schemity.com/doc/tables-and-fields/) offers SQL Server's own types: `varchar`, `char`, `varchar(max)`, `tinyint`, `money`, `smallmoney`, `datetime`, `smalldatetime`, `real`, `binary`, `varbinary` and `xml` beside the ones it had. A diagram drawn for another database still converts its text columns to `nvarchar`.

![The Billing legend of the Bitwarden diagram read from the live database: OrganizationPlanMigrationCohort.MigrationPathId as TINYINT, Transaction.Amount as MONEY, TaxRate's columns as VARCHAR(40), VARCHAR(50), VARCHAR(10) and VARCHAR(2), and SubscriptionDiscount's as VARCHAR(10) and VARCHAR(20), with the minimap of all 11 legends in the corner; the status bar reads Entities: 125, Relations: 88 (+30 virtual hidden)](https://schemity.com/images/blog/sqlserver-round-trip-bitwarden-smartstore/bitwarden-minimap.webp)

### What happens to a filtered unique index?

It depends on its filter, and before the fixes both readings were wrong in a different way. On SQL Server a unique index treats `NULL` as a value, so it allows one row without a value. A unique index filtered to `WHERE col IS NOT NULL` is how SQL Server writes the standard rule, many `NULL`s allowed, and Smartstore has five of them, among them `IX_RewardPointsHistory_UsedWithOrder_Id`. Import SQL dropped the filter, so the copy refused the second reward point record with no order. It now reads such an index as a unique rule that ignores `NULL`, as the live reader already did, and writes it back as the same filtered index.

![Schemity's Entity SQL dialog for Smartstore's RewardPointsHistory, in the diagram imported from its script: 16 numbered lines starting SET ANSI_NULLS ON and SET QUOTED_IDENTIFIER ON, the table with its two foreign keys, and CREATE UNIQUE INDEX IX_RewardPointsHistory_UsedWithOrder_Id ON RewardPointsHistory (UsedWithOrder_Id) WHERE UsedWithOrder_Id IS NOT NULL](https://schemity.com/images/blog/sqlserver-round-trip-bitwarden-smartstore/rewardpoints-view-sql.webp)

Bitwarden's `IX_TaxRate_Country_PostalCode_Active_Uniqueness` is the other kind: unique `WHERE ([Active]=(1))`, one active rate per postal code. Import SQL left it out entirely, so the copy accepted two active rates; the live reader dropped its filter, so the copy refused two inactive ones. The diagram now keeps a SQL Server index's filter as its `WHERE`, as it keeps a PostgreSQL partial index's.

![Schemity's index dialog for Bitwarden's IX_TaxRate_Country_PostalCode_Active_Uniqueness: Fields Country and PostalCode, Where ([Active]=(1)), Type NONCLUSTERED and Unique ticked, over TaxRate's index list showing the same WHERE and a tick in the U column](https://schemity.com/images/blog/sqlserver-round-trip-bitwarden-smartstore/taxrate-filtered-unique.webp)

### What about INCLUDE, DESC and clustered indexes?

They change speed, not rows, and they were all lost. A covering index's `INCLUDE` columns, a key sorted `DESC` and a `CLUSTERED` index that is not the primary key are now kept on both paths and written back; a table whose clustered index is another index gets its primary key written `NONCLUSTERED`, since SQL Server allows one clustered index a table. Neither schema needed that last one: Smartstore's clustered unique index is on a table with no primary key. `INCLUDE` turned up a PostgreSQL bug on the way: its live reader read a covering index's `INCLUDE` columns as key columns. PostgreSQL indexes keep `INCLUDE` now too.

![Schemity's index dialog for Bitwarden's IX_Event_DateOrganizationIdUserId: Keys [Date] DESC, [OrganizationId], [ActingUserId], [CipherId], Include ServiceAccountId, GrantedServiceAccountId, Type NONCLUSTERED; behind it, Event's index list shows IX_Event_OrganizationIdSendIdDate with [Date] DESC WHERE ([SendId] IS NOT NULL)](https://schemity.com/images/blog/sqlserver-round-trip-bitwarden-smartstore/event-index-desc-include.webp)

### Which bugs did only a refresh find?

Import a script, connect to the database it came from, and [Refresh & merge](https://schemity.com/doc/resync-database/) should find nothing to change. Measured through the app's own merge code, called from a script, it planned 113 statements for Bitwarden and 459 for Smartstore, all spelling: `INTEGER` from the script against `INT` from the reader, a `CHECK` that SQL Server stores in its own lowercase form, `INCLUDE` columns in another order, and names in brackets against names without. Both refreshes now list nothing, and so do the four MySQL and PostgreSQL schemas through the same check.

The same holds in the app's window the other way round: Import SQL of Bitwarden's script onto the diagram read from its live database finds nothing to change.

![Schemity's Import SQL summary for Bitwarden's script on the diagram read from the live database: the last rows of the table list, each marked Unchanged, the same 61 views not imported and 643 statements skipped, and the button Import (0 new, 0 updated) disabled](https://schemity.com/images/blog/sqlserver-round-trip-bitwarden-smartstore/bitwarden-import-unchanged.webp)

Two more turned up while this post was being checked, in schemas neither app has. A script's `INT` primary key without `IDENTITY`, the shape of a lookup table with fixed ids, came back as `IDENTITY(1,1)`, so the copy refused the ids the application inserts; the diagram now records whether SQL Server numbers a column, on both paths. And Import SQL wrote a deprecated `text` column as `nvarchar(max)`, the same widening as above; it now reads `text`, `ntext` and `image` as `varchar(max)`, `nvarchar(max)` and `varbinary(max)`, as the live reader does.

One more turned up later, while taking the SQLite post's screenshots, which the scripts here could not see because they skip a step the app takes when it reads a table: the app lists a table's fields key first, foreign keys next and timestamps last, and wrote `CREATE TABLE` in that order, so a copy built from the diagram met a `SELECT *` or an `INSERT` without a column list with its columns in another order. The diagram now remembers each column's position as the table declares it and writes that order, while the canvas keeps its own; a column added in the diagram goes last, where `ADD COLUMN` puts it. Read and written back the way the app does it, 62 of Bitwarden's 64 tables and 96 of Smartstore's 118 came back in a different column order before this fix.

## What does the round trip give you now?

The same database, on both paths, for both schemas.

| | Import SQL | Live connection |
| --- | --- | --- |
| Bitwarden: catalog differences | 2 foreign keys `WITH NOCHECK` | the same |
| Bitwarden: probes that match the original | 12 of 12 | 12 of 12 |
| Smartstore: catalog differences | none | none |
| Smartstore: probes that match the original | 9 of 9 | 9 of 9 |

Every one of the 75 `varchar`, 47 `tinyint`, 3 `money` and 4 `datetime` columns, the 27 filtered indexes, 23 covering indexes, 6 indexes with a `DESC` key and Smartstore's clustered unique index come back as they were, and the scripts run under sqlcmd without `-I`.

## What still differs, and why?

**Two foreign keys SQL Server does not trust.** Bitwarden's `FK_EmergencyAccess_GranteeId` and `FK_EmergencyAccess_GrantorId` were created `WITH NOCHECK`, so SQL Server did not check the rows already in the table when they were added and marks them not trusted. Both copies create them as ordinary, trusted keys. Every new row is checked either way, and an empty copy has no old rows to skip, so the diagram does not record it.

**Objects a diagram does not hold.** Bitwarden's 574 procedures, 3 functions and 5 table types are skipped by Import SQL, and its 61 views are skipped by name; a live connection reads the views as read-only tables. None of them is written back. SQL Server's deprecated `text`, `ntext` and `image` types are written as `varchar(max)`, `nvarchar(max)` and `varbinary(max)` on both paths; neither schema uses them. An `IDENTITY` column comes back with seed 1 and increment 1, which every identity in both schemas has.

## What this benchmark does not show

- **The app's window.** These runs called Schemity's import, reader, export and comparison code from scripts on a development build. The screenshots are from the app's window on the same build.
- **Data.** The originals hold only their seed rows. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
- **Triggers.** Neither schema has one.
- **Azure SQL and older servers.** Everything ran on SQL Server 2022 CU26.
- **Other databases.** SQLite has [its own post](https://schemity.com/blog/sqlite-round-trip-campfire-jellyfin/), with Campfire and Jellyfin, and so has Snowflake, [with TPC-DS](https://schemity.com/blog/snowflake-round-trip-tpcds/).
- **Schemas this size are not a stress test.** 64 and 118 tables are typical; a thousand-table schema is covered in [a separate post](https://schemity.com/blog/database-diagram-1000-tables-memory-cpu/).

## How to rerun it

Create each schema with its own tooling against an empty SQL Server, take `sqlpackage /a:Extract /SourceConnectionString:<connection> /TargetFile:<schema>.sql /p:ExtractTarget=File`, put the script or the database through Schemity, export the SQL and apply it to an empty database. Then list both catalogs with a query like this one, a shorter form of the benchmark's, run with `sqlcmd -d <database> -h -1 -y 8000`, and diff the output:

```sql
SET NOCOUNT ON;
WITH cols AS (
  SELECT CONCAT('column | ', t.name, ' | ', c.name, ' | ', ty.name,
      CASE
        WHEN ty.name IN ('nvarchar', 'nchar') THEN IIF(c.max_length = -1, '(max)', CONCAT('(', c.max_length / 2, ')'))
        WHEN ty.name IN ('varchar', 'char', 'varbinary', 'binary') THEN IIF(c.max_length = -1, '(max)', CONCAT('(', c.max_length, ')'))
        WHEN ty.name IN ('decimal', 'numeric') THEN CONCAT('(', c.precision, ',', c.scale, ')')
        WHEN ty.name IN ('datetime2', 'datetimeoffset', 'time') THEN CONCAT('(', c.scale, ')')
        ELSE '' END,
      IIF(c.is_nullable = 1, ' | null', ' | not null'),
      ' | ', ISNULL('default ' + dc.definition, 'no default'),
      ' | ', IIF(ic.column_id IS NULL, '-', CONCAT('identity(', CONVERT(varchar(40), ic.seed_value), ',', CONVERT(varchar(40), ic.increment_value), ')')),
      ' | ', ISNULL(c.collation_name, '-')) AS line
  FROM sys.tables t
  JOIN sys.columns c ON c.object_id = t.object_id
  JOIN sys.types ty ON ty.user_type_id = c.user_type_id
  LEFT JOIN sys.default_constraints dc ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
  LEFT JOIN sys.identity_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
),
idx AS (
  SELECT CONCAT(IIF(i.is_primary_key = 1, 'pk', IIF(i.is_unique_constraint = 1, 'unique constraint', 'index')),
      ' | ', t.name, ' | ', i.name, ' | ', IIF(i.is_unique = 1, 'unique', 'non-unique'), ' | ', LOWER(i.type_desc), ' | ',
      (SELECT STRING_AGG(CONCAT(c.name, IIF(ic.is_descending_key = 1, ' desc', '')), ', ') WITHIN GROUP (ORDER BY ic.key_ordinal)
         FROM sys.index_columns ic JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
         WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0),
      ISNULL(' | include ' + (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY c.name)
         FROM sys.index_columns ic JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
         WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1), ''),
      ISNULL(' | where ' + i.filter_definition, '')) AS line
  FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id
  WHERE i.type > 0
),
fks AS (
  SELECT CONCAT('fk | ', t.name, ' | ', fk.name, ' | ',
      (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY fc.constraint_column_id)
         FROM sys.foreign_key_columns fc JOIN sys.columns c ON c.object_id = fc.parent_object_id AND c.column_id = fc.parent_column_id
         WHERE fc.constraint_object_id = fk.object_id),
      ' | ', OBJECT_NAME(fk.referenced_object_id),
      ' | on delete ', LOWER(fk.delete_referential_action_desc), ' | on update ', LOWER(fk.update_referential_action_desc),
      IIF(fk.is_not_trusted = 1, ' | not trusted', '')) AS line
  FROM sys.foreign_keys fk JOIN sys.tables t ON t.object_id = fk.parent_object_id
)
SELECT line FROM (
  SELECT line COLLATE Latin1_General_BIN2 AS line FROM cols
  UNION ALL SELECT line COLLATE Latin1_General_BIN2 FROM idx
  UNION ALL SELECT line COLLATE Latin1_General_BIN2 FROM fks
  UNION ALL SELECT CONCAT('check | ', t.name, ' | ', ck.name, ' | ', ck.definition) COLLATE Latin1_General_BIN2
    FROM sys.check_constraints ck JOIN sys.tables t ON t.object_id = ck.parent_object_id
) catalog ORDER BY line;
```

For behaviour, run a few statements per rule inside `BEGIN TRANSACTION` and `ROLLBACK` on the original and on the copy, end each with a `SELECT` of what was stored, and compare both the step SQL Server refuses at and the values it reads back. Connect with `QUOTED_IDENTIFIER ON`, or SQL Server refuses every write to a table with a filtered index before your probe gets to its point.

## Frequently asked questions

### How do you generate an ERD from a SQL Server database without losing indexes?

The diagram has to keep what SQL Server writes beyond the columns: a filtered index's `WHERE`, the `INCLUDE` columns of a covering index, a `DESC` key and `CLUSTERED`. Schemity reads all four from a live database and from a sqlpackage script, and writes them back. A unique index filtered to `IS NOT NULL` is read as a unique rule that ignores `NULL`, the way the diagram models a unique rule on every database, and written back as the same filtered index, so it keeps allowing more than one `NULL`.

### Why does a filtered index fail to create in sqlcmd?

A filtered index needs `QUOTED_IDENTIFIER ON`, and sqlcmd connects with it `OFF` unless you pass `-I`, so the statement fails with Msg 1934. SSMS and most drivers turn it on, and SSMS's Generate Scripts writes it into the script; a script extracted with sqlpackage does not. Schemity's SQL Server scripts now start with `SET ANSI_NULLS ON` and `SET QUOTED_IDENTIFIER ON` so they run under sqlcmd too.

### Is varchar the same as nvarchar in a SQL Server schema diagram?

No. `varchar` stores one byte a character in the column's code page, so a character outside it is approximated or replaced; `nvarchar` stores Unicode at two bytes a character. Bitwarden declares 75 `varchar` columns, and a diagram that writes them as `nvarchar` builds a database that stores different values and twice the bytes.